Programmatically get relation between tables mysql

Question

Assume that I have 3 tables namely sales_order , sales_order_items , invoice and set ON UPDATE = NO ACTION ON DELETE = NO ACTION.

Now I need to have the ability do delete the sales order. But if there is a relation exists in any other table it means that that sales order is used somewhere else and i need to prevent deletion.

Ex: I have sales_order_id = 34 , I want to check its exists in any other tables.

Previously to achieve the same thing I used transactions, something like below

$db = new database();

//start transaction

$db->start_trans();

//try to delete the sales order with ID = 34
$db->exec( 'DELETE FROM SALES_ORDER WHERE ID = 34' );

//check transaction success or failure 

if( $db->trans_status() == true ){

 //THERE IS NO RELATION EXISTS
 //ROLLBACK
 $db->rollback_trans();

 #Soft Delete the record     
 $db->exec( 'UPDATE sales_order SET is_deleted = 1 WHERE id = 34' );

}else{

 //RELATION EXISTS FOR ID = 34 IN SOME OTHER TABLES
}

the above code works, but the problem is with sales_order_items. Because it is a child table of sales_order and if it have contents then the transaction will fail and will try to execute the else part.

But in fact sales_order_items is a property of sales_order and I need to DELETE that particular sales_order (don't care about items).

I am expecting something like this

$relations = $db->get_relation( 'sales_order.id', '34' );

Expected Output

array( 'sales_order_items','invoice','another_table' .... );

Note: The above is an example only, I have number of tables and it's not possible to go through each table and check ID exists.

why are you not using on delete cascade and then you're set emulating its behavior anyway through your application? — STT LCU
– STT LCU, Commented Jul 24, 2013 at 7:08
the fact that you used the "DELETE" keyword mislead me. Now i understand. — STT LCU
– STT LCU, Commented Jul 24, 2013 at 7:11
@STTLCU I am sorry about that :) i think that i need to join hand with php inorder to solve the problem. May be some kind of loops. — Red
– Red, Commented Jul 24, 2013 at 7:17
I lost the point: you need to mark row as deleted in both cases - when it is affected or not affected by foreign key constraint. So what is the problem? You just run UPDATE sales_order SET is_deleted = 1 WHERE id = 34. — revoua
– revoua, Commented Jul 24, 2013 at 7:54

revoua · Accepted Answer · 2013-07-24 07:55:24Z

2

select * from information_schema.table_constraints
where constraint_schema = DATABASE();

select * from information_schema.key_column_usage
where constraint_schema = DATABASE();

SQLFIDDLE

edited Jul 24, 2013 at 7:55

answered Jul 24, 2013 at 6:33

revoua

2,0691 gold badge23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Red Over a year ago

Could you please explain ?

revoua Over a year ago

Sure, preparing sqlfiddle demo.

revoua Over a year ago

Added example where you see existing relation with table and column names.

Red Over a year ago

But where i put the value ? in my example 34.

Red · Accepted Answer · 2013-07-24 11:53:08Z

Thanks Revoua for providing a good answer. Here is little modified version to suite my needs. Please correct me if it have some better way.

function check_relation( $table_name , $table_column ,$table_value ){

        //Get relation tables
        $qry = "SELECT  table_name,
                column_name,
                referenced_table_name,
                referenced_column_name 
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE REFERENCED_TABLE_SCHEMA = ?
                AND REFERENCED_TABLE_NAME is not null
                AND referenced_table_name = ?
                AND referenced_column_name = ?";

        $db = $this->crm->db;

        //database name
        $database = $db->dbprefix.$db->database;

        //predefined array to store table names
        $related_tables = array();

        //execute the query 
        $qry = $db->query( $qry , array( $database , $table_name ,$table_column ) );

        unset($database);

        //Make sure that query is success
        if( $qry != FALSE && $qry->row_count() > 0 ){

            //Get as array
            $result = $qry->to_array();
            unset($qry);

            foreach( $result as $result_item ){

                //Get count based on table_value
                $qry = "SELECT COUNT(*)as total FROM ".$result_item['table_name']." WHERE ".$result_item['column_name']." = ? and is_del = 0";
                $qry = $db->query($qry,array($table_value));

                if( $qry!= FALSE && $qry->row_count() > 0 ) {

                    $qry = $qry->to_array(1);

                    //There are some entries ..
                    if( intval($qry['total']) > 0 ){        
                        $related_tables[] = $result_item['table_name'];
                    }

                }


            }

            return $related_tables;

        }else{
            return $related_tables;
        }

    }

Usage:

$relation = $db->check_relation("SALES_ORDER","ID",34);

var_dump( $relation ) ==>

array( 'SALES_ORDER_ITEM','INVOICE' ... );

Now in business logic

if( empty( $relation )  ){

  // No relation exists

}elseif( in_array( 'invoice', $relation ) ){

   $message->set_message("Cannot delete Sales Order, Invoice exists.");

}else ...

Collectives™ on Stack Overflow

Programmatically get relation between tables mysql

2 Answers 2

4 Comments

Usage:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Usage:

Comments

Your Answer

Sign up or log in

Post as a guest

Related