Constraints - Relation Already Exists Issue - MySQL to PostgreSQL Migration

When migrating tables from MySQL to PostgreSQL you can notice “ERROR: relation “constraint_name” already exists” error. For example,

MySQL:

  CREATE TABLE u1 (c1 INT, CONSTRAINT un UNIQUE(c1));
  # Query OK, 0 rows affected (0.10 sec)
 
  CREATE TABLE u2 (c1 INT, CONSTRAINT un UNIQUE(c1));
  # Query OK, 0 rows affected (0.04 sec)

But the same code fails in PostgreSQL, and the second table cannot be created:

PostgreSQL:

  CREATE TABLE u1 (c1 INT, CONSTRAINT un UNIQUE(c1));
  # CREATE TABLE ok
 
  CREATE TABLE u2 (c1 INT, CONSTRAINT un UNIQUE(c1));
  # ERROR:  relation "un" already exists

PostgreSQL does allow using the same constraint names for different tables, for example, you are able to create a table with a CHECK constraint:

PostgreSQL:

  CREATE TABLE u3 (c1 INT, CONSTRAINT un CHECK(c1 > 0));
  # CREATE TABLE ok

The problem is that PostgreSQL creates an index for every UNIQUE constraint and its name is the constraint name. But index names are unique within the schema, not within the table, that's why you cannot create an index with the same name and get “relation already exist” error.

Renaming the constraint name solves this problem:

PostgreSQL:

  CREATE TABLE u2 (c1 INT, CONSTRAINT un2 UNIQUE(c1));
  # CREATE TABLE ok

For more information, see MySQL to PostgreSQL Migration Reference.