Foreign keys / Transactions in MySQL.. WTF?

Aaron Crane perl at
Tue Jul 31 08:40:33 BST 2007

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:

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:

Aaron Crane

More information about the mailing list