在 PostgreSQL 中创建表时出现唯一约束错误（从 MySQL 迁移）
我以极大的热情阅读了题为 在 Linux 上从 MySQL 迁移到 PostgreSQL 的问题（库本图）。 星球大战主题让它变得更加有趣。但我遇到了有关 PostgreSQL 中唯一约束的问题。
我严格按照上面的帖子使用 sqlt 创建 PostgreSQL DDL。思考过程是首先创建架构/表，然后导入数据。然而，我的 72 个表中有 57 个使用 CONSTRAINT "userid" UNIQUE ("user_id", "key")
这是其中一个表的示例：
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")
);
当我复制这些表时使用 pgadmin3 中的查询工具进入我的 PostgreSQL 数据库，出现以下错误：
错误：关系“user_id”已存在 SQL状态：42P07
我没有设计这个数据库模式。我只是帮助完成迁移过程。在阅读有关唯一约束的文档时，似乎可以使用相同的名称，只要它位于不同的表中即可。 http://www.postgresql.org/docs/8.3/static/ ddl-constraints.html。我是否误解了这一点？
任何建议或指示将不胜感激。
谢谢你！
PS：谢谢 https://stackoverflow.com/users/59087/dave-jarvis 和 https://stackoverflow.com/users/26534/michael-trausch 让我走到这一步;-)
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 ;-)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不确定您正在阅读文档的哪一部分，但您误解了它。约束名称必须是全局唯一的。因此，您可以根据需要拥有任意多个
UNIQUE ("user_id", "key")，但您不能为每一个都命名“user_id”。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".您可以通过为约束指定更详细的名称来解决此类问题。
您需要为数据库对象制定命名标准以避免此类问题。也许类似于 type_schema_tablename_columnname。例如 uidx_public_account_otherserviceinfor_user_id_key。这种类型的名称将确保您不会遇到问题，并且可以轻松找出错误消息所指的对象。您可以讨论实现我所说的最清晰的方法，但关键点是提出一个标准，用于适用于您的环境的所有对象。
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.
您不应使用
user_id作为约束名称，因为它已用作列名称。You should not use
user_idas constraint name because it is already used as column name.