Daniel Pittman <dan...@rimspace.net> writes: > "H.Merijn Brand" <h.m.br...@xs4all.nl> writes: >> On Tue, 08 Jan 2008 04:43:51 -0500, num...@deathwyrm.com wrote: >>> Philip Newton wrote: >>> > It even lets you mix the two with REPLACE INTO, which will do an >>> > INSERT, unless the record is already present, in which case it'll do >>> > an UPDATE instead. >>> >>> Rather, it does a DELETE then an INSERT. Which I'd rate slightly more >>> hateful, since it's another entry towards any auto-insert fields running >>> out of numbers. But it probably depends on the situation. >> >> And delete won't work if it is a referenced record, where update would. >> This is just screaming for cursing users. More hate > > Yeah, if MySQL actually had referential integrity then this would be a > nasty problem. Of course, the clever folks putting it together were > kind enough to anticipate this ... and just not bother implementing > those inconvenient checks at all. > > MySQL, friend of us all.
MySQL has had foreign key constraints (but only when using the InnoDB engine) since version 3.23.44, and the above scenario fails exactly as Merijn describes. To insert or update a potentially-referenced row you need the even more hateful INSERT … ON DUPLICATE KEY UPDATE syntax, which was added in 4.1.0 -- ilmari "A disappointingly low fraction of the human race is, at any given time, on fire." - Stig Sandbeck Mathisen
