Foreign keys / Transactions in MySQL.. WTF?
perl at aaroncrane.co.uk
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)
More information about the london.pm