web-gelistirme-sc.com

Tüm yabancı anahtar kısıtlamalarını geçici olarak devre dışı bırakın

FlatFiles'tan bir veritabanındaki mevcut tablolara birkaç tablo için verilerin yerini alacak bir SSIS paketi kullanıyorum.

Paketim tabloları kesecek ve ardından yeni verileri ekleyecektir. SSIS paketimi çalıştırdığımda, yabancı anahtarlar nedeniyle bir istisna alıyorum.

Kısıtlamaları devre dışı bırakabilir, içe aktarma işlemimi gerçekleştirip tekrar etkinleştirebilir miyim?

41
HaBo

Yabancı anahtar kısıtlamalarını devre dışı bırakmak için:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Yeniden etkinleştirmek için:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Ancak, tabloları kısaltamazsınız, tabloları doğru sırayla silmeniz gerekir. Bunları truncate yapmanız gerekirse, kısıtlamaları tamamen kaldırmanız ve yeniden oluşturmanız gerekir. Yabancı anahtar kısıtlamalarınızın tümü basit, tek sütun kısıtlamalarıysa, ancak birden çok sütun varsa kesinlikle daha karmaşıksa bu işlemi yapmak kolaydır. 

İşte deneyebileceğiniz bir şey. Bunu SSIS paketinizin bir parçası yapmak için, SSIS paketi çalışırken FK tanımlarını saklamak için bir yere ihtiyacınız olacak (hepsini tek bir komut dosyasında yapamazsınız). Yani bazı yardımcı program veritabanında bir tablo oluşturun:

CREATE TABLE dbo.PostCommand(cmd NVARCHAR(MAX));

Sonra veritabanında, bunu yapan bir saklı yordam olabilir:

DELETE other_database.dbo.PostCommand;

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
   + ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' 
   + STUFF((SELECT ',' + c.name
    FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
        ON fkc.parent_column_id = c.column_id
        AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
+ ') REFERENCES ' + 
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id))
+ '(' + 
STUFF((SELECT ',' + c.name
    FROM sys.columns AS c 
        INNER JOIN sys.foreign_key_columns AS fkc 
        ON fkc.referenced_column_id = c.column_id
        AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + ');
' FROM sys.foreign_keys AS fk
WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

INSERT other_database.dbo.PostCommand(cmd) SELECT @sql;

IF @@ROWCOUNT = 1
BEGIN
  SET @sql = N'';

  SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
    + ' DROP CONSTRAINT ' + fk.name + ';
  ' FROM sys.foreign_keys AS fk;

  EXEC sp_executesql @sql;
END

Şimdi SSIS paketiniz bittiğinde, aşağıdakileri yapan farklı bir saklı yordam çağırmalıdır:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = cmd FROM other_database.dbo.PostCommand;

EXEC sp_executesql @sql;

Bunların hepsini sadece silme yerine kesmek uğruna yapmak için yapıyorsanız, sadece isabet alarak silmenizi öneririm. Belki kütüğün etkisini en aza indirmek için toplu olarak kaydedilen kurtarma modelini kullanın. Genel olarak, bu çözümün sadece bir silmeyi doğru sırayla kullanmaktan çok daha hızlı olacağını görmüyorum.

2014'te bu konuda daha ayrıntılı bir yazı yayınladım:

77
Aaron Bertrand

Yerleşik sp_msforeachtable saklı yordamını kullanın.

Tüm kısıtlamaları devre dışı bırakmak için:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";

Tüm kısıtlamaları etkinleştirmek için:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";

Tüm tabloları bırakmak için:

EXEC sp_msforeachtable "DROP TABLE ?";
29
Ed Randall

http://msdn.Microsoft.com/en-us/magazine/cc163442.aspx 'De "Tüm Yabancı Anahtarların Devre Dışı Bırakılması" başlığı altında iyi bir referans verilmiştir.

Ondan esinlenerek, geçici bir tablo oluşturarak ve bu tablodaki kısıtlamaları ekleyerek ve sonra kısıtlamaları bırakıp ardından bu geçici tablodan yeniden uygulayarak bir yaklaşım yapılabilir. Yeter ki burada bahsettiğim şey söylendi

 SET NOCOUNT ON

    DECLARE @temptable TABLE(
       Id INT PRIMARY KEY IDENTITY(1, 1),
       FKConstraintName VARCHAR(255),
       FKConstraintTableSchema VARCHAR(255),
       FKConstraintTableName VARCHAR(255),
       FKConstraintColumnName VARCHAR(255),
       PKConstraintName VARCHAR(255),
       PKConstraintTableSchema VARCHAR(255),
       PKConstraintTableName VARCHAR(255),
       PKConstraintColumnName VARCHAR(255)    
    )

    INSERT INTO @temptable(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName)
    SELECT 
       KeyColumnUsage.CONSTRAINT_NAME, 
       KeyColumnUsage.TABLE_SCHEMA, 
       KeyColumnUsage.TABLE_NAME, 
       KeyColumnUsage.COLUMN_NAME 
    FROM 
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
          INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
             ON KeyColumnUsage.CONSTRAINT_NAME = TableConstraints.CONSTRAINT_NAME
    WHERE
       TableConstraints.CONSTRAINT_TYPE = 'FOREIGN KEY'

    UPDATE @temptable SET
       PKConstraintName = UNIQUE_CONSTRAINT_NAME
    FROM 
       @temptable tt
          INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint
             ON tt.FKConstraintName = ReferentialConstraint.CONSTRAINT_NAME

    UPDATE @temptable SET
       PKConstraintTableSchema  = TABLE_SCHEMA,
       PKConstraintTableName  = TABLE_NAME
    FROM @temptable tt
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
          ON tt.PKConstraintName = TableConstraints.CONSTRAINT_NAME

    UPDATE @temptable SET
       PKConstraintColumnName = COLUMN_NAME
    FROM @temptable tt
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
          ON tt.PKConstraintName = KeyColumnUsage.CONSTRAINT_NAME


    --Now to drop constraint:
    SELECT
       '
       ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] 
       DROP CONSTRAINT ' + FKConstraintName + '

       GO'
    FROM
       @temptable

    --Finally to add constraint:
    SELECT
       '
       ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] 
       ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')

       GO'
    FROM
       @temptable

    GO
