?

Log in

No account? Create an account
Mixing InnoDB Foreign keys that cascade constraints with "REPLACE INTO" is a bad idea. - 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:
[User Picture]From: instantdharma
2006-01-20 11:24 pm (UTC)
I have no idea what that was, but it was funny.

Warren and I were talking about A Pimp Named Slickback today.

I never want to lose all my hos.
(Reply) (Thread)
From: (Anonymous)
2006-01-21 05:49 pm (UTC)
I hope everything with your dad went ok!

Also, I saw you from across the train station at cecil today and tried to text you "I see you" but then I realized somehow your number didnt transfer over to me new phone!

Hit me up with yo number.
(Reply) (Thread)
[User Picture]From: pinkneonchick
2006-01-21 06:10 pm (UTC)
Oops that was me, I didnt realize I wasnt logged in
(Reply) (Thread)
[User Picture]From: cpm
2006-01-21 10:34 pm (UTC)
Shoot. I don't think I copied your number over to my new phone either! Heh. I'll give you my number next time I see you on aim.

Thank you for the well wishing. My father is still in ICU, but doing well.
(Reply) (Parent) (Thread)
[User Picture]From: supacheex
2006-01-21 10:01 pm (UTC)
This entry has been nominated for LJ Entry of the Year 2006

Congrats
(Reply) (Thread)
[User Picture]From: cpm
2006-01-21 10:34 pm (UTC)
"You like me. You really like me!" Heh. :)
(Reply) (Parent) (Thread)
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)