Foreign keys / Transactions in MySQL.. WTF?
publiustemp-londonpm at yahoo.com
Tue Jul 31 09:28:13 BST 2007
--- 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
If you want to have fun, read this while searching through the phrase
"Deviation from SQL standards".
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.
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/
More information about the london.pm