CODEWITHSUNDEEP
postgresqluniqueconstraintsdatabase-migration
Cam Collins
Cam Collins

Reputation: 61

Unique Constraint error creating tables in PostgreSQL (migrated from MySQL)

I read with great enthusiasm the question entitled Migrate from MySQL to PostgreSQL on Linux (Kubuntu). The Star Wars theme made it that much more entertaining. But I've run into an issue regarding Unique Constraints in PostgreSQL.

I followed the above post closely to create a PostgreSQL DDL using sqlt. The thought process was to create the schema/tables first and then import the data. However 57 of my 72 tables use CONSTRAINT "userid" UNIQUE ("user_id", "key")

Here is an example of one of the tables: 

CREATE TABLE "account_otherserviceinfo" (
    "id" serial NOT NULL,
    "user_id" bigint NOT NULL,
    "key" character varying(50) NOT NULL,
    "value" text NOT NULL,
    PRIMARY KEY ("id"),
    CONSTRAINT "user_id" UNIQUE ("user_id", "key")
);

When I copy these tables into my PostgreSQL DB using the Query tool in pgadmin3, I get the following error:

ERROR: relation "user_id" already exists SQL state: 42P07

I did not design this database schema. I am only helping with the migration process. When reading the documentation on Unique Constraints, it appears that it is Ok to use the same name as long as it's in a different table. http://www.postgresql.org/docs/8.3/static/ddl-constraints.html. Am I misinterpreting this?

Any suggestions or pointers would be greatly appreciated.

Thank you!

PS: Thanks https://stackoverflow.com/users/59087/dave-jarvis and https://stackoverflow.com/users/26534/michael-trausch for getting me this far ;-)

Upvotes: 4

Views: 3875

Answers (3)

Kuberchaun
Kuberchaun

Reputation: 30332

You can get around this type of issue by giving your constraints more detailed names.

You will need to come up with a naming standard for you database objects to avoid this type of issue. Maybe something like type_schema_tablename_columnname. So for example uidx_public_account_otherserviceinfor_user_id_key. That type of name will make sure you don't have issues and makes it easy to figure out what object an error message is referring to. You can debate the clearest way to implement what I said, but the key point is come up with a standard to use for all objects that works for your enviroment.

Upvotes: 3

Toto
Toto

Reputation: 91488

You should not use user_id as constraint name because it is already used as column name.

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95632

When reading the documentation on Unique Constraints, it appears that it is Ok to use the same name as long as it's in a different table.

I'm not sure what part of the documentation you're reading, but you're misinterpreting it. Constraint names have to be globally unique. So you can have as many of these UNIQUE ("user_id", "key") as you like, but you can't name every one of them "user_id".

Upvotes: 5

Related Questions