Foreign keys constraint

To be frank, i just find the shell prompt and query exection happening a bit different or should i say handled a bit different. For eg:- Say you got a table with foreign key constraint. Before a update you could speed the later update by disabling the keys and turning of the foreign key checks at shell prompt.  I expected the same from within php script but nopes , it was not.

It was being taken as seperately and my foregin key check made the update to fail rather to happen.

Solution I worked around was to write a handler function for multiple queries to a sql file and this got the prompt do the task which I found it does perfectly.

$batchUpdatesql .= “ALTER TABLE {tablename} DISABLE KEYS;\n”;
$batchUpdatesql .= “SET FOREIGN_KEY_CHECKS=0;\n”;
$batchUpdatesql .= {update queries};
$batchUpdatesql .= “ALTER TABLE {tablename} ENABLE KEYS; \n”;
$batchUpdatesql .= ” SET FOREIGN_KEY_CHECKS=1;\n”;

Just to brush it up the function in the dbwrapper is pasted below
public function executeMultipleQueries($filepath,$sql)
{
global $errorlog;
$file = fopen($filepath ,”w+”);
fwrite($file,$sql);
fclose($file);
exec(“mysql -u” . $this->login .  ” -p” . $this->pwd .  ” ” . $this->db . ” < ” . $filepath  );
}

Again, some better suggestion would be highly appreciated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: