Wie können Fremdschlüsseleinschränkungen mit T-SQL vorübergehend deaktiviert werden?
Wird das Deaktivieren und Aktivieren von Fremdschlüsseleinschränkungen in SQL Server unterstützt? Oder ist meine einzige Option fürdropund dann re -createdie Einschränkungen?
Wenn Sie alle Einschränkungen in der Datenbank deaktivieren möchten, führen Sie einfach folgenden Code aus:
-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Um sie wieder einzuschalten, führen Sie Folgendes aus: (der Druck ist natürlich optional und es werden nur die Tabellen aufgelistet)
-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Ich finde es nützlich, wenn Daten von einer Datenbank in eine andere gefüllt werden. Es ist viel besser als das Aufheben von Einschränkungen. Wie Sie bereits erwähnt haben, ist es praktisch, wenn Sie alle Daten in der Datenbank löschen und erneut ausfüllen (beispielsweise in einer Testumgebung).
Wenn Sie alle Daten löschen, finden Sie diese Lösung hilfreich.
Manchmal ist es auch praktisch, alle Trigger zu deaktivieren. Sie können die vollständige Lösung hier sehen.
http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
-- 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
Um die Einschränkung zu deaktivieren, haben Sie ALTER die Tabelle mit NOCHECK
ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
Um es zu aktivieren, müssen Sie doppelt CHECK verwenden.
ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
- Beachten Sie bei der Aktivierung das doppelte CHECK CHECK.
- ALL bedeutet für alle Einschränkungen in der Tabelle.
Wenn Sie den Status überprüft haben, können Sie dieses Skript verwenden, um den Status der Einschränkung aufzulisten. 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
Die beste Option ist, DROP und CREATE Fremdschlüsseleinschränkungen zu erstellen.
Ich habe in diesem Beitrag keine Beispiele gefunden, die für mich "wie besehen" funktionieren würden, einer würde nicht funktionieren, wenn Fremdschlüssel auf andere Schemas verweisen, der andere würde nicht funktionieren, wenn Fremdschlüssel auf mehrere Spalten verweisen. Dieses Skript berücksichtigt sowohl mehrere Schemata als auch mehrere Spalten pro Fremdschlüssel.
Hier ist das Skript, das "ADD CONSTRAINT" -Anweisungen generiert. Bei mehreren Spalten werden diese durch Kommas getrennt ( speichern Sie diese Ausgabe unbedingt, bevor Sie DROP-Anweisungen ausführen ):
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 Anweisungen "DROP CONSTRAINT" generiert:
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
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
GO'
FROM
@table
--ADD CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
GO'
FROM
@table
GO
Ich stimme dir zu, Hamlin. Wenn Sie Daten mit SSIS übertragen oder Daten replizieren möchten, scheint es durchaus notwendig, Fremdschlüsseleinschränkungen vorübergehend zu deaktivieren oder zu löschen und sie dann erneut zu aktivieren oder neu zu erstellen. In diesen Fällen ist die referenzielle Integrität kein Problem, da sie bereits in der Quellendatenbank verwaltet wird. Daher können Sie in dieser Angelegenheit sicher sein.
SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200),
UpdateRule NVARCHAR(100),
DeleteRule NVARCHAR(100)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,
T.UpdateRule = R.UPDATE_RULE,
T.DeleteRule = R.DELETE_RULE
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
SELECT '
BEGIN TRANSACTION
BEGIN TRY'
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table
SELECT '
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR(''Operation failed.'', 16, 1)
END CATCH
IF(@@TRANCOUNT != 0)
BEGIN
COMMIT TRANSACTION
RAISERROR(''Operation completed successfully.'', 10, 1)
END
'
--ADD CONSTRAINT:
SELECT '
BEGIN TRANSACTION
BEGIN TRY'
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ON UPDATE ' + UpdateRule + ' ON DELETE ' + DeleteRule + '
'
FROM
@table
SELECT '
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR(''Operation failed.'', 16, 1)
END CATCH
IF(@@TRANCOUNT != 0)
BEGIN
COMMIT TRANSACTION
RAISERROR(''Operation completed successfully.'', 10, 1)
END'
GO
Der SQL-92-Standard ermöglicht, dass ein constaint als DEFERRABLE deklariert wird, sodass es im Rahmen einer Transaktion (implizit oder explizit) zurückgestellt werden kann. Leider fehlt SQL Server diese SQL-92-Funktionalität immer noch.
Für mich ist das Ändern einer Einschränkung in NOCHECK so etwas wie eine Änderung der Datenbankstruktur unter den Bedingungen, die das Ablegen von Fliegen betreffen - und dies sollte vermieden werden (z. B. erfordern Benutzer erhöhte Berechtigungen).
Erster Beitrag :)
Für das OP funktioniert die Lösung von kristof, es sei denn, es gibt Probleme mit umfangreichen Daten- und Transaktionslog-Problemen bei großen Löschungen. Selbst wenn Tlog-Speicher nicht vorhanden ist, kann der Vorgang für Tabellen mit Hunderten von Millionen Zeilen sehr lange dauern, da das Schreiben in das Tlog gelöscht wird.
Ich verwende eine Reihe von Cursorn, um häufig große Kopien einer unserer riesigen Produktionsdatenbanken abzuschneiden und erneut zu laden. Die von der Lösung aus entwickelten Konten für mehrere Schemata, mehrere Fremdschlüsselspalten und vor allem können für die Verwendung in SSIS verwendet werden.
Dazu gehören die Erstellung von drei Staging-Tabellen (reale Tabellen) für die DROP-, CREATE- und CHECK FK-Skripts, das Erstellen und Einfügen dieser Skripts in die Tabellen sowie das Schleifen über die Tabellen und deren Ausführung. Das angehängte Skript besteht aus vier Teilen: 1.) Erstellen und Speichern der Skripts in den drei Staging-Tabellen (Real), 2.) Ausführen der Drop-FK-Skripts über einen Cursor nacheinander, 3.) Verwenden von sp_MSforeachtable, um alle zu kürzen Tabellen in der Datenbank mit Ausnahme unserer drei Staging-Tabellen und 4.) Ausführung der FK-Erstellung und Überprüfung der FK-Skripts am Ende Ihres ETL-SSIS-Pakets.
Führen Sie den Skripterstellungsabschnitt in einer SQL-Task in SSIS ausführen aus. Führen Sie den Abschnitt "Ausführen von FK-Skripts ausführen" in einer zweiten Task zum Ausführen von SQL aus. Fügen Sie das Abschneidungsskript in eine dritte Task zum Ausführen von SQL ein, und führen Sie dann vor dem Anhängen der CREATE- und CHECK-Skripts in einer abschließenden Task zum Ausführen von SQL (oder zwei, falls gewünscht) am Ende des Steuerungsflusses alle anderen ETL-Prozesse aus, die Sie ausführen müssen.
Das Speichern der Skripts in realen Tabellen hat sich als unbezahlbar erwiesen, wenn die erneute Anwendung der Fremdschlüssel fehlschlägt. Sie können * aus sync_CreateFK auswählen, in Ihr Abfragefenster kopieren/einfügen, sie einzeln ausführen und die Datenprobleme einmal beheben Finden Sie diejenigen, die fehlgeschlagen sind/sich noch nicht erneut bewerben.
Führen Sie das Skript nicht erneut aus, wenn es fehlschlägt, ohne sicherzustellen, dass Sie zuvor alle Fremdschlüssel/Überprüfungen erneut anwenden. Andernfalls verlieren Sie wahrscheinlich etwas an Erstellung und überprüfen das fk-Skripting, wenn unsere Staging-Tabellen gelöscht werden wird vor der Erstellung der auszuführenden Skripte neu erstellt.
----------------------------------------------------------------------------
1)
/*
Author: Denmach
DateCreated: 2014-04-23
Purpose: Generates SQL statements to DROP, ADD, and CHECK existing constraints for a
database. Stores scripts in tables on target database for execution. Executes
those stored scripts via independent cursors.
DateModified:
ModifiedBy
Comments: This will eliminate deletes and the T-log ballooning associated with it.
*/
DECLARE @schema_name SYSNAME;
DECLARE @table_name SYSNAME;
DECLARE @constraint_name SYSNAME;
DECLARE @constraint_object_id INT;
DECLARE @referenced_object_name SYSNAME;
DECLARE @is_disabled BIT;
DECLARE @is_not_for_replication BIT;
DECLARE @is_not_trusted BIT;
DECLARE @delete_referential_action TINYINT;
DECLARE @update_referential_action TINYINT;
DECLARE @tsql NVARCHAR(4000);
DECLARE @tsql2 NVARCHAR(4000);
DECLARE @fkCol SYSNAME;
DECLARE @pkCol SYSNAME;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);
DECLARE @referenced_schema_name SYSNAME;
--------------------------------Generate scripts to drop all foreign keys in a database --------------------------------
IF OBJECT_ID('dbo.sync_dropFK') IS NOT NULL
DROP TABLE sync_dropFK
CREATE TABLE sync_dropFK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
DECLARE FKcursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
FROM
sys.foreign_keys WITH (NOLOCK)
ORDER BY
1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+ ' DROP CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ';';
--PRINT @tsql;
INSERT sync_dropFK (
Script
)
VALUES (
@tsql
)
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
---------------Generate scripts to create all existing foreign keys in a database --------------------------------
----------------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.sync_createFK') IS NOT NULL
DROP TABLE sync_createFK
CREATE TABLE sync_createFK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
IF OBJECT_ID('dbo.sync_createCHECK') IS NOT NULL
DROP TABLE sync_createCHECK
CREATE TABLE sync_createCHECK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
DECLARE FKcursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
, OBJECT_NAME(referenced_object_id)
, OBJECT_ID
, is_disabled
, is_not_for_replication
, is_not_trusted
, delete_referential_action
, update_referential_action
, OBJECT_SCHEMA_NAME(referenced_object_id)
FROM
sys.foreign_keys WITH (NOLOCK)
ORDER BY
1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
, @referenced_object_name
, @constraint_object_id
, @is_disabled
, @is_not_for_replication
, @is_not_trusted
, @delete_referential_action
, @update_referential_action
, @referenced_schema_name;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+ CASE
@is_not_trusted
WHEN 0 THEN ' WITH CHECK '
ELSE ' WITH NOCHECK '
END
+ ' ADD CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ' FOREIGN KEY (';
SET @tsql2 = '';
DECLARE ColumnCursor CURSOR FOR
SELECT
COL_NAME(fk.parent_object_id
, fkc.parent_column_id)
, COL_NAME(fk.referenced_object_id
, fkc.referenced_column_id)
FROM
sys.foreign_keys fk WITH (NOLOCK)
INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id
WHERE
fkc.constraint_object_id = @constraint_object_id
ORDER BY
fkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0;
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + QUOTENAME(@fkCol);
SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
--PRINT '@tsql = ' + @tsql
--PRINT '@tsql2 = ' + @tsql2
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
--PRINT 'FK Column ' + @fkCol
--PRINT 'PK Column ' + @pkCol
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @tsql = @tsql + ' ) REFERENCES '
+ QUOTENAME(@referenced_schema_name)
+ '.'
+ QUOTENAME(@referenced_object_name)
+ ' ('
+ @tsql2 + ')';
SET @tsql = @tsql
+ ' ON UPDATE '
+
CASE @update_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ ' ON DELETE '
+
CASE @delete_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+
CASE @is_not_for_replication
WHEN 1 THEN ' NOT FOR REPLICATION '
ELSE ''
END
+ ';';
END;
-- PRINT @tsql
INSERT sync_createFK
(
Script
)
VALUES (
@tsql
)
-------------------Generate CHECK CONSTRAINT scripts for a database ------------------------------
----------------------------------------------------------------------------------------------------------
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+
CASE @is_disabled
WHEN 0 THEN ' CHECK '
ELSE ' NOCHECK '
END
+ 'CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ';';
--PRINT @tsql;
INSERT sync_createCHECK
(
Script
)
VALUES (
@tsql
)
END;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
, @referenced_object_name
, @constraint_object_id
, @is_disabled
, @is_not_for_replication
, @is_not_trusted
, @delete_referential_action
, @update_referential_action
, @referenced_schema_name;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
--SELECT * FROM sync_DropFK
--SELECT * FROM sync_CreateFK
--SELECT * FROM sync_CreateCHECK
---------------------------------------------------------------------------
2.)
-----------------------------------------------------------------------------------------------------------------
----------------------------execute Drop FK Scripts --------------------------------------------------
DECLARE @scriptD NVARCHAR(4000)
DECLARE DropFKCursor CURSOR FOR
SELECT Script
FROM sync_dropFK WITH (NOLOCK)
OPEN DropFKCursor
FETCH NEXT FROM DropFKCursor
INTO @scriptD
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptD
EXEC (@scriptD)
FETCH NEXT FROM DropFKCursor
INTO @scriptD
END
CLOSE DropFKCursor
DEALLOCATE DropFKCursor
--------------------------------------------------------------------------------
3.)
------------------------------------------------------------------------------------------------------------------
----------------------------Truncate all tables in the database other than our staging tables --------------------
------------------------------------------------------------------------------------------------------------------
EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN
(
ISNULL(OBJECT_ID(''dbo.sync_createCHECK''),0),
ISNULL(OBJECT_ID(''dbo.sync_createFK''),0),
ISNULL(OBJECT_ID(''dbo.sync_dropFK''),0)
)
BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
PRINT ''Truncation failed on''+ ? +''
END CATCH;'
GO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
----------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts---------------
----------------------------tack me at the end of the ETL in a SQL task-------------------------
-------------------------------------------------------------------------------------------------
DECLARE @scriptC NVARCHAR(4000)
DECLARE CreateFKCursor CURSOR FOR
SELECT Script
FROM sync_createFK WITH (NOLOCK)
OPEN CreateFKCursor
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptC
EXEC (@scriptC)
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
END
CLOSE CreateFKCursor
DEALLOCATE CreateFKCursor
-------------------------------------------------------------------------------------------------
DECLARE @scriptCh NVARCHAR(4000)
DECLARE CreateCHECKCursor CURSOR FOR
SELECT Script
FROM sync_createCHECK WITH (NOLOCK)
OPEN CreateCHECKCursor
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptCh
EXEC (@scriptCh)
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
END
CLOSE CreateCHECKCursor
DEALLOCATE CreateCHECKCursor
WITH CHECK CHECK ist fast sicher erforderlich!
Dieser Punkt wurde in einigen Antworten und Kommentaren angesprochen, aber ich denke, es ist wichtig genug, um es noch einmal aufzurufen.
Das erneute Aktivieren einer Einschränkung mit dem folgenden Befehl (kein WITH CHECK) hat einige gravierende Nachteile .
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;
MIT CHECK | MIT NOCHECK
Gibt an, ob die Daten in der Tabelle auf .__ überprüft werden oder nicht. eine neu hinzugefügte oder wieder aktivierte FOREIGN KEY- oder CHECK-Einschränkung. Wenn nicht angegeben wird, wird WITH CHECK für neue Bedingungen und WITH NOCHECK .__ angenommen. wird für wieder aktivierte Bedingungen angenommen.
Wenn Sie keine neuen CHECK- oder FOREIGN KEY-Einschränkungen überprüfen möchten gegen vorhandene Daten verwenden Sie WITH NOCHECK. Wir empfehlen nicht, dies, außer in seltenen Fällen. Die neue Bedingung wird in .__ ausgewertet. alle späteren Datenaktualisierungen. Alle Verstöße gegen Beschränkungen, die unterdrückt werden von WITH NOCHECK, wenn die Einschränkung hinzugefügt wird, kann zukünftige Aktualisierungen verursachen zu scheitern, wenn sie Zeilen mit Daten aktualisieren, die nicht mit der .__ übereinstimmen. Zwang.
Das Abfrageoptimierungsprogramm berücksichtigt keine Einschränkungen, die definiert sind MIT NOCHECK. Solche Einschränkungen werden ignoriert, bis sie wieder aktiviert werden mit der ALTER TABLE-Tabelle WITH CHECK CHECK CONSTRAINT ALL.
Hinweis: WITH NOCHECK ist der Standard für das erneute Aktivieren von Einschränkungen. Ich muss mich fragen, warum ...
- Während der Ausführung dieses Befehls werden keine vorhandenen Daten in der Tabelle ausgewertet. Der erfolgreiche Abschluss ist keine Garantie dafür, dass die Daten in der Tabelle gemäß der Einschränkung gültig sind.
- Bei der nächsten Aktualisierung der ungültigen Datensätze wird die Einschränkung ausgewertet und schlägt fehl. Dies kann zu Fehlern führen, die möglicherweise nicht mit der tatsächlichen Aktualisierung zusammenhängen.
- Anwendungslogik, die sich auf die Einschränkung stützt, um sicherzustellen, dass die Daten gültig sind, kann fehlschlagen.
- Das Abfrageoptimierungsprogramm verwendet keine auf diese Weise aktivierte Einschränkung.
Die Systemansicht sys.foreign_keys bietet einen Einblick in das Problem. Beachten Sie, dass es sowohl eine is_disabled- als auch eine is_not_trusted-Spalte hat. is_disabled gibt an, ob zukünftige Datenmanipulationsoperationen anhand der Einschränkung validiert werden. is_not_trusted gibt an, ob alle derzeit in der Tabelle vorhandenen Daten gegen die Einschränkung geprüft wurden.
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;
Sind Ihre Einschränkungen zu vertrauen? Rausfinden...
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
Finde die Einschränkung
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')
Führen Sie die von dieser SQL generierte SQL aus
SELECT
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) +
'.[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')
Safeway.
Hinweis: Lösung zum Löschen der Einschränkung hinzugefügt, sodass die Tabelle ohne Einschränkungsfehler gelöscht oder geändert werden kann.
Sie sollten Fremdschlüsseleinschränkungen tatsächlich auf die gleiche Weise deaktivieren können, wie Sie andere Einschränkungen vorübergehend deaktivieren:
Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName
Stellen Sie nur sicher, dass Sie die Einschränkung für die erste Tabelle deaktivieren, die im Namen der Einschränkung aufgeführt ist. Wenn meine Fremdschlüsseleinschränkung beispielsweise FK_LocationsEmployeesLocationIdEmployeeId war, würde ich Folgendes verwenden:
Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId
ein Verstoß gegen diese Einschränkung führt zwar zu einem Fehler, der diese Tabelle nicht unbedingt als Quelle des Konflikts angibt.
Klicken Sie mit der rechten Maustaste auf das Tabellendesign, und wählen Sie Beziehungen aus, und wählen Sie im linken Bereich den Fremdschlüssel aus. Setzen Sie im rechten Bereich die Option Fremdschlüsseleinschränkung auf "Ja" (zum Aktivieren von Fremdschlüsseleinschränkungen) oder "Nein" (bis) deaktivieren Sie es) . 
Ein Skript, um sie alle zu beherrschen: Dies kombiniert Befehle zum Abschneiden und Löschen mit sp_MSforeachtable, sodass Sie das Löschen und Neuerstellen von Einschränkungen vermeiden können Maßnahme (getestet in 2008r2)
declare @schema nvarchar(max) = 'and Schema_Id=Schema_id(''Value'')'
declare @deletiontables nvarchar(max) = '(''TableA'',''TableB'')'
declare @truncateclause nvarchar(max) = @schema + ' and o.Name not in ' + + @deletiontables;
declare @deleteclause nvarchar(max) = @schema + ' and o.Name in ' + @deletiontables;
exec sp_MSforeachtable 'alter table ? nocheck constraint all', @[email protected]
exec sp_MSforeachtable 'truncate table ?', @[email protected]
exec sp_MSforeachtable 'delete from ?', @[email protected]
exec sp_MSforeachtable 'alter table ? with check check constraint all', @[email protected]
Antwort mit der Aufschrift '905' sieht gut aus, funktioniert aber nicht.
Folgendes arbeitete für mich. Alle Primärschlüssel, Eindeutiger Schlüssel oder Standardeinschränkungen CAN NOT sind deaktiviert. Wenn 'sp_helpconstraint' 'in status_enabled' n/a 'zeigt, bedeutet dies, dass NICHT aktiviert/deaktiviert werden kann.
- Skript zum Deaktivieren generieren
select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints
where status & 0x4813 = 0x813 order by object_name(id)
- Um ein Skript für ENABLE zu generieren
select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints
where status & 0x4813 = 0x813 order by object_name(id)
Ich habe eine nützlichere Version, wenn Sie interessiert sind. Ich habe hier ein bisschen Code von einer Website geholt, auf der der Link nicht mehr aktiv ist. Ich habe es geändert, um ein Array von Tabellen in der gespeicherten Prozedur zuzulassen, und es füllt die drop-, trunk- und add-Anweisungen auf, bevor sie alle ausgeführt werden. Auf diese Weise können Sie entscheiden, welche Tabellen abgeschnitten werden müssen.
/****** Object: UserDefinedTableType [util].[typ_objects_for_managing] Script Date: 03/04/2016 16:42:55 ******/
CREATE TYPE [util].[typ_objects_for_managing] AS TABLE(
[schema] [sysname] NOT NULL,
[object] [sysname] NOT NULL
)
GO
create procedure [util].[truncate_table_with_constraints]
@objects_for_managing util.typ_objects_for_managing readonly
[email protected] sysname
--,@table sysname
as
--select
-- @table = 'TABLE',
-- @schema = 'SCHEMA'
declare @exec_table as table (ordinal int identity (1,1), statement nvarchar(4000), primary key (ordinal));
--print '/*Drop Foreign Key Statements for ['[email protected]+'].['[email protected]+']*/'
insert into @exec_table (statement)
select
'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']'
from sys.foreign_keys fk
inner join sys.objects o
on fk.parent_object_id = o.object_id
where
exists (
select * from @objects_for_managing chk
where
chk.[schema] = SCHEMA_NAME(o.schema_id)
and
chk.[object] = o.name
)
;
--o.name = @table and
--SCHEMA_NAME(o.schema_id) = @schema
insert into @exec_table (statement)
select
'TRUNCATE TABLE ' + src.[schema] + '.' + src.[object]
from @objects_for_managing src
;
--print '/*Create Foreign Key Statements for ['[email protected]+'].['[email protected]+']*/'
insert into @exec_table (statement)
select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+'])
REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])'
from sys.foreign_key_columns fkc
inner join sys.foreign_keys fk
on fkc.constraint_object_id = fk.object_id
inner join sys.objects o
on fk.parent_object_id = o.object_id
inner join sys.columns c
on fkc.parent_column_id = c.column_id and
o.object_id = c.object_id
inner join sys.objects refob
on fkc.referenced_object_id = refob.object_id
inner join sys.columns refcol
on fkc.referenced_column_id = refcol.column_id and
fkc.referenced_object_id = refcol.object_id
where
exists (
select * from @objects_for_managing chk
where
chk.[schema] = SCHEMA_NAME(o.schema_id)
and
chk.[object] = o.name
)
;
--o.name = @table and
--SCHEMA_NAME(o.schema_id) = @schema
declare @looper int , @total_records int, @sql_exec nvarchar(4000)
select @looper = 1, @total_records = count(*) from @exec_table;
while @looper <= @total_records
begin
select @sql_exec = (select statement from @exec_table where ordinal [email protected])
exec sp_executesql @sql_exec
print @sql_exec
set @looper = @looper + 1
end
Sie können Einschränkungen für Ihre Tabellen vorübergehend deaktivieren, arbeiten und sie dann neu erstellen.
Hier ist eine einfache Möglichkeit, dies zu tun ...
Deaktivieren Sie alle Indizes, einschließlich der Primärschlüssel. Dadurch werden alle Fremdschlüssel deaktiviert. Aktivieren Sie dann nur die Primärschlüssel, um mit ihnen arbeiten zu können.
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)
from
sys.tables t
where type='u'
select @sql = @sql +
'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.key_constraints i
join
sys.tables t on i.parent_object_id=t.object_id
where
i.type='PK'
exec dbo.sp_executesql @sql;
go
[Tun Sie etwas, wie das Laden von Daten]
Aktivieren Sie dann die Indizes erneut und erstellen Sie sie neu ...
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.tables t
where type='u'
exec dbo.sp_executesql @sql;
go