wake-up-neo.net

So aktualisieren Sie den Primärschlüssel

Hier ist mein Problem: Ich habe 2 Tabellen:

  1. WORKER mit den Spalten |ID|OTHER_STAF|, wobei ID der Primärschlüssel ist
  2. FIRM mit den Spalten |FPK|ID|SOMETHING_ELSE|, wobei die Kombination FPK und ID zum Primärschlüssel wird, und auch ID ist ein auf WORKER.ID referenzierter Fremdschlüssel (nicht null und muss denselben Wert wie in WORKER haben).

Ich möchte die gespeicherte Prozedur UPDATE_ID_WORKER erstellen, in der ich den Wert einer bestimmten ID in WORKER und auch in allen Instanzen eines bestimmten ID-Werts in FIRM ändern möchte.

gespeicherte Prozedur:

[email protected] würde..????........

19
Slavisa

Sie sollten dies nicht wirklich tun, sondern stattdessen einen neuen Datensatz einfügen und ihn auf diese Weise aktualisieren.
Wenn Sie es wirklich brauchen, können Sie Folgendes tun:

  • Deaktivieren der temporären Erzwingung von FK-Einschränkungen (z. B. ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)
  • Dann aktualisieren Sie Ihre PK 
  • Aktualisieren Sie dann Ihre FKs, um sie an die PK-Änderung anzupassen
  • Aktivieren Sie schließlich die Rückerzwingung von FK-Einschränkungen 
28
kevchadders

Als Erstes wählen wir stabile (nicht statische) Datenspalten aus, um einen Primärschlüssel zu bilden, gerade weil das Aktualisieren von Schlüsseln in einer relationalen Datenbank (in der sich die Referenzen auf Schlüssel beziehen) vermieden werden soll.

  1. Für dieses Problem spielt es keine Rolle, ob der Schlüssel ein relationaler Schlüssel ist ("aus den Daten zusammengesetzt") und daher relationale Integrität, Leistung und Geschwindigkeit aufweist oder ob der "Schlüssel" eine Datensatz-ID ohne ID ist dieser relationalen Integrität, Kraft und Geschwindigkeit. Der Effekt ist der gleiche.

  2. Ich sage dies, weil es viele ahnungslose Posts gibt, die darauf hindeuten, dass dies genau der Grund ist, warum Datensatz-IDs irgendwie besser sind als relationale Schlüssel.

  3. Der Punkt ist, dass der Schlüssel oder die Datensatz-ID an die Stelle migriert wird, an der eine Referenz erforderlich ist.

Zweitens, wenn Sie den Wert des Schlüssels oder der Datensatz-ID ändern müssen, müssen Sie ihn ändern. Hier ist die OLTP Standard-konforme Methode. Beachten Sie, dass die High-End-Anbieter keine "Kaskadenaktualisierung" zulassen.

  • Schreibe einen Proc. Foo_UpdateCascade_tr @ID, wobei Foo der Tabellenname ist

  • Beginnen Sie eine Transaktion

  • Zuerst INSERT-SELECT eine neue Zeile in der übergeordneten Tabelle aus der alten Zeile mit dem neuen Schlüssel- oder RID-Wert

  • Zweitens müssen Sie für alle untergeordneten Tabellen von oben nach unten die neuen Zeilen aus den alten Zeilen mit dem neuen Schlüssel- oder RID-Wert EINFÜGEN-AUSWÄHLEN

  • Drittens LÖSCHEN Sie die Zeilen in den untergeordneten Tabellen, die den alten Schlüssel- oder RID-Wert haben und von unten nach oben arbeiten

  • Zuletzt LÖSCHEN Sie die Zeile in der übergeordneten Tabelle, die den alten Schlüssel- oder RID-Wert enthält

  • Übernehmen Sie die Transaktion

Re die anderen Antworten

