Microsoft SQL Server 表不應(yīng)該包含重復(fù)行和非唯一主鍵。為簡(jiǎn)潔起見(jiàn)，在本文中我們有時(shí)稱主鍵為“鍵”或“PK”，但這始終表示“主鍵”。重復(fù)的 PK 違反了實(shí)體完整性，在關(guān)系系統(tǒng)中是不允許的。SQL Server 有各種強(qiáng)制執(zhí)行實(shí)體完整性的機(jī)制，包括索引、唯一約束、主鍵約束和觸發(fā)器。
盡管如此，在某些情況下還可能會(huì)出現(xiàn)重復(fù)的主鍵;如果出現(xiàn)此類(lèi)情況，就必須清除重復(fù)主鍵。出現(xiàn)重復(fù)主鍵的情形之一是，在 SQL Server 外部的非關(guān)系數(shù)據(jù)中存在重復(fù)的 PK，在導(dǎo)入這些數(shù)據(jù)時(shí)沒(méi)有強(qiáng)制執(zhí)行 PK 唯一性。出現(xiàn)重復(fù)主鍵的另一種情形來(lái)自數(shù)據(jù)庫(kù)設(shè)計(jì)錯(cuò)誤，如沒(méi)有對(duì)每張表強(qiáng)制執(zhí)行實(shí)體完整性。
通常在嘗試創(chuàng)建唯一索引時(shí)會(huì)發(fā)現(xiàn)重復(fù)的 PK，因?yàn)槿绻业街貜?fù)的鍵，唯一索引的創(chuàng)建即會(huì)中止，并且將顯示以下消息：
Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.
如果使用的是 SQL Server 2000 或 SQL Server 2005，則會(huì)收到以下錯(cuò)誤消息：
Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name '%.*ls' and index name '%.*ls'.The duplicate key value is %ls.
本文討論如何查找和刪除表中重復(fù)的主鍵。但是，您應(yīng)該仔細(xì)檢查出現(xiàn)重復(fù)鍵的進(jìn)程以避免重復(fù)出現(xiàn)。
更多信息
在該示例中，我們將使用下表，它具有重復(fù)的 PK 值。在該表中，主鍵是兩列(col1、col2)。我們無(wú)法創(chuàng)建唯一索引或主鍵約束，因?yàn)檫@兩行具有重復(fù)的 PK。該過(guò)程演示如何識(shí)別和刪除重復(fù)的主鍵。
復(fù)制代碼 代碼如下:
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
第一步是識(shí)別哪些行具有重復(fù)的主鍵值：
復(fù)制代碼 代碼如下:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
這將為表中的每組重復(fù)的 PK 值返回一行。此結(jié)果中的最后一列是特定 PK 值的重復(fù)數(shù)。
col1 col2
1 1 2
如果只有幾組重復(fù)的 PK 值，則最佳方法是手動(dòng)逐個(gè)刪除它們。例如：
復(fù)制代碼 代碼如下:
set rowcount 1
delete from t1
where col1=1 and col2=1
rowcount 值應(yīng)該是給定鍵值的重復(fù)數(shù)減去 1。在該示例中，有 2 個(gè)重復(fù)的主鍵，所以 rowcount 被設(shè)置為 1。col1/col2 值來(lái)自上面的 GROUP BY 查詢結(jié)果。如果 GROUP BY 查詢返回多行，則“set rowcount”查詢將必須為這些行中的每一行各運(yùn)行一次。每次運(yùn)行該查詢時(shí)，將 rowcount 設(shè)置為特定 PK 值的重復(fù)數(shù)減去 1。
在刪除行之前，您應(yīng)該驗(yàn)證是否整行重復(fù)。雖然整行重復(fù)不太可能發(fā)生，但可能 PK 值重復(fù)，而整行不重復(fù)。例如一個(gè)將身份證號(hào)碼作為主鍵的表，該表有兩個(gè)具有相同號(hào)碼的不同的人(即行)，但每個(gè)人有唯一的屬性。在這種情況下，任何引起重復(fù)鍵的問(wèn)題可能還引起在行中放入有效的唯一的數(shù)據(jù)。在刪除該數(shù)據(jù)之前，應(yīng)該將該數(shù)據(jù)復(fù)制出來(lái)并保存下來(lái)以進(jìn)行研究和適當(dāng)?shù)恼{(diào)整。
如果表中有多組完全不同的重復(fù)的 PK 值，則逐個(gè)刪除它們會(huì)很費(fèi)時(shí)間。在這種情況下，可使用下面的方法： 1.首先，運(yùn)行上面的 GROUP BY 查詢來(lái)確定有多少組重復(fù)的 PK 值及每組的重復(fù)數(shù)。
2.選擇重復(fù)的鍵值放入臨時(shí)表中。例如：
復(fù)制代碼 代碼如下:
SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
3.選擇重復(fù)的行放入臨時(shí)表中，以清除進(jìn)程中的重復(fù)值。例如：
復(fù)制代碼 代碼如下:
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
4.此時(shí)，holddups 表應(yīng)有唯一的 PK;但是，如果 t1 有重復(fù)的 PK 而行唯一(如上面的 SSN 示例)，情況就不是這樣了。請(qǐng)驗(yàn)證 holddups 中的各個(gè)鍵是否唯一，是否沒(méi)有鍵重復(fù)而行唯一的情況。如果是這樣，您必須停在該處，確定對(duì)于給定重復(fù)的鍵值，您希望保留哪些行。例如，以下查詢：
復(fù)制代碼 代碼如下:
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2
應(yīng)為各行返回計(jì)數(shù) 1。如果結(jié)果是 1，請(qǐng)繼續(xù)執(zhí)行下面的步驟 5。如果不是 1，則存在鍵重復(fù)而行唯一的情況，且需要您決定要保存哪些行。通常，這將需要舍棄行或?yàn)榇诵袆?chuàng)建新的唯一的鍵值。為 holddups 表中每個(gè)此種重復(fù) PK 執(zhí)行這兩個(gè)步驟之一。
5.從原始表中刪除重復(fù)的行。例如：
復(fù)制代碼 代碼如下:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
6.將唯一行放回原始表中。例如：
INSERT t1 SELECT * FROM holddups
您可能感興趣的文章:- 刪除DataTable重復(fù)列，只刪除其中的一列重復(fù)行的解決方法
- 分享SQL Server刪除重復(fù)行的6個(gè)方法
- sqlserver利用存儲(chǔ)過(guò)程去除重復(fù)行的sql語(yǔ)句
- 刪除Table表中的重復(fù)行的方法
- 批處理 刪除重復(fù)行的代碼
- 用vbscript實(shí)現(xiàn)從文本文件中刪除所有重復(fù)行的代碼
- sql里將重復(fù)行數(shù)據(jù)合并為一行數(shù)據(jù)使用逗號(hào)進(jìn)行分隔
