Breaking referential integrity in MySql


Integrity constraint violation: 1452 Cannot add or update a child row: a foreign
key constraint fails (`sumaria_magento/mg_catalog_category_product_index`,
CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_PRODUCT_ENTITY` FOREIGN KEY
(`product_id`) REFERENCES `mg_catalog_product_entity` (`entity_id)

Reason..
The Constraint dont find any Parent keys in parent table. But children for these Product ids are present. That means… database have Children entries without Parent …

Is it possible to break the referential integrity when a foreign key constraint is set on an InnoDB table? The answer is an (unfortunate?) yes. It’s not a bad feature that it’s possible to temporarily turn @@foreign_key_checks off, because sometimes it’s necessary to change data in a way that referential integrity needs to be broken during the modification process. But most of the time, the data should be valid, when modification is finished. So what can be done?

A little example demonstrates this:

mysql> CREATE TABLE table_one (
->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
->   ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table_one (id) VALUES
->   (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE table_two (
->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->   id_table_one INT NOT NULL,
->   FOREIGN KEY (id_table_one) REFERENCES table_one(id))
->   ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table_two (id_table_one) VALUES
->   (3), (2), (2), (3), (1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM table_one; SELECT * FROM table_two;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
3 rows in set (0.00 sec)

+—-+——-+
| id | id_table_one |
+—-+——-+
|  5 |     1 |
|  2 |     2 |
|  3 |     2 |
|  1 |     3 |
|  4 |     3 |
+—-+——-+
5 rows in set (0.00 sec)

mysql> INSERT INTO table_two (id_table_one) VALUES (4);
ERROR 1452 (23000): Cannot add or update a
child row: a foreign key constraint fails
(`test/table_two`, CONSTRAINT `table_two_ibfk_1` FOREIGN KEY
(`id_table_one`) REFERENCES `table_one` (`id`))

mysql> SET @@foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table_two (id_table_one) VALUES
->   (2), (1), (4), (2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @@foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM table_one; SELECT * FROM table_two;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
3 rows in set (0.00 sec)

+—-+——-+
| id | id_table_one |
+—-+——-+
|  5 |     1 |
|  7 |     1 |
|  2 |     2 |
|  3 |     2 |
|  6 |     2 |
|  9 |     2 |
|  1 |     3 |
|  4 |     3 |
|  8 |     4 |
+—-+——-+
9 rows in set (0.00 sec)

The record no 8 in table table_two has now an invalid value, due to the fact that we have set the foreign key verification temporarily to off (0).

Advertisements

6 thoughts on “Breaking referential integrity in MySql

  1. Hi,

    The article was nice but felt it was abruptly stopped from my learning point.

    could you pl. tell me:

    the check is temporarily off.

    what will be my next steps:
    a) can I add value ‘4’ to table_one later on ?
    b) do I have make the foreign key check on later on ?
    c) if so, when should I check it on? at what stage ?

    or is it that, one the value ‘4’ is added to table-one, there is automatica synchrony between the two tables ?

    pardon my lack of knowledge.
    Thank you,
    looking forward to hearing from you,
    Prasad.

  2. Hello Prasad
    Thanks for visiting my blog and your intelligent comment. Yes you may add value ‘4′ to table_one later on and also you may check foreign key later on but follow the step carefully when you want to check ‘foreign key’ and table synchronization will be automatically.

    Thanks and Regards
    –Rana

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