Die anderen Antworten sind falsch.

  • Das Deaktivieren und anschließende Aktivieren von Einschränkungen nach dem UPDATEN der erforderlichen Zeilen (übergeordnetes Element und alle untergeordneten Elemente) ist nichts, was eine Person in einer Online-Produktionsumgebung tun würde, wenn sie beschäftigt bleiben möchte. Dieser Rat ist gut für Einzelbenutzer-Datenbanken.

  • Die Notwendigkeit, den Wert eines Schlüssels oder einer RID zu ändern, weist nicht auf einen Konstruktionsfehler hin. Es ist ein gewöhnliches Bedürfnis. Dies wird durch die Auswahl von stabilen (nicht statischen) Schlüsseln gemildert. Es kann gemildert, aber nicht beseitigt werden.

  • Ein Ersatz, der einen natürlichen Schlüssel ersetzt, macht keinen Unterschied. In dem von Ihnen angegebenen Beispiel ist der "Schlüssel" ein Ersatz. Und es muss aktualisiert werden.

    • Bitte, nur Ersatz, es gibt keinen "Ersatzschlüssel", weil jedes Wort dem anderen widerspricht. Entweder ist es ein Schlüssel (aus den Daten zusammengesetzt) ​​oder nicht. Ein Surrogat besteht nicht aus den Daten, es ist explizit Nicht-Daten. Es hat keine der Eigenschaften eines Schlüssels.
  • Es ist nicht "knifflig", alle erforderlichen Änderungen zu kaskadieren. Befolgen Sie die oben angegebenen Schritte.

  • Es gibt nichts, was verhindert werden könnte, dass sich das Universum verändert. Es ändert. Komm damit klar. Und da die Datenbank eine Sammlung von Fakten über das Universum ist, muss sich die Datenbank ändern, wenn sich das Universum ändert. Das ist das Leben in der Großstadt, es ist nichts für neue Spieler.

  • Menschen, die heiraten und Igel, die beerdigt werden, sind kein Problem (obwohl solche Beispiele darauf hindeuten, dass es sich um ein Problem handelt ). Weil wir keine Namen als Schlüssel verwenden. Wir verwenden kleine, stabile Bezeichner, die zum Identifizieren der Daten im Universum verwendet werden.

    • Namen, Beschreibungen usw. sind in einer Zeile einmal vorhanden. Schlüssel sind überall dort vorhanden, wo sie migriert wurden. Und wenn der "Schlüssel" eine RID ist, dann existiert die RID auch dort, wo sie migriert wurde.
  • Aktualisiere nicht die PK! ist die zweitlustigste Sache, die ich seit einiger Zeit gelesen habe. Eine neue Spalte hinzufügen ist das Meiste.

23
PerformanceDBA

Wenn Sie sicher sind, dass diese Änderung für die Umgebung geeignet ist, in der Sie arbeiten: Setzen Sie die FK-Bedingungen für die sekundären Tabellen auf UPDATE CASCADING.

Wenn Sie beispielsweise SSMS als GUI verwenden:

  1. klicken Sie mit der rechten Maustaste auf den Schlüssel
  2. auswählen ändern
  3. "INSERT- und UPDATE-spezifisch" ausklappen
  4. Wählen Sie für 'Update Rule' die Option Cascade aus.
  5. Schließen Sie den Dialog und speichern Sie den Schlüssel.

Wenn Sie anschließend einen Wert in der PK-Spalte in Ihrer Primärtabelle aktualisieren, werden die FK-Verweise in den anderen Tabellen so aktualisiert, dass sie auf den neuen Wert verweisen, wodurch die Datenintegrität erhalten bleibt.

10
pbeentje

Wenn Sie feststellen, dass ein Primärschlüsselwert sowie alle dazugehörigen Fremdschlüssel aktualisiert werden müssen, muss das gesamte Design repariert werden.

Es ist schwierig, alle erforderlichen Fremdschlüsseländerungen zu kaskadieren. Es wird empfohlen, den Primärschlüssel niemals zu aktualisieren. Wenn Sie dies für erforderlich halten, sollten Sie einen Surrogate Primary Key verwenden. Hierbei handelt es sich um einen Schlüssel, der nicht aus Anwendungsdaten abgeleitet ist. Infolgedessen hat sein Wert keinen Bezug zur Geschäftslogik und muss nie geändert werden (und sollte für den Endbenutzer nicht sichtbar sein). Sie können dann eine andere Spalte aktualisieren und anzeigen.

zum Beispiel:

BadUserTable
UserID     varchar(20) primary key --user last name
other columns...

wenn Sie viele Tabellen mit einer FK-zu-UserID erstellen, um alles zu verfolgen, woran der Benutzer gearbeitet hat, der Benutzer aber heiratet und eine ID wünscht, die mit dem neuen Nachnamen übereinstimmt, haben Sie kein Glück.

GoodUserTable
UserID    int identity(1,1) primary key
UserLogin varchar(20) 
other columns....

sie können jetzt den Surrogate Primary Key für alle anderen Tabellen verwenden und bei Bedarf UserLogin anzeigen. Erlauben Sie ihnen, sich mit diesem Wert anzumelden. Wenn sie geändert werden müssen, ändern Sie sie nur in einer Spalte einer Zeile.

5
KM.

Nicht den Primärschlüssel aktualisieren . Wenn andere Tabellen darauf verweisen, kann dies zu einer Reihe von Problemen führen.

Idealerweise erstellen Sie ein neues Feld, wenn Sie ein eindeutiges Feld wünschen, das aktualisiert werden kann.

2
Daniel A. White

Sie können diese rekursive Funktion verwenden, um das erforderliche T-SQL-Skript zu generieren.

