db4free.net blog: Breaking referential integrity

Tuesday, December 27, 2005

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/breaking-referential-integrity/.

rpbouman said...: Hey Markus,

The DEFERRED thingie is defined as part of the SQL standard. It is particularly useful for things that can be handled within the transaction. You'd use it in case it is part of the business logic that the child rows are created before you'd know a parent record exists, or before you'd know the exact properties of the parent row (or rows, for that matter).

A feature that disables constrains entirely - like this

@@foreign_key_checks

variable setting - is especially useful for stuff like bulk loading data.

Of course, I agree that it would be nice if MySQL would check for foreign key violations before re-enabling it (Oracle employs this strategy). This way, you can TRUST an enabled foreign key definition to be unviolated.

It would also be nicer still to have DEFERRED constraints.

(just pointing out the difference between DEFERRED and disabling/enabling constraints); 23:45
Markus Popp said...: Hi,

usually, you get this error if the value is not available in the referenced table. At the moment, I wouldn't have an idea why you get the error, although the value is there. Could you provide an example, where this error occurs?; 17:06
Anonymous said...: to avoiding error 1452
try to ignore the foreign key sheck by this command :

SET FOREIGN_KEY_CHECKS = 0;

hth; 14:08

db4free.net blog