Wie können Fremdschlüssel-Beschränkungen mit T-SQL vorübergehend deaktiviert werden?

Question

Wird das Deaktivieren und Aktivieren von Fremdschlüssel-Beschränkungen in SQL Server unterstützt? Oder ist meine einzige Option drop und dann re- create die Zwänge?

Answer 1

Wenn Sie alle Beschränkungen in der Datenbank deaktivieren möchten, führen Sie einfach diesen Code aus:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Um sie wieder einzuschalten, führen Sie aus: (der Ausdruck ist natürlich optional und listet nur die Tabellen auf)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Ich finde es nützlich, wenn ich Daten aus einer Datenbank in eine andere übertrage. Es ist ein viel besserer Ansatz als das Fallenlassen von Beschränkungen. Wie Sie erwähnten, ist es praktisch, wenn alle Daten in der Datenbank gelöscht und neu aufgefüllt werden (z. B. in einer Testumgebung).

Wenn Sie alle Daten löschen, finden Sie möglicherweise diese Lösung hilfreich zu sein.

Manchmal ist es auch praktisch, alle Auslöser zu deaktivieren. Sie können die vollständige Lösung sehen aquí .

Answer 2

(Kopiert von http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx , die jetzt in der Wayback Machine archiviert ist )

Fremdschlüsselbeschränkungen und Prüfbeschränkungen sind sehr nützlich, um die Datenintegrität und Geschäftsregeln durchzusetzen. Es gibt jedoch bestimmte Szenarien, in denen es sinnvoll ist, sie vorübergehend zu deaktivieren, weil ihr Verhalten entweder nicht benötigt wird oder mehr Schaden als Nutzen anrichten könnte. Ich deaktiviere manchmal die Einschränkungsprüfung für Tabellen, wenn Daten aus externen Quellen geladen werden oder wenn ich ein Skript für das Löschen/Erstellen einer Tabelle benötige, bei dem die Daten wieder in die Tabelle zurückgeladen werden. Normalerweise mache ich das in Szenarien, in denen ich nicht möchte, dass ein zeitaufwändiger Prozess fehlschlägt, weil eine oder ein paar von vielen Millionen Zeilen fehlerhafte Daten enthalten. Aber ich schalte die Beschränkungen immer wieder ein, sobald der Prozess abgeschlossen ist, und in einigen Fällen führe ich auch Datenintegritätsprüfungen für die importierten Daten durch.

Wenn Sie eine Fremdschlüssel-Beschränkung deaktivieren, können Sie einen Wert einfügen, der in der übergeordneten Tabelle nicht vorhanden ist. Wenn Sie eine Prüfbeschränkung deaktivieren, können Sie einen Wert in eine Spalte eingeben, als ob die Prüfbeschränkung nicht vorhanden wäre. Hier sind einige Beispiele für die Deaktivierung und Aktivierung von Tabellenbeschränkungen:

   -- Disable all table constraints
   ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

   -- Enable all table constraints
   ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

   -- Disable single constraint

   ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

   -- Enable single constraint
   ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

Answer 3

Um die Einschränkung zu deaktivieren, müssen Sie ALTER die Tabelle mit NOCHECK

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

Damit Sie doppelt verwenden müssen CHECK :

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

• Achten Sie auf das Doppel CHECK CHECK bei der Aktivierung.
• ALL bedeutet für alle Constraints in der Tabelle.

Wenn Sie nach der Fertigstellung den Status überprüfen müssen, verwenden Sie dieses Script, um den Status der Einschränkung aufzulisten. Das wird sehr hilfreich sein:

    SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
        ELSE 'DISABLED'
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

Answer 4

Die beste Option ist DROP und CREATE von Fremdschlüsselbeschränkungen.

Ich habe in diesem Beitrag keine Beispiele gefunden, die für mich "so wie sie sind" funktionieren würden, eines würde nicht funktionieren, wenn Fremdschlüssel auf verschiedene Schemata verweisen, das andere würde nicht funktionieren, wenn Fremdschlüssel auf mehrere Spalten verweisen. Dieses Skript berücksichtigt beides, mehrere Schemata und mehrere Spalten pro Fremdschlüssel.

Hier ist das Skript, das "ADD CONSTRAINT"-Anweisungen generiert, für mehrere Spalten werden sie durch Komma getrennt ( Speichern Sie diese Ausgabe vor der Ausführung von DROP-Anweisungen ):

PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --';

SET NOCOUNT ON;
SELECT '
PRINT N''Creating '+ const.const_name +'...''
GO
ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
GO'
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

Hier ist das Skript, das "DROP CONSTRAINT"-Anweisungen erzeugt:

PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --';

SET NOCOUNT ON;

SELECT '
PRINT N''Dropping ' + fk.NAME + '...''
GO
ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']
GO'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

Answer 5

Nach dem SQL-92-Standard kann eine Konstante als DEFERRABLE deklariert werden, so dass sie im Rahmen einer Transaktion (implizit oder explizit) zurückgestellt werden kann. Leider fehlt diese SQL-92-Funktionalität in SQL Server noch immer.

Für mich ist das Ändern einer Einschränkung auf NOCHECK wie eine fliegende Änderung der Datenbankstruktur - das Fallenlassen von Einschränkungen ist es auf jeden Fall - und etwas, das man vermeiden sollte (z. B. benötigen Benutzer mehr Rechte).