Foreign keys / Transactions in MySQL.. WTF?

[Andy Wardley]

Toby Corkindale wrote:
> 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 got stung by this one yesterday:

   mysql> CREATE TABLE badgers (
       ->     id char(32) NOT NULL,
       ->     name char(32)
       -> );
   Query OK, 0 rows affected (0.01 sec)

   mysql> INSERT INTO badgers (name) VALUES ('tiberius');
   Query OK, 1 row affected, 1 warning (0.00 sec)

See how MySQL completely ignores the 'NOT NULL' constraint on the id field?
I just noticed that it did raise a warning, but that's hardly helpful.

   mysql> show warnings;
   +---------+------+-----------------------------------------+
   | Level   | Code | Message                                 |
   +---------+------+-----------------------------------------+
   | Warning | 1364 | Field 'id' doesn't have a default value |
   +---------+------+-----------------------------------------+
   1 row in set (0.00 sec)

A