Pertanyaan Bagaimana batasan kunci asing dapat dinonaktifkan sementara menggunakan T-SQL?


Apakah menonaktifkan dan mengaktifkan batasan kunci asing yang didukung di SQL Server? Atau satu-satunya pilihan saya drop lalu kembalicreate kendala?


743
2017-10-01 18:33


asal


Jawaban:


Jika Anda ingin menonaktifkan semua kendala dalam database, jalankan kode ini:

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

Untuk mengaktifkannya kembali, jalankan: (cetakan opsional tentu saja dan itu hanya daftar tabel)

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

Saya merasa berguna ketika mengisi data dari satu database ke yang lain. Ini adalah pendekatan yang jauh lebih baik daripada menjatuhkan batasan. Seperti yang Anda sebutkan itu berguna ketika menjatuhkan semua data dalam database dan mengisi kembali itu (katakanlah dalam lingkungan uji).

Jika Anda menghapus semua data yang mungkin Anda temukan solusi ini untuk membantu.

Juga kadang-kadang sangat berguna untuk menonaktifkan semua pemicu juga, Anda dapat melihat solusi lengkap sini.


1029
2017-10-02 08:50



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

350
2017-10-01 18:37



Pilihan terbaik Anda adalah DROP dan MENCIPTAKAN batasan kunci asing.

Saya tidak menemukan contoh dalam posting ini yang akan bekerja untuk saya "apa adanya", seseorang tidak akan bekerja jika kunci asing mengacu skema yang berbeda, yang lain tidak akan berfungsi jika kunci asing referensi beberapa kolom. Skrip ini mempertimbangkan keduanya, beberapa skema dan beberapa kolom per kunci asing.

Berikut adalah skrip yang menghasilkan pernyataan "ADD CONSTRAINT", untuk beberapa kolom akan memisahkannya dengan koma (pastikan untuk menyimpan output ini sebelum menjalankan pernyataan DROP):

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

Berikut adalah skrip yang menghasilkan pernyataan "DROP CONSTRAINT":

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

24
2018-06-03 22:00



Untuk menonaktifkan kendala Anda ALTER menggunakan tabel NOCHECK

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

Untuk memungkinkan Anda harus menggunakan dua kali lipat MEMERIKSA

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
  • Perhatikan ganda LIHAT PERIKSA saat diaktifkan.
  • SEMUA berarti untuk semua kendala dalam tabel.

Setelah selesai, jika Anda perlu memeriksa status, gunakan skrip ini untuk daftar status kendala. Akan sangat membantu:

    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 

21
2018-04-03 20:06



Standar SQL-92 memungkinkan sebuah batasan untuk dinyatakan sebagai DEFERRABLE sehingga dapat ditangguhkan (implisit atau eksplisit) dalam lingkup transaksi. Sayangnya, SQL Server masih kehilangan fungsionalitas SQL-92 ini.

Bagi saya, mengubah kendala untuk NOCHECK mirip dengan mengubah struktur database dengan cepat - menjatuhkan kendala tentu - dan sesuatu yang harus dihindari (misalnya pengguna membutuhkan peningkatan hak istimewa).


9
2017-10-02 11:01



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

Saya setuju dengan Anda, Hamlin. Ketika Anda mentransfer data menggunakan SSIS atau ketika ingin mereplikasi data, tampaknya cukup perlu untuk menonaktifkan sementara atau drop batasan kunci asing dan kemudian mengaktifkan kembali atau membuat ulang mereka. Dalam kasus ini, integritas referensial bukan masalah, karena sudah dikelola dalam basis data sumber. Oleh karena itu, Anda dapat yakin tentang hal ini.


9
2018-02-26 10:24



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

9
2018-06-19 19:13



Pos pertama :)

Untuk OP, solusi kristof akan berfungsi, kecuali jika ada masalah dengan masalah besar data dan balon log transaksi pada penghapusan besar. Juga, bahkan dengan penyimpanan tlog untuk cadangan, karena menghapus menulis ke tlog, operasi dapat mengambil waktu yang sangat lama untuk tabel dengan ratusan juta baris.

Saya menggunakan serangkaian kursor untuk memotong dan memuat ulang salinan besar dari salah satu basis data produksi besar kami sering. Rekening hasil rekayasa untuk beberapa skema, beberapa kolom kunci asing, dan yang terbaik dari semuanya dapat dihilangkan untuk digunakan di SSIS.

