Zusammenfassung

Microsoft SQL Server-Tabellen sollten niemals duplizierte Zeilen oder nicht eindeutige Primärschlüssel enthalten. Aus Gründen der Kürze werden wir Primärschlüssel manchmal in diesem Artikel „Schlüssel“ oder „PK“ nennen, aber dies wird immer „Primärschlüssel“ bedeuten. Duplizierte PKs stellen eine Verletzung der Entitätsintegrität dar und sollten in einem relationalen System nicht zugelassen werden. SQL Server verfügt über verschiedene Mechanismen zum Erzwingen der Entitätsintegrität, einschließlich Indizes, EINDEUTIGKEITS-Einschränkungen, PRIMÄRSCHLÜSSEL-Einschränkungen und Trigger.

Trotzdem können unter ungewöhnlichen Umständen duplizierte Primärschlüssel auftreten, und wenn dies geschieht, müssen diese eliminiert werden. Eine Möglichkeit, dass diese auftreten, ist, dass duplizierte PKs in nicht relationalen Daten außerhalb von SQL Server vorhanden sind und die Daten importiert werden, während die PK-Eindeutigkeit nicht erzwungen wird. Eine andere Möglichkeit, dass sie auftreten, ist durch einen Datenbankentwurfsfehler, z. B. das Nichterzwingen der Entitätsintegrität in jeder Tabelle.

Häufig werden duplizierte PKs bemerkt, wenn Sie versuchen, einen eindeutigen Index zu erstellen, der abgebrochen wird, wenn duplizierte Schlüssel gefunden werden. Diese Meldung lautet:

Msg 1505, Ebene 16, Status 1 Erstellen eines eindeutigen Indexes wurde auf dupliziertem Schlüssel abgebrochen.

Wenn Sie SQL Server 2000 oder SQL Server 2005 verwenden, wird möglicherweise die folgende Fehlermeldung angezeigt:

Msg 1505, Ebene 16, Status 1 ERSTELLEN EINES EINDEUTIGEN INDEXES wurde beendet, weil ein duplizierter Schlüssel für den Objektnamen '%.*ls' und den Indexnamen '%.*ls' gefunden wurde. Der duplizierte Schlüsselwert ist %ls.

In diesem Artikel wird erläutert, wie sie duplizierte Primärschlüssel aus einer Tabelle suchen und entfernen. Sie sollten jedoch den Prozess, der die Duplikate ermöglichte, genau untersuchen, um ein Wiederauftreten zu verhindern.

Weitere Informationen

In diesem Beispiel verwenden wir die folgende Tabelle mit duplizierten PK-Werten. In dieser Tabelle sind die beiden Spalten (col1, col2) der Primärschlüssel. Wir können keinen eindeutigen Index oder eine PRIMÄRSCHLÜSSEL-Einschränkung erstellen, da zwei Zeilen duplizierte PKs haben. Dieses Verfahren veranschaulicht, wie Sie die Duplikate identifizieren und entfernen.

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')

Der erste Schritt besteht darin, zu ermitteln, welche Zeilen duplizierte Primärschlüsselwerte aufweisen:

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Dadurch wird eine Zeile für jeden Satz duplizierter PK-Werte in der Tabelle zurückgegeben. Die letzte Spalte in diesem Ergebnis ist die Anzahl der Duplikate für den jeweiligen PK-Wert.

col1

col2

1

1

2



Wenn es nur wenige Sätze duplizierter PK-Werte gibt, ist es am besten, diese manuell auf individueller Basis zu löschen. Beispiel:

set rowcount 1
delete from t1
where col1=1 and col2=1

Der Zeilenanzahl-Wert sollte n-1 der Anzahl der Duplikate für einen gegebenen Schlüsselwert sein. In diesem Beispiel gibt es 2 Duplikate, sodass die Zeilenanzahl auf 1 festgelegt ist. Die col1/col2-Werte stammen aus dem obigen GRUPPIEREN NACH-Abfrageergebnis. Wenn die GRUPPIEREN NACH-Abfrage mehrere Zeilen zurückgibt, muss die Abfrage „Zeilenanzahl festlegen“ einmal für jede dieser Zeilen ausgeführt werden. Legen Sie bei jeder Ausführung die Zeilenanzahl auf n-1, die Anzahl der Duplikate des jeweiligen PK-Werts, fest.