3
NG.

Tüm tablo kısıtlamalarını devre dışı bırak

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

- Tüm tablo kısıtlamalarını etkinleştir

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
2
Alam Usmani

Tablonun kesilmesi, yabancı anahtarları devre dışı bıraksanız bile mümkün olmayacaktır, bu nedenle tüm kayıtları tablodan çıkarmak için Delete komutunu kullanabilirsiniz, ancak aşağıdakilerden oluşan bir tablo için delete Komutunu kullandığınızdan emin olun. milyonlarca kayıt daha sonra paketiniz yavaş olacaktır... işlem günlüğü büyüklüğünüz artacaktır ve değerli disk alanınızı doldurabilir.

Kısıtlamaları kaldırırsanız, tablonuzu kirli verilerle doldurmanız gerekebilir .. ve kısıtlamaları yeniden oluşturmaya çalıştığınızda, hata vereceği için size izin vermeyebilir . Kısıtlamaları kaldırın, birbiriyle doğru şekilde ilgili verileri yüklüyorsunuz ve yeniden oluşturacağınız kısıtlama ilişkilerini yerine getiriyorsunuz.

bu yüzden lütfen her yöntemin avantaj ve dezavantajlarını dikkatlice düşünün ve gereksinimlerinize göre kullanın

1
vimal vasudevan

sql'de sıralanabilir FK'lar için sorgular çalıştırmanıza gerek yoktur. Tablo A'dan B'ye bir FK varsa, yapmanız gerekenler:

  • tablo A'dan veri sil 
  • b tablosundaki verileri sil
  • b'ye veri ekle
  • a'ya veri ekle

Hedefe kısıtlamaları kontrol etmemesini de söyleyebilirsiniz.

enter image description here

1
Diego

".Dbo" dan farklı bir veritabanı şeması kullanıyorsanız veya db'niz birkaç alandan oluşan Pk'leri içeriyorsa, lütfen Carter Medlin'in çözümünü kullanmayın, aksi halde db'nize zarar verirsiniz !!!

Farklı şemalarla çalışırken bunu deneyin (daha önce veritabanınızı yedeklemeyi unutmayın!):

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+ '['+ t.[name] + '] DISABLE;'+CHAR(13)
from  
    sys.tables t
where type='u'

select @sql = @sql +
    'ALTER INDEX ' + i.[name] + ' ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + 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

Bazı Fk-free işlemlerini yaptıktan sonra,

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' +  t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.tables t
where type='u'
print @sql

exec dbo.sp_executesql @sql;
exec sp_msforeachtable "ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL";
0
olaf870