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

Die Antwort mit der Kennzeichnung '905' sieht gut aus, funktioniert aber nicht.

Folgendes hat bei mir funktioniert. Jeder Primärschlüssel, eindeutige Schlüssel oder Standardbeschränkungen KANN NICHT deaktiviert werden. Wenn nämlich 'sp_helpconstraint '' unter status_enabled 'n/a' anzeigt, bedeutet dies, dass es NICHT aktiviert/deaktiviert werden.

-- So erzeugen Sie ein Skript für DISABLE

select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

-- So erzeugen Sie ein Skript für ENABLE

select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

Answer 2

Sie sollten Fremdschlüssel-Beschränkungen auf die gleiche Weise deaktivieren können wie andere Beschränkungen:

Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName

Vergewissern Sie sich nur, dass Sie die Einschränkung für die erste im Namen der Einschränkung aufgeführte Tabelle deaktivieren. Wenn meine Fremdschlüssel-Beschränkung zum Beispiel FK_LocationsEmployeesLocationIdEmployeeId wäre, würde ich Folgendes verwenden wollen:

Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId

obwohl die Verletzung dieser Einschränkung eine Fehlermeldung hervorruft, die nicht unbedingt diese Tabelle als Quelle des Konflikts angibt.

Answer 3

Klicken Sie mit der rechten Maustaste auf den Tabellenentwurf, gehen Sie zu Beziehungen und wählen Sie im linken Bereich den Fremdschlüssel aus. Im rechten Bereich setzen Sie die Fremdschlüssel-Beschränkung "Enforce" auf "Ja" (um Fremdschlüssel-Beschränkungen zu aktivieren) oder "Nein" (um sie zu deaktivieren).

Answer 4

Ich habe eine nützlichere Version, wenn Sie interessiert sind. Ich habe einen Teil des Codes von einer Website übernommen, deren Link nicht mehr aktiv ist. Ich habe ihn so geändert, dass er ein Array von Tabellen in der gespeicherten Prozedur zulässt und die Drop-, Truncate- und Add-Anweisungen auffüllt, bevor sie alle ausgeführt werden. So 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

--@schema 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 ['+@schema+'].['+@table+']*/'

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 ['+@schema+'].['+@table+']*/'
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 =@looper)
exec sp_executesql @sql_exec 
print @sql_exec 
set @looper = @looper + 1
end

Answer 5

Ein Skript für alle Fälle: Dieses Skript kombiniert die Befehle "truncate" und "delete" mit sp_MSforeachtable, so dass Sie das Löschen und Neuanlegen von Constraints vermeiden können - geben Sie einfach die Tabellen an, die gelöscht und nicht gekürzt werden sollen, und für meine Zwecke habe ich einen zusätzlichen Schemafilter hinzugefügt (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', @whereand=@schema
exec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclause
exec sp_MSforeachtable 'delete from ?', @whereand=@deleteclause
exec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema