Constraint name visibility on MySQL and PostgreSQL

By Adrian Smith7 Feb 2013300 words2 mins to read

Can one have two tables with constraints of the same name? Is that even consistent between types of constraints? What about between database vendors?

It turns out that it's neither consistent between types, nor is the way in which it's inconsistent consistent between database vendors.

  MySQL 5.5 PostgreSQL 9.2
Unique constraint names Local to table Global to DB
Foreign Key constraint names Global to DB Local to table

From MySQL:

mysql> CREATE TABLE foo (x INTEGER, 
    ->   CONSTRAINT foo_unique UNIQUE(x));
Query OK, 0 rows affected (0.01 sec)

-- Second UNIQUE constraint CAN be created with same name
mysql> CREATE TABLE foo2 (x INTEGER, 
    ->   CONSTRAINT foo_unique UNIQUE(x));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE foo3 (x INTEGER, 
    ->   CONSTRAINT foo_fk FOREIGN KEY (x) REFERENCES foo(x));
Query OK, 0 rows affected (0.01 sec)

-- Second FOREIGN KEY constraint CANNOT be created with same name
mysql> CREATE TABLE foo4 (x INTEGER, 
    ->   CONSTRAINT foo_fk FOREIGN KEY (x) REFERENCES foo(x));
ERROR 1005 (HY000): Can't CREATE TABLE 'test.foo4' (errno: 121)

From PostgreSQL:

postgres=# CREATE TABLE foo (x INTEGER, 
postgres-#   CONSTRAINT foo_unique UNIQUE (x));
CREATE TABLE

-- Second UNIQUE constraint CANNOT be created with same name
postgres=# CREATE TABLE foo2 (x INTEGER,
postgres-#   CONSTRAINT foo_unique UNIQUE (x));
ERROR:  relation "foo_unique" already exists

postgres=# CREATE TABLE foo3 (x INTEGER,
postgres-#   CONSTRAINT foo_fk FOREIGN KEY (x) REFERENCES foo(x));
CREATE TABLE

-- Second FOREIGN KEY constraint CAN be created with same name
postgres=# CREATE TABLE foo4 (x INTEGER,
postgres-#   CONSTRAINT foo_fk FOREIGN KEY (x) REFERENCES foo(x));
CREATE TABLE

This article was written by Adrian Smith on 7 Feb 2013

Follow me: Facebook | Twitter | Email

More on: FAIL | Databases | MySQL | PostgreSQL