SQL 我可以为主键设置 ignore_dup_key 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2594193/
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
Can I set ignore_dup_key on for a primary key?
提问by Mr. Flibble
I have a two-column primary key on a table. I have attempted to alter it to set the ignore_dup_keyto on with this command:
我在一张桌子上有一个两列主键。我试图改变它以ignore_dup_key使用以下命令将其设置为打开：
ALTER INDEX PK_mypk on MyTable
SET (IGNORE_DUP_KEY = ON);
But I get this error:
但我收到此错误：
Cannot use index option ignore_dup_key to alter index 'PK_mypk' as it enforces a primary or unique constraint.
Cannot use index option ignore_dup_key to alter index 'PK_mypk' as it enforces a primary or unique constraint.
How else should I set IGNORE_DUP_KEYto on?
我还应该如何设置IGNORE_DUP_KEY？
采纳答案by BradC
Its not documented in Books Online, but I've found that while this is valid for Primary Keys, you can't change this with an ALTER INDEX, you'll have to drop and re-create the primary key.
它没有记录在联机丛书中，但我发现虽然这对主键有效，但您不能使用 ALTER INDEX 更改它，您必须删除并重新创建主键。
Keep in mind that this flag doesn't allow you to actually store duplicate rows, it simply changes the error that results:
请记住，此标志不允许您实际存储重复的行，它只是更改导致的错误：
ON
A warning message will occur when duplicate key values are inserted into a unique
index. Only the rows violating the uniqueness constraint will fail.
OFF
An error message will occur when duplicate key values are inserted into a
unique index. The entire INSERT operation will be rolled back.
回答by Kvasi
ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON)
回答by gbn
It determines what happens when you insert duplicates only
它确定仅插入重复项时会发生什么
Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE.
指定插入操作尝试将重复的键值插入唯一索引时的错误响应。IGNORE_DUP_KEY 选项仅适用于创建或重建索引后的插入操作。该选项在执行 CREATE INDEX、ALTER INDEX 或 UPDATE 时无效。
..and it does not apply to PKs
..它不适用于PK
The BOL comment for ALTER TABLE about this and "backwards compatibility" is somewhat confusing. I just tried it and BradC is correct.
ALTER TABLE 关于此和“向后兼容性”的 BOL 注释有些令人困惑。我刚试过，BradC 是正确的。
CREATE TABLE dbo.foo (bar int PRIMARY KEY WITH (FILLFACTOR=90, IGNORE_DUP_KEY = ON))
GO
INSERT dbo.foo VALUES (1)
GO
INSERT dbo.foo VALUES (1)
GO
--gives
(1 row(s) affected)
Duplicate key was ignored.
(0 row(s) affected)
回答by Marcus Adams
Note that this setting only affects what happens if you try to insert a duplicate key, it won't allow you to insert a duplicate key.
请注意，此设置仅影响您尝试插入重复键时发生的情况，它不允许您插入重复键。
If you're attempting to insert duplicate keys, you could drop the primary key index, insert your records, fix up the data (remove duplicates, etc.), then recreate the index.
如果您尝试插入重复键，您可以删除主键索引，插入您的记录，修复数据（删除重复项等），然后重新创建索引。
回答by HLGEM
Personally I never want it to ignore the duplicate. If there is a duplicate value to a primary key, it needs to be fixed. I don't want it ignored and the other records inserted because then the user might think that they all got inserted. This setting is a cover-up for a bad insert process. A well designed process doesn't need this setting as it cleans the data before entering it (or uses upsert to update existing and insert new ones) and sends the bad records to a table so that they can be fixed and reinserted or sends an error back to the user, so they they know their record was not inserted.
我个人从不希望它忽略重复项。如果主键存在重复值，则需要对其进行修复。我不希望它被忽略并插入其他记录，因为这样用户可能会认为它们都被插入了。此设置掩盖了错误的插入过程。设计良好的进程不需要此设置，因为它会在输入数据之前清理数据（或使用 upsert 更新现有数据并插入新数据）并将坏记录发送到表中，以便修复并重新插入或发送错误返回给用户，让他们知道他们的记录没有被插入。
回答by Daniel C.
Drop the PK and then recreate it
删除 PK 然后重新创建它
ALTER TABLE TableName DROP CONSTRAINT PK_TableName
GO
ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED
( MyIDColumn ASC )
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SEE -> https://eitanblumin.com/2018/10/28/the-ignore_dup_key-option-in-primary-keys-and-unique-indexes/
见-> https://eitanblumin.com/2018/10/28/the-ignore_dup_key-option-in-primary-keys-and-unique-indexes/
