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

Question

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 ;-)

Answer 1

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".