This blog post has moved. Please find it at:
http://www.mpopp.net/2005/12/breaking-referential-integrity/.
Subscribe to:
Post Comments (Atom)
skip to main |
skip to sidebar
This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
About Me
Links
Blog Archive
-
▼
2005
(48)
-
▼
December
(16)
- New features in and not in MySQL 5.1
- MySQL - PostgreSQL reactions
- Breaking referential integrity
- MySQL - PostgreSQL
- The first week on my new job
- RTFM experiences
- db4free.net CleanUp process
- News about me & db4free.net
- Language specific MySQL.com website
- Simple or complex?
- TX isolation levels (4) How to produce an artifici...
- TX isolation levels (3) REPEATABLE READ, SERIALIZABLE
- Server statistics
- db4free.net data type statistics
- db4free.net offers MySQL 5.0 AND 5.1 databases
- db4free.net about to offer MySQL 5.1 databases as ...
-
▼
December
(16)
5 comments:
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)
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.
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 :)
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?
to avoiding error 1452
try to ignore the foreign key sheck by this command :
SET FOREIGN_KEY_CHECKS = 0;
hth
Post a Comment