postgresql 重复键违反了唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4448340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql duplicate key violates unique constraint
提问by red
Hi i have a question i know this was posted many times but i didn't find an answer to my problem. The problem is that i have a table and a column "id" i want it to be unique number just as normal. This type of column is serial and the next value after each insert is comming from a sequence so everything seems to be all right but it still sometimes showing this error. I dont know why ? In the documentation it is writtne the sequence is fool prof and always works. If I add a UNIQUE constraint to that column will it thelp ? I worked before many times on Postres but this error is showing for me for the first time. I did everything as normal and i never had this problem before. Can you help me to find the answer that can be used in the future for all tables that will be created ? Lets say we have something easy like this:
嗨，我有一个问题，我知道这已经发布了很多次，但我没有找到问题的答案。问题是我有一个表和一个列“id”，我希望它像往常一样是唯一的数字。这种类型的列是串行的，每次插入后的下一个值来自一个序列，所以一切似乎都没有问题，但有时仍会显示此错误。我不知道为什么？在文档中，它的顺序是傻瓜教授并且总是有效。如果我向该列添加 UNIQUE 约束，它会解决吗？我以前在 Postres 上工作过很多次，但这个错误是第一次向我显示。我一切正常，以前从未遇到过这个问题。你能帮我找到将来所有要创建的表都可以使用的答案吗？假设我们有这样简单的事情：
CREATE TABLE comments
(
id serial NOT NULL,
some_column text NOT NULL,
CONSTRAINT id_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE interesting.comments OWNER TO postgres;
If i add:
如果我添加：
ALTER TABLE comments ADD CONSTRAINT id_id_key UNIQUE(id)
Will if be enought or is there some other thing that should be done ?
如果足够了，还是应该做一些其他的事情？
回答by adamo
This article explainsthat your sequence might be out of sync and that you have to manually bring it back in sync.
本文解释了您的序列可能不同步，您必须手动将其恢复同步。
An excerpt from the article in case the URL changes:
文章摘录以防 URL 更改：
If you get this message when trying to insert data into a PostgreSQL database:
ERROR: duplicate key violates unique constraintThat likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:
SELECT MAX(the_primary_key) FROM the_table; SELECT nextval('the_primary_key_sequence');If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);That will set the sequence to the next available value that's higher than any existing primary key in the sequence.
如果您在尝试将数据插入 PostgreSQL 数据库时收到此消息：
ERROR: duplicate key violates unique constraint这可能意味着您正在使用的表中的主键序列以某种方式变得不同步，可能是因为大规模导入过程（或类似的事情）。称之为“设计错误”，但似乎您必须在从转储文件恢复后手动重置主键索引。无论如何，要查看您的值是否不同步，请运行以下两个命令：
SELECT MAX(the_primary_key) FROM the_table; SELECT nextval('the_primary_key_sequence');如果第一个值高于第二个值，则您的序列不同步。备份你的PG数据库（以防万一），然后运行这个L
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);这会将序列设置为高于序列中任何现有主键的下一个可用值。
回答by naskar
For future searchs, use ON CONFLICT DO NOTHING.
对于未来的搜索，请使用 ON CONFLICT DO NOTHING。
回答by Stephen Denne
The primary key is already protecting you from inserting duplicate values, as you're experiencing when you get that error. Adding another unique constraint isn't necessary to do that.
主键已经在保护您免于插入重复值，正如您在遇到该错误时所遇到的那样。不需要添加另一个唯一约束来做到这一点。
The "duplicate key" error is telling you that the work was not done because it would produce a duplicate key, not that it discovered a duplicate key already commited to the table.
“重复键”错误告诉您工作没有完成，因为它会产生一个重复的键，而不是它发现了一个已经提交到表中的重复键。
回答by stacker
From http://www.postgresql.org/docs/current/interactive/datatype.html
来自http://www.postgresql.org/docs/current/interactive/datatype.html
Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type.
注意：在 PostgreSQL 7.3 之前，串行表示 UNIQUE。这不再是自动的。如果您希望串行列位于唯一约束或主键中，则现在必须指定它，与任何其他数据类型相同。
回答by Keval
参考 - https://www.calazan.com/how-to-reset-the-primary-key-sequence-in-postgresql-with-django/
I had the same problem try this:
python manage.py sqlsequencereset table_name
我有同样的问题试试这个：
python manage.py sqlsequencereset table_name
Eg:
例如：
python manage.py sqlsequencereset auth
you need to run this in production settings(if you have) and you need Postgres installed to run this on the server
您需要在生产设置中运行它（如果有）并且需要安装 Postgres 才能在服务器上运行它
回答by Randy Sumampong
On the first place, I thought the problem was the sequence :). But the error says ERROR: duplicate key value violates unique constraint "ndxregnum".. So I look for the name "ndxregnum" from my database. I found it under my table indexes. Then I delete "ndxregnum". Then error is gone. everything back to normal.
首先，我认为问题在于顺序:)。但是错误说错误：重复的键值违反了唯一约束“ndxregnum”..所以我从我的数据库中查找名称“ndxregnum”。我在我的表索引下找到了它。然后我删除“ndxregnum”。然后错误消失了。一切恢复正常。
I hope this will help you...
我希望这能帮到您...
