Foreign keys / Transactions in MySQL.. WTF?

[Toby Corkindale]

Luis Motta Campos wrote:
> On Tuesday 31 July 2007 03:30, Toby Corkindale wrote:
>> So, I've been an advocate of PostgreSQL over MySQL for a long time, but
>> perhaps I've never been fair because I haven't actually tried to use
>> MySQL seriously for a long time.. and its advocates say that recent
>> versions are much better, and include things like transactions and
>> foreign key support.
>>
>> I'm working on a contract which mandated MySQL as the database backend.
>> They're actually using version 4.1.2, but I've tested the following
>> against version 5.0 as well.
>>
>> Essentially - if MySQL has transaction support, then what the hell is
>> going on here?
>>
>> $ mysql testdb
>> [snip]
>> mysql> begin;
>> Query OK, 0 rows affected (0.00 sec)
> 
>   Transaction begins.
> 
>> mysql> create table foo (bar integer primary key);
>> Query OK, 0 rows affected (0.07 sec)
> 
>   DDL (Data Definition Language) statement: implicitly committing an empty 
> transaction, creating table as defined.

Oh, OK, so DDL statements are not meant to occur within transactions?
Perhaps this is an oddity of PostgreSQL then, where they ARE managed
within transactions.. which gives me a lot more confidence when making a
series of changes and verifying them before committing the results.

[snip]

>> OK, and what about the foreign key support?
>>
>> mysql> create table somekeys (id serial primary key) Engine=InnoDB;
>> Query OK, 0 rows affected (0.01 sec)
>>
>> 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)
>>
>> mysql> insert into somekeys (id) values (1), (2);
>> Query OK, 2 rows affected (0.04 sec)
>> Records: 2  Duplicates: 0  Warnings: 0
>>
>> mysql> insert into foo(bar) values (3);
>> Query OK, 1 row affected (0.00 sec)
>>
>> No, not that query isn't ok!
> 
>   I need to confirm this in the manuals, but it seems to me that MySQL 4.1 has 
> no support for foreign keys. If you're looking for a Relational Database 
> Management System, look another way ;-)

Yeah, and that happens on MySQL 5.0 as well. The MySQL advocates seem to
suggest that MySQL *is* a RDMS. Silly me for believing them :)

>> I just want to check that I'm not doing something braindead.
>> Am I? Is there an --enable-sql-features flag that needs to be turned on
>> at compile time?
> 
>    No, You're not brainded. You should try using RDBMSs just for a 
> change... ;-)

Well, as I said at the top of my original message - I normally DO use
RDMSes, but am merely using MySQL due to the contract requirements. If I
regularly used MySQL then I probably wouldn't even be attempting to use
these sql features! ;)

Toby