Foreign keys / Transactions in MySQL.. WTF?

[Toby Corkindale]

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)

mysql> create table foo (bar integer primary key);
Query OK, 0 rows affected (0.07 sec)

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

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

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

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

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


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



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 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?

Cheers,
Toby