Foreign keys / Transactions in MySQL.. WTF?

[Luis Motta Campos]

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.

> mysql> commit;
> Query OK, 0 rows affected (0.00 sec)

  Implicit creating a new transaction, explicit commit of another empty 
transaction

> mysql> begin;
> Query OK, 0 rows affected (0.00 sec)

  Explicit creating another transaction

> mysql> drop table foo;
> Query OK, 0 rows affected (0.00 sec)

  DDL used: implicitly committing an empty transaction, dropping table as 
requested.

> mysql> rollback;
> Query OK, 0 rows affected (0.00 sec)

  No effect: there is no transaction to roll back

> mysql> select * from foo;
> ERROR 1146 (42S02): Table 'testdb.foo' doesn't exist
> mysql>

  Error: trying to select from a non-existent table. :-)

> I rolled the transaction back, so why has the table gone away?

  I guess the reason is pretty obvious now...

> 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 ;-)

> 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... ;-)

   Putamplexos!
-- 
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}