Ini melibatkan pembuatan tiga tabel pementasan (tabel nyata) untuk menempatkan DROP, MENCIPTAKAN, dan MEMERIKSA skrip FK, membuat dan memasukkan skrip-skrip itu ke dalam tabel, dan kemudian mengulang di atas tabel dan mengeksekusinya. Skrip terlampir adalah empat bagian: 1.) pembuatan dan penyimpanan skrip dalam tabel tiga pementasan (nyata), 2.) eksekusi skrip drop FK melalui kursor satu per satu, 3.) Menggunakan sp_MSforeachtable untuk memotong semua tabel dalam database selain dari tiga tabel pementasan dan 4.) eksekusi dari membuat FK dan periksa skrip FK pada akhir paket SSIS ETL Anda.

Jalankan bagian pembuatan skrip dalam tugas Execute SQL di SSIS. Jalankan bagian "execute Drop FK Scripts" dalam tugas Execute SQL kedua. Masukkan skrip pemotongan dalam tugas Execute SQL ketiga, lalu lakukan proses ETL apa pun lainnya yang perlu Anda lakukan sebelum melampirkan skrip CREATE dan CHECK dalam tugas Execute SQL akhir (atau dua jika diinginkan) di akhir aliran kontrol Anda.

Penyimpanan skrip di tabel nyata telah terbukti tak ternilai ketika aplikasi ulang kunci asing gagal karena Anda dapat memilih * dari sync_CreateFK, salin / tempel ke jendela kueri Anda, jalankan satu per satu, dan perbaiki masalah data setelah Anda temukan yang gagal / masih gagal untuk mendaftar ulang.

Jangan menjalankan ulang skrip lagi jika gagal tanpa memastikan bahwa Anda menerapkan kembali semua kunci / cek asing sebelum melakukannya, atau kemungkinan besar Anda akan kehilangan beberapa pembuatan dan memeriksa fk scripting karena tabel pementasan kami dijatuhkan dan diciptakan kembali sebelum penciptaan skrip untuk dieksekusi.

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

7
2018-02-16 08:16



WITH CHECK CHECK hampir pasti diperlukan!

Hal ini dibangkitkan dalam beberapa jawaban dan komentar tetapi saya merasa bahwa itu cukup penting untuk menyebutnya lagi.

Mengaktifkan kembali kendala menggunakan perintah berikut (no WITH CHECK) akan memiliki beberapa kerugian serius.

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;

DENGAN PERIKSA | DENGAN NOCHECK

Menentukan apakah data dalam tabel atau tidak divalidasi   KATA FOREIGN atau PERIKSA WAKTU baru ditambahkan atau diaktifkan kembali. Jika tidak   ditentukan, DENGAN PERIKSA diasumsikan untuk kendala baru, dan DENGAN NOCHECK   diasumsikan untuk pembatasan yang diaktifkan kembali.

Jika Anda tidak ingin memverifikasi kendala CHECK atau FOREIGN KEY yang baru   terhadap data yang ada, gunakan DENGAN NOCHECK. Kami tidak menyarankan melakukan   ini, kecuali dalam kasus yang jarang terjadi. Kendala baru akan dievaluasi dalam   semua pembaruan data selanjutnya. Setiap pelanggaran kendala yang ditekan   oleh DENGAN NOCHECK ketika kendala ditambahkan dapat menyebabkan pembaruan di masa depan   gagal jika mereka memperbarui baris dengan data yang tidak sesuai dengan   paksaan.

Kueri optimizer tidak mempertimbangkan batasan yang ditentukan   DENGAN NOCHECK. Kendala semacam itu diabaikan sampai mereka diaktifkan kembali   dengan menggunakan ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

catatan: DENGAN NOCHECK adalah standar untuk mengaktifkan kembali batasan. Saya harus bertanya-tanya mengapa ...

  1. Tidak ada data yang ada dalam tabel yang akan dievaluasi selama pelaksanaan perintah ini - penyelesaian yang berhasil tidak menjamin bahwa data dalam tabel valid berdasarkan batasan.
  2. Selama pembaruan berikutnya dari catatan yang tidak valid, kendala akan dievaluasi dan akan gagal - menghasilkan kesalahan yang mungkin tidak terkait dengan pembaruan aktual yang dibuat.
  3. Logika aplikasi yang bergantung pada kendala untuk memastikan bahwa data valid mungkin gagal.
  4. Pengoptimal kueri tidak akan menggunakan batasan apa pun yang diaktifkan dengan cara ini.

Itu sys.foreign_keys tampilan sistem menyediakan beberapa visibilitas ke masalah ini. Perhatikan bahwa itu memiliki keduanya is_disabled dan sebuah is_not_trusted kolom. is_disabled menunjukkan apakah operasi manipulasi data di masa depan akan divalidasi terhadap kendala. is_not_trusted menunjukkan apakah semua data saat ini dalam tabel telah divalidasi terhadap kendala.

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;

Apakah kendala Anda dipercaya? Temukan...

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;

6
2018-02-14 06:31