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

Question

I read with great enthusiasm a question called Migrating from MySQL to PostgreSQL on Linux (Kubuntu) . The theme of Star Wars made it much more interesting. But I had a problem with Unique Constraints in PostgreSQL.

I carefully followed the post above to create 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 to my PostgreSQL database 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 only help in the migration process. When you read the documentation about unique constraints, it seems that using one name is the same as in another table. http://www.postgresql.org/docs/8.3/static/ddl-constraints.html Am I misinterpreting this?

Any suggestions or pointers are welcome.

Thanks!

PS: Thank you https://stackoverflow.com/users/59087/dave-jarvis and https://stackoverflow.com/users/26534/michael-trausch for going this far; -)

Answer 1

When reading documentation on Unique Constraints, it seems like it's good to use the same name while it's in a different table.

I'm not sure what part of the documentation you are reading, but you are misinterpreting it. Constraint names must be globally unique. Thus, you can have as many of these UNIQUE ("user_id", "key") as you want, but you cannot name each of them "user_id" .

Answer 2

You can work around this problem by specifying your limitations with more detailed names.

You will need to develop a naming standard for database objects to avoid this type of problem. Maybe something like type_schema_tablename_columnname. So, for example, uidx_public_account_otherserviceinfor_user_id_key. This type of name ensures that you have no problems, and makes it easy to determine which object the error message refers to. You can discuss the clearest way to implement what I said, but the key point is the standard used for all objects that work for your environment.

Answer 3

user_id cannot be used as a constraint name because it is already used as a column name.