CREATE FUNCTION dbo.Update_Delete_PrimaryKey
(
    @TableName      NVARCHAR(255),
    @ColumnName     NVARCHAR(255),
    @OldValue       NVARCHAR(MAX),
    @NewValue       NVARCHAR(MAX),
    @Del            BIT
)
RETURNS NVARCHAR 
(
    MAX
)
AS
BEGIN
    DECLARE @fks TABLE 
            (
                constraint_name NVARCHAR(255),
                table_name NVARCHAR(255),
                col NVARCHAR(255)
            );
    DECLARE @Sql                  NVARCHAR(MAX),
            @EnableConstraints     NVARCHAR(MAX);

    SET @Sql = '';
    SET @EnableConstraints = '';

    INSERT INTO @fks
      (
        constraint_name,
        table_name,
        col
      )
    SELECT oConstraint.name     constraint_name,
           oParent.name         table_name,
           oParentCol.name      col
    FROM   sys.foreign_key_columns sfkc
           --INNER JOIN sys.foreign_keys sfk
           --     ON  sfk.[object_id] = sfkc.constraint_object_id

           INNER JOIN sys.sysobjects oConstraint
                ON  sfkc.constraint_object_id = oConstraint.id
           INNER JOIN sys.sysobjects oParent
                ON  sfkc.parent_object_id = oParent.id
           INNER JOIN sys.all_columns oParentCol
                ON  sfkc.parent_object_id = oParentCol.object_id
                AND sfkc.parent_column_id = oParentCol.column_id
           INNER JOIN sys.sysobjects oReference
                ON  sfkc.referenced_object_id = oReference.id
           INNER JOIN sys.all_columns oReferenceCol
                ON  sfkc.referenced_object_id = oReferenceCol.object_id
                AND sfkc.referenced_column_id = oReferenceCol.column_id
    WHERE  oReference.name = @TableName
           AND oReferenceCol.name = @ColumnName
    --AND (@Del <> 1 OR sfk.delete_referential_action = 0)
    --AND (@Del = 1 OR sfk.update_referential_action = 0)

    IF EXISTS(
           SELECT 1
           FROM   @fks
       )
    BEGIN
        DECLARE @Constraint     NVARCHAR(255),
                @Table          NVARCHAR(255),
                @Col            NVARCHAR(255)  

        DECLARE Table_Cursor CURSOR LOCAL 
        FOR
            SELECT f.constraint_name,
                   f.table_name,
                   f.col
            FROM   @fks AS f

        OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Constraint, @Table,@Col  
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF @Del <> 1
            BEGIN
                SET @Sql = @Sql + 'ALTER TABLE ' + @Table + ' NOCHECK CONSTRAINT ' + @Constraint + CHAR(13) + CHAR(10);
                SET @EnableConstraints = @EnableConstraints + 'ALTER TABLE ' + @Table + ' CHECK CONSTRAINT ' + @Constraint 
                    + CHAR(13) + CHAR(10);
            END

            SET @Sql = @Sql + dbo.Update_Delete_PrimaryKey(@Table, @Col, @OldValue, @NewValue, @Del);
            FETCH NEXT FROM Table_Cursor INTO @Constraint, @Table,@Col
        END

        CLOSE Table_Cursor DEALLOCATE Table_Cursor
    END

    DECLARE @DataType NVARCHAR(30);
    SELECT @DataType = t.name +
           CASE 
                WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' +
                     CASE 
                          WHEN c.max_length = -1 THEN 'MAX'
                          ELSE CONVERT(
                                   VARCHAR(4),
                                   CASE 
                                        WHEN t.name IN ('nchar', 'nvarchar') THEN c.max_length / 2
                                        ELSE c.max_length
                                   END
                               )
                     END + ')'
                WHEN t.name IN ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(4), c.precision) + ',' 
                     + CONVERT(VARCHAR(4), c.Scale) + ')'
                ELSE ''
           END
    FROM   sys.columns c
           INNER JOIN sys.types t
                ON  c.user_type_id = t.user_type_id
    WHERE  c.object_id = OBJECT_ID(@TableName)
           AND c.name = @ColumnName

    IF @Del <> 1
    BEGIN
        SET @Sql = @Sql + 'UPDATE [' + @TableName + '] SET [' + @ColumnName + '] = CONVERT(' + @DataType + ', ' + ISNULL('N''' + @NewValue + '''', 'NULL') 
            + ') WHERE [' + @ColumnName + '] = CONVERT(' + @DataType + ', ' + ISNULL('N''' + @OldValue + '''', 'NULL') +
            ');' + CHAR(13) + CHAR(10);
        SET @Sql = @Sql + @EnableConstraints;
    END
    ELSE
        SET @Sql = @Sql + 'DELETE [' + @TableName + '] WHERE [' + @ColumnName + '] = CONVERT(' + @DataType + ', N''' + @OldValue 
            + ''');' + CHAR(13) + CHAR(10);
    RETURN @Sql;
END
GO

DECLARE @Result NVARCHAR(MAX);
SET @Result = dbo.Update_Delete_PrimaryKey('@TableName', '@ColumnName', '@OldValue', '@NewValue', 0);/*Update*/
EXEC (@Result)
SET @Result = dbo.Update_Delete_PrimaryKey('@TableName', '@ColumnName', '@OldValue', NULL, 1);/*Delete*/
EXEC (@Result)
GO

DROP FUNCTION Update_Delete_PrimaryKey;
0