Foreign keys / Transactions in MySQL.. WTF?

[Ovid]

--- Toby Corkindale <tjc at wintrmute.net> wrote:
> I rolled the transaction back, so why has the table gone away?

That one appears to have been explained in previous emails.

> mysql> create table foo (id serial primary key, bar integer not null
> references somekeys(id)) Engine=InnoDB;
> Query OK, 0 rows affected (0.03 sec)

Yeah, this has bitten many people.  MySQL thoughtfully parses, and then
*silently* discards this legal syntax.  Thus, you're screwed if you try
and use it.  The MySQL folks have long since pulled down their idiotic
essay about how FK support belongs in the application layer and not the
database, probably because they couldn't handle all of the serious
database folk pointing and laughing, but they still don't *quite* get
it.

If you want to have fun, read this while searching through the phrase
"Deviation from SQL standards".

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Hell, maybe someone should write an article about how many ways it's
possible to violate constraints in MySQL.

There is *still* virtually no reason to choose bug-ridden MySQL over
PostgreSQL, except that some people find its toys easier to play with.

Cheers,
Ovid

--
Buy the book  - http://www.oreilly.com/catalog/perlhks/
Perl and CGI  - http://users.easystreet.com/ovid/cgi_course/
Personal blog - http://publius-ovidius.livejournal.com/
Tech blog     - http://use.perl.org/~Ovid/journal/