Constraint name visibility on MySQL and PostgreSQL

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
P.S. I recently created a nerdy privacy-respecting tool called When Will I Run Out Of Money? It's available for free if you want to check it out.

This article is © Adrian Smith.
It was originally published on 7 Feb 2013
More on: FAIL | Databases | MySQL | PostgreSQL