Tuesday, December 27, 2005

Breaking referential integrity

This blog post has moved. Please find it at:

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

5 comments:

Roland Bouman 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)

the hitchhiker said...

hi, could u tell me y u got the error 1452, as i get the same--ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`mydiary/article`, CONSTRAINT `article_ibfk_1` FOREIGN KEY (`articleId`)
i am inserting the FK diaryTitle value in this mydiary/article, and this value does exist in mydiary/diary where it's the PK...i know this isn't any forum where u help others..but please tell me if u have a minute...thanks for reading this.

the hitchhiker said...

hi, please tell me why u got that error 1452...cannot add or update a child row..., i am getting the same on adding a value to a FK, but this value does exist in its table where it's a PK...thx in advance Mr. Markus :)

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?

Anonymous said...

to avoiding error 1452
try to ignore the foreign key sheck by this command :

SET FOREIGN_KEY_CHECKS = 0;

hth