Bagaimana menghapus duplikat dari tabel di SQL Server

Microsoft SQL Server tabel tidak pernah harus berisi duplikasi baris, atau non-unik kunci primer. Untuk singkatnya, kita akan kadang-kadang merujuk utama kunci sebagai "kunci" atau "PK" di artikel ini, tetapi ini akan selalu menunjukkan "utama kunci." PKs duplikat pelanggaran entitas integritas, dan harus batasan dalam sistem relasional. SQL Server memiliki berbagai mekanisme untuk menegakkan entitas integritas, termasuk indeks, unik kendala, PRIMARY KEY kendala, dan memicu.

Meskipun demikian, di bawah kondisi yang tidak biasa duplikat kunci primer dapat terjadi, dan jika demikian mereka harus dihilangkan. Salah satu cara mereka dapat terjadi adalah jika duplikat PKs ada di non data relasional di luar SQL Server, dan data diimpor sementara PK keunikan tidak ditegakkan. Cara lain yang mereka dapat terjadi melalui sebuah desain database error, seperti tidak menegakkan entitas integritas di setiap meja.

Sering duplikat PKs menyadari ketika Anda mencoba untuk membuat indeks unik, yang akan membatalkan jika duplikat kunci ditemukan. Pesan ini adalah:Jika Anda menggunakan SQL Server 2000 atau SQL Server 2005, Anda mungkin menerima pesan galat berikut:Artikel ini membahas cara untuk mencari dan menghapus duplikat kunci primer dari meja. Namun, Anda harus dekat memeriksa proses yang memungkinkan duplikat terjadi untuk mencegah terulangnya.

Untuk contoh ini, kita akan menggunakan tabel berikut dengan nilai-nilai PK duplikat. Dalam tabel ini kunci utama adalah dua kolom (col1, col2). Kita tidak bisa membuat indeks unik atau PRIMARY KEY kendala karena dua baris memiliki PKs duplikat. Prosedur ini menggambarkan bagaimana mengidentifikasi dan menghapus duplikat.Langkah pertama adalah untuk mengidentifikasi baris yang memiliki nilai-nilai kunci primer duplikat:Ini akan kembali satu baris untuk setiap rangkaian duplikat PK nilai dalam tabel. Kolom terakhir dalam hasil ini adalah jumlah duplikat untuk nilai PK tertentu.



Jika hanya beberapa set nilai PK duplikat, prosedur terbaik adalah untuk menghapus ini secara manual secara individual. Misalnya:Nilai rowcount harus n-1 jumlah duplikat untuk nilai kunci tertentu. Dalam contoh ini, ada 2 duplikat begitu rowcount diatur ke 1. Col1/col2 nilai yang diambil dari di atas GROUP BY query result. Jika grup oleh permintaan kembali beberapa baris, permintaan "mengatur rowcount" akan harus dijalankan sekali untuk masing-masing baris ini. Setiap kali menjalankan, mengatur rowcount n-1 jumlah duplikat dari nilai PK tertentu.

Sebelum menghapus baris, Anda harus memverifikasi bahwa seluruh baris duplikat. Meskipun tidak mungkin, mungkin bahwa nilai-nilai PK duplikat, Namun baris secara keseluruhan tidak. Contoh ini akan meja dengan Nomor jaminan sosial sebagai primary key, dan memiliki dua orang yang berbeda (atau baris) dengan nomor yang sama, masing-masing memiliki atribut yang unik. Sedemikian kasus kerusakan apa pun yang menyebabkan tombol duplikat mungkin juga disebabkan data unik yang benar harus diletakkan di baris. Data ini harus disalin dan diawetkan untuk studi dan mungkin rekonsiliasi sebelum untuk menghapus data.

Jika ada banyak berbeda set duplikat PK nilai dalam tabel, mungkin menjadi terlalu memakan waktu untuk menghapusnya secara individual. Dalam hal ini mengikuti prosedur dapat digunakan:

1. Pertama, berjalan di atas kelompok oleh permintaan untuk menentukan berapa banyak set duplikat PK nilai ada, dan menghitung duplikat untuk setiap set.
2. Pilih nilai-nilai kunci duplikat ke dalam tabel memegang. Misalnya:

   SELECT col1, col2, col3=count(*)
   INTO holdkey
   FROM t1
   GROUP BY col1, col2
   HAVING count(*) > 1
   					

3. Pilih duplikasi baris ke dalam tabel memegang, menghilangkan duplikasi dalam proses. Misalnya:

   SELECT DISTINCT t1.*
   INTO holddups
   FROM t1, holdkey
   WHERE t1.col1 = holdkey.col1
   AND t1.col2 = holdkey.col2
   					

4. Pada titik ini, tabel holddups harus memiliki unik PKs, namun, ini tidak akan terjadi jika t1 duplikat PKs, namun baris unik (seperti dalam SSN contoh di atas). Verifikasi bahwa setiap key dalam holddups unik, dan bahwa Anda tidak memiliki kunci duplikat, namun unik baris. Jika demikian, Anda harus berhenti di sini dan mendamaikan yang baris Anda ingin tetap untuk nilai kunci duplikat yang diberikan. Misalnya, permintaan:

   SELECT col1, col2, count(*)
   FROM holddups
   GROUP BY col1, col2
   						

   harus kembali hitungan 1 untuk setiap baris. Jika ya, lanjutkan ke langkah 5 di bawah ini. Jika tidak, Anda memiliki kunci duplikat, namun unik baris, dan perlu memutuskan yang baris untuk menyelamatkan. Ini biasanya akan memerlukan membuang berturut-turut, atau menciptakan nilai kunci unik baru untuk baris ini. Mengambil salah satu dari dua langkah untuk setiap PK seperti duplikat dalam tabel holddups.
5. Menghapus duplikat dari meja asli. Misalnya:

   DELETE t1
   FROM t1, holdkey
   WHERE t1.col1 = holdkey.col1
   AND t1.col2 = holdkey.col2
   					

6. Meletakkan baris unik kembali di meja asli. Misalnya:

   INSERT t1 SELECT * FROM holddups