SQL 如何在主键列中插入重复值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36429229/
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
How to insert a duplicate value in primary key column?
提问by arun
I have a table that has primary key on first column, but when I see the data available of this table, I find that records are repeated include primary key data. I want to know how can it be possible? Does primary column has repeated data in SQL Server 2008?
我有一个在第一列上有主键的表，但是当我看到这个表的可用数据时，我发现重复的记录包括主键数据。我想知道怎么可能？SQL Server 2008 中主列是否有重复数据？
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DemoTbl](
[ProcedureId] [int] NOT NULL,
[ProcedureName] [nvarchar](100) NOT NULL,
[VersionNo] [char](5) NULL,
[PublishDate] [datetime] NULL,
[PublishUser] [varchar](50) NULL,
[SpecialtyId] [int] NOT NULL,
[ProcedureNumber] [varchar](20) NULL,
[PowerpointName] [nvarchar](100) NULL,
[Duration] [int] NOT NULL,
[LanguageId] [int] NOT NULL,
[TierId] [smallint] NOT NULL,
[PrintPdf] [bit] NULL,
[PresentationModeId] [tinyint] NULL,
CONSTRAINT [pk_DemoTbl] PRIMARY KEY CLUSTERED
(
[ProcedureId] ASC,
[LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DemoTbl] WITH CHECK ADD FOREIGN KEY([PresentationModeId])
REFERENCES [dbo].[DemoTbl] ([PresentationModeId])
GO
ALTER TABLE [dbo].[DemoTbl] WITH CHECK ADD CONSTRAINT [FK_DemoTbl_Specialty] FOREIGN KEY([SpecialtyId])
REFERENCES [dbo].[Specialty] ([SpecialtyId])
GO
ALTER TABLE [dbo].[DemoTbl] CHECK CONSTRAINT [FK_DemoTbl_Specialty]
GO
ALTER TABLE [dbo].[DemoTbl] WITH CHECK ADD CONSTRAINT [FK_DemoTbl_TierMaster] FOREIGN KEY([TierId])
REFERENCES [dbo].[TierMaster] ([TierId])
GO
ALTER TABLE [dbo].[DemoTbl] CHECK CONSTRAINT [FK_DemoTbl_TierMaster]
GO
ALTER TABLE [dbo].[DemoTbl] ADD CONSTRAINT [DF_DemoTbl_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[DemoTbl] ADD CONSTRAINT [DF_DemoTbl_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[DemoTbl] ADD CONSTRAINT [DF_DemoTbl_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[DemoTbl] ADD CONSTRAINT [DF_DemoTbl_LanguageId] DEFAULT ((1)) FOR [LanguageId]
GO
ALTER TABLE [dbo].[DemoTbl] ADD CONSTRAINT [DF_DemoTbl_ManageContent] DEFAULT ('false') FOR [ManageContent]
GO
ALTER TABLE [dbo].[DemoTbl] ADD CONSTRAINT [DF__Procedure__Print__1B14C01D] DEFAULT ((1)) FOR [PrintPdf]
GO
回答by Tab Alleman
No, it is not possible in SQL Server to insert records that have duplicate values in the Primary Key.
不，在 SQL Server 中无法插入主键中具有重复值的记录。
回答by Martin GM
It is not possible to have more than one row in the same table with the same primary key (PK).
同一个表中不可能有多于一行且具有相同的主键 (PK)。
If you are doing a SELECT and getting more than one row with the same PK could be because:
如果您正在执行 SELECT 并获得多于一行的相同 PK 可能是因为：
That table actually does not have a PK, maybe it was not properly created. You can check this by doing the following (it should return the PK column(s)):
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'TableName' AND COLUMN_KEY='PRI';
The SELECT statement is not correct. Maybe you are selecting from two or more tables and you are not relating them correctly.
该表实际上没有 PK，也许它没有正确创建。您可以通过执行以下操作来检查这一点（它应该返回 PK 列）：
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'TableName' AND COLUMN_KEY='PRI';
SELECT 语句不正确。也许您正在从两个或多个表格中进行选择，并且您没有正确关联它们。
To clarify, you can do the following:
1- Create a new table:
澄清一下，您可以执行以下操作：
1- 创建一个新表：
CREATE TABLE IF NOT EXISTS `Customer` (
`id` int(4),
`name` varchar(20),
`surname` varchar(40),
`phone` int(9),
PRIMARY KEY (`id`)
) ENGINE=INNODB;
Now you have a new table for storing customers identified by an ID.
2- Let's add some customers:
现在您有一个用于存储由 ID 标识的客户的新表。
2- 让我们添加一些客户：
INSERT INTO Customer VALUES(111, 'John', 'White', 123456789),
(222, 'Bianca', 'Williams', 987654321),
(333, 'Mikel', 'Peterson', 421345642),
(444, 'Jolene', 'Gomez', 948113552);
3- If you try to insert a customer with an existing PK (id) you will get an error:
3- 如果您尝试插入具有现有 PK (id) 的客户，您将收到错误消息：
INSERT INTO Customer VALUES (222, 'Paul', 'Brown', 123412345);
4- In order to check the final table, you can do the following select:
4- 为了检查决赛桌，您可以执行以下选择：
SELECT * FROM Customer;
回答by jamiedanq
No it is not possible to have duplicate primary keys if the primary key constrainthas been set to True.
不，如果primary key constraint已设置为，则不可能有重复的主键True。
Further to prevent duplicate primary keys set the Identity Specificationto True
进一步防止重复的主键设置Identity Specification为True
回答by marco
If this table is an intermediate table in a many to many connection the original columns are foreign keys and able to accept many instances of the same prodId(the whole point of the exercise). If someone then slams a PK on that column then no more duplicates can be added but if you query it the table will still return the original stuff in this guys screenshot.
如果此表是多对多连接中的中间表，则原始列是外键并且能够接受相同 prodId 的许多实例（练习的重点）。如果有人然后在该列上猛击 PK，则无法添加更多重复项，但是如果您查询它，该表仍将返回此人屏幕截图中的原始内容。
This scenario can be seen in the Northwind sample database between Products and orders (OrderDetails table has composite PK on OrderID, ProdID but still shows duplicates of both). The PK was added afterwards, after data had been added.
这种情况可以在 Northwind 示例数据库中的 Products 和 orders 中看到（OrderDetails 表在 OrderID、ProdID 上具有复合 PK，但仍显示两者的重复项）。PK 是在添加数据后添加的。
The intermediate table is a link between the Many-to-Many tables and in general doesn't need a PK. Normalization 101.
中间表是多对多表之间的链接，一般不需要PK。规范化 101。
回答by KashifKiani
For PK Constraint it create a Unique Index (Clustered / non Clustered ) on defined PK. If we disable indexes before loading that include PK Unique Index as well than we can insert duplicate PK in the column because the PK constraint is disabled. So while disabling Indexes please avoid PK index not be disable "is_primary_key=0 to disable all other indexes"
对于 PK 约束，它在定义的 PK 上创建一个唯一索引（集群/非集群）。如果我们在加载之前禁用包含 PK 唯一索引的索引，那么我们可以在列中插入重复的 PK，因为禁用了 PK 约束。因此，在禁用索引时，请避免 PK 索引不要禁用“is_primary_key=0 以禁用所有其他索引”
