正確刪除 SQL Server 表中的重復(fù)行的實(shí)際操作步驟
此文章主要是教會大家如何正確刪除 SQL Server 表中的重復(fù)行，我們大家都知道Microsoft SQL Server 數(shù)據(jù)庫中的實(shí)際應(yīng)用表其不應(yīng)該包含重復(fù)行與非唯一主鍵。為簡潔起見，在本文中我們有時稱主鍵為“鍵”或“PK”，但這始終表示“主鍵”。
重復(fù)的 PK 違反了實(shí)體完整性，在關(guān)系系統(tǒng)中是不允許的。SQL Server 有各種強(qiáng)制執(zhí)行實(shí)體完整性的機(jī)制，包括索引、唯一約束、主鍵約束和觸發(fā)器。
盡管如此，在某些情況下還可能會出現(xiàn)重復(fù)的主鍵;如果出現(xiàn)此類情況，就必須清除重復(fù)主鍵。出現(xiàn)重復(fù)主鍵的情形之一是，在 SQL Server 外部的非關(guān)系數(shù)據(jù)中存在重復(fù)的 PK，在導(dǎo)入這些數(shù)據(jù)時沒有強(qiáng)制執(zhí)行 PK 唯一性。出現(xiàn)重復(fù)主鍵的另一種情形來自數(shù)據(jù)庫設(shè)計(jì)錯誤，如沒有對每張表強(qiáng)制執(zhí)行實(shí)體完整性。
通常在嘗試創(chuàng)建唯一索引時會發(fā)現(xiàn)重復(fù)的 PK，因?yàn)槿绻业街貜?fù)的鍵，唯一索引的創(chuàng)建即會中止，并且將顯示以下消息：
- Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.
如果使用的是 SQL Server 2000 或 SQL Server 2005，則會收到以下錯誤消息：
- 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.
本文討論如何查找和刪除 SQL Server 表中的重復(fù)的主鍵。但是，您應(yīng)該仔細(xì)檢查出現(xiàn)重復(fù)鍵的進(jìn)程以避免重復(fù)出現(xiàn)。
更多信息
在該示例中，我們將使用下表，它具有重復(fù)的 PK 值。在該表中，主鍵是兩列(col1、col2)。我們無法創(chuàng)建唯一索引或主鍵約束，因?yàn)檫@兩行具有重復(fù)的 PK。該過程演示如何識別和刪除 SQL Server 表重復(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')
第一步是識別哪些行具有重復(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 值，則最佳方法是手動逐個刪除它們。例如：
- set rowcount 1
- delete from t1
- where col1=1 and col2=1
rowcount 值應(yīng)該是給定鍵值的重復(fù)數(shù)減去 1。在該示例中，有 2 個重復(fù)的主鍵，所以 rowcount 被設(shè)置為 1。col1/col2 值來自上面的 GROUP BY 查詢結(jié)果。如果 GROUP BY 查詢返回多行，則“set rowcount”查詢將必須為這些行中的每一行各運(yùn)行一次。每次運(yùn)行該查詢時，將 rowcount 設(shè)置為特定 PK 值的重復(fù)數(shù)減去 1。
在刪除行之前，您應(yīng)該驗(yàn)證是否整行重復(fù)。雖然整行重復(fù)不太可能發(fā)生，但可能 PK 值重復(fù)，而整行不重復(fù)。例如一個將身份證號碼作為主鍵的表，該表有兩個具有相同號碼的不同的人(即行)，但每個人有唯一的屬性。在這種情況下，任何引起重復(fù)鍵的問題可能還引起在行中放入有效的唯一的數(shù)據(jù)。在刪除該數(shù)據(jù)之前，應(yīng)該將該數(shù)據(jù)復(fù)制出來并保存下來以進(jìn)行研究和適當(dāng)?shù)恼{(diào)整。
如果表中有多組完全不同的重復(fù)的 PK 值，則逐個刪除 SQL Server 表中的重復(fù)行會很費(fèi)時間。在這種情況下，可使用下面的方法： 1.首先，運(yùn)行上面的 GROUP BY 查詢來確定有多少組重復(fù)的 PK 值及每組的重復(fù)數(shù)。
2.選擇重復(fù)的鍵值放入臨時表中。例如：
- SELECT col1, col2, col3=count(*)
- INTO holdkey
- FROM t1
- GROUP BY col1, col2
- HAVING count(*) > 1
3.選擇重復(fù)的行放入臨時表中，以清除進(jìn)程中的重復(fù)值。例如：
- SELECT DISTINCT t1.*
- INTO holddups
- FROM t1, holdkey
- WHERE t1.col1 = holdkey.col1
- AND t1.col2 = holdkey.col2
4.此時，holddups 表應(yīng)有唯一的 PK;但是，如果 t1 有重復(fù)的 PK 而行唯一(如上面的 SSN 示例)，情況就不是這樣了。請驗(yàn)證 holddups 中的各個鍵是否唯一，是否沒有鍵重復(fù)而行唯一的情況。如果是這樣，您必須停在該處，確定對于給定重復(fù)的鍵值，您希望保留哪些行。例如，以下查詢：
- SELECT col1, col2, count(*)
- FROM holddups
- GROUP BY col1, col2
應(yīng)為各行返回計(jì)數(shù) 1。如果結(jié)果是 1，請繼續(xù)執(zhí)行下面的步驟 5。如果不是 1，則存在鍵重復(fù)而行唯一的情況，且需要您決定要保存哪些行。通常，這將需要舍棄行或?yàn)榇诵袆?chuàng)建新的唯一的鍵值。為 holddups 表中每個此種重復(fù) PK 執(zhí)行這兩個步驟之一。
5.從原始表中刪除 SQL Server 表中的重復(fù)行。例如：
- DELETE t1
- FROM t1, holdkey
- WHERE t1.col1 = holdkey.col1
- AND t1.col2 = holdkey.col2
6.將唯一行放回原始表中。例如：
- INSERT t1 SELECT * FROM holddups
上述的相關(guān)內(nèi)容就是對刪除 SQL Server 表中的重復(fù)行的描述，希望會給你帶來一些幫助在此方面。
【編輯推薦】
