?

Log in

No account? Create an account
Chaz Meyers [entries|archive|friends|userinfo]
Chaz Meyers

[ website | chazmeyers.com ]
[ profile | view profile ]
[ archive | view archive ]

Links
[Links:| chazmeyers.com Twitter ]

Mixing InnoDB Foreign keys that cascade constraints with "REPLACE INTO" is a bad idea. [Jan. 20th, 2006|02:58 pm]
Chaz Meyers
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.
LinkReply

Comments:
From: (Anonymous)
2006-02-10 05:44 pm (UTC)

How to have your hos stick around

If you want your hos to stick around, but still have REPLACE INTO-like flexibility on your ho-management database, try:

INSERT INTO pimps VALUES (2, "A Pimp named Slickback") ON DUPLICATE KEY UPDATE

That should just cause an UPDATE event rather than a combo DELETE/INSERT event on a key collision, and therefore your ho-ranks are unchanged.
(Reply) (Thread)
[User Picture]From: cpm
2006-02-11 06:34 am (UTC)

Re: How to have your hos stick around

Thanks! First I've heard of that clause.

I filed a bug with Class::PObject, so hopefully they'll start using "ON DUPLICATE KEY UPDATE" instead of "REPLACE INTO" for updates and inserts.
(Reply) (Parent) (Thread)