Chaz Meyers (cpm) wrote,
Chaz Meyers
cpm

Mixing InnoDB Foreign keys that cascade constraints with "REPLACE INTO" is a bad idea.

Consider the following database:

mysql> desc pimps;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(255)     |      | PRI | NULL    | auto_increment |
| name  | varchar(255) |      |     |         |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc hos;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(255)     |      | PRI | NULL    | auto_increment |
| name    | varchar(255) |      |     |         |                |
| pimp_id | int(255)     |      | MUL | 0       |                |
+---------+--------------+------+-----+---------+----------------+


hos.pimp_id refers to pimps.id and cascades on update and delete.

Now, suppose we have a Pimp Named Slickback and Crystal:
mysql> select * from pimps;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | Slickback |
+----+-----------+
1 row in set (0.00 sec)

mysql> select * from hos;
+----+---------+---------+
| id | name    | pimp_id |
+----+---------+---------+
|  1 | Crystal |       2 |
+----+---------+---------+
1 row in set (0.00 sec)


But, wait! It's "A Pimp Named Slickback". You say the whole thing. It's like "A Tribe Called Quest".

mysql> REPLACE INTO pimps VALUES(2, "A Pimp Named Slickback");
Query OK, 2 rows affected (0.01 sec)

mysql> select * from pimps;
+----+------------------------+
| id | name                   |
+----+------------------------+
|  2 | A Pimp Named Slickback |
+----+------------------------+
1 row in set (0.00 sec)


That's better! Let's see how Crytal, like the champagne is doing.

mysql> select * from hos;
Empty set (0.00 sec)


Uh oh. Crystal ran off with Grandad, it looks.



Moral of the story? REPLACE INTO is not intelligent. It never does UPDATE. When the record already exists, it DELETEs and then INSERTs. This can be bad because it will trigger foreign key constraints.

If you are using foreign keys, you must check that they exist at the application layer. The REPLACE INTO might look like an attractive avenue to pursue, but it will leave you in tears. You will lose all your hos.



update An anonymous patron points out that with "INSERT INTO ... ON DUPLICATE KEY UPDATE", I can keep my ho's amd my foreign keys, and still be lazy about checking if a row exists or not! The best of both worlds.
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 8 comments