Foreign keys / Transactions in MySQL.. WTF?

[Aaron Crane]

Toby Corkindale writes:
> 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)

DDL statements implicitly commit the current transaction:

  http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

The ability to do schema upgrades safely is something I really like
about PostgreSQL compared to MySQL.

> OK, and what about the foreign key support?
> 
> 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 supports only a small part of the standard syntax for foreign
keys.  You have to specify them as table constraints (not column
constraints), and you need to name the columns referred to:

  create table foo (
    id int primary key auto_increment,
    bar int not null,
    foreign key (bar) references bar (id)
  ) engine=InnoDB;

See also:

  http://hateful.hates-software.com/2006/10/19/f304fb73.html

-- 
Aaron Crane