Bevor Sie die Zeilen löschen, sollten Sie überprüfen, ob die gesamte Zeile dupliziert ist. Obwohl unwahrscheinlich, ist es möglich, dass die PK-Werte dupliziert sind, aber die Zeile als Ganzes ist es nicht. Ein Beispiel hierfür wäre eine Tabelle mit der Sozialversicherungsnummer als Primärschlüssel und mit zwei verschiedenen Personen (oder Zeilen) mit derselben Zahl, die jeweils eindeutige Attribute aufweisen. In einem solchen Fall kann der Fehler, der den doppelten Schlüssel verursacht hat, auch dazu geführt haben, dass gültige eindeutige Daten in die Zeile platziert wurden. Diese Daten sollten vor dem Löschen der Daten kopiert und für die Untersuchung und mögliche Abstimmung aufbewahrt werden.

Wenn die Tabelle viele verschiedene Sätze mit duplizierten PK-Werten enthält, kann es zu zeitaufwändig sein, sie einzeln zu entfernen. In diesem Fall kann das folgende Verfahren angewandt werden:

  1. Führen Sie zunächst die obige GROUP BY-Abfrage aus, um zu bestimmen, wie viele Sätze duplizierter PK-Werte vorhanden sind und wie viele Duplikate für jeden Satz gezählt werden.

  2. Wählen Sie die duplizierten Schlüsselwerte in einer Haltetabelle aus. Beispiel:

    SELECT col1, col2, col3=count(*)
    INTO holdkey
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1
  3. Wählen Sie die duplizierten Zeilen aus und setzen Sie sie in eine Haltetabelle, wodurch Duplikate im Prozess vermieden werden. Beispiel:

    SELECT DISTINCT t1.*
    INTO holddups
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
  4. An diesem Punkt sollte die Holddups-Tabelle über eindeutige PKs verfügen, dies ist jedoch nicht der Fall, wenn t1 duplizierte PKs, aber eindeutige Zeilen (wie im SSN-Beispiel oben) hatte. Stellen Sie sicher, dass jeder Schlüssel in Holddups eindeutig ist und dass Sie nicht über duplizierte Schlüssel, aber über eindeutige Zeilen verfügen. Wenn dies der Fall ist, müssen Sie hier anhalten und die Zeilen abstimmen, die Sie für einen bestimmten duplizierten Schlüsselwert beibehalten möchten. Zum Beispiel, die Abfrage:

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

    sollte eine Anzahl von 1 für jede Zeile zurückgeben. Wenn ja, fahren Sie mit Schritt 5 weiter unten fort. Wenn nein, haben Sie duplizierte Schlüssel, jedoch eindeutige Zeilen und Sie müssen entscheiden, welche Zeilen gespeichert werden sollen. Dies bedeutet in der Regel, dass entweder eine Zeile verworfen oder ein neuer eindeutiger Schlüsselwert für diese Zeile erstellt wird. Führen Sie einen dieser beiden Schritte für jede solche duplizierte PK in der Holddups-Tabelle aus.

  5. Löschen Sie die duplizierten Zeilen aus der ursprünglichen Tabelle. Beispiel:

    DELETE t1
    FROM t1, holdkey
    WHERE t1.col1 = holdkey.col1
    AND t1.col2 = holdkey.col2
  6. Setzen Sie die eindeutigen Zeilen wieder in die ursprüngliche Tabelle. Beispiel:

    INSERT t1 SELECT * FROM holddups

Benötigen Sie weitere Hilfe?

Ihre Office-Fähigkeiten erweitern
Schulungen erkunden
Neue Funktionen als Erster erhalten
Microsoft Insider beitreten

War diese Information hilfreich?

Wie zufrieden sind Sie mit der Übersetzungsqualität?
Was hat Ihre Erfahrung beeinflusst?

Vielen Dank für Ihr Feedback!

×