sql-server - key是什么 - sql reference用法




如何使用T-SQL临时禁用外键约束? (10)

是否禁用和启用SQL Server支持的外键约束? 或者是我唯一的选择,然后重新create约束条件?


SQL-92标准允许将常量声明为DEFERRABLE,以便可以在事务范围内延迟(隐式或显式)。 可悲的是,SQL Server仍然缺少这种SQL-92功能。

对我而言,将约束更改为NOCHECK类似于正在改变数据库结构 - 丢弃约束肯定是 - 并且要避免的事情(例如,用户需要增加的权限)。


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

一个脚本来统治他们:这将截断和删除命令与sp_MSforeachtable结合起来,这样您就可以避免丢弃和重新创建约束 - 只需指定需要删除而不是截断的表,并且为了我的目的,我已经包含了一个额外的架构过滤器测量(在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]

你最好的选择是DROP和CREATE外键约束。

我没有在这篇文章中找到可以为我“按原样”工作的例子,如果外键引用不同的模式,那么就不会工作,如果外键引用多个列,另一个将不起作用。 该脚本考虑了每个外键多个模式和多个列。

这里是生成“ADD CONSTRAINT”语句的脚本,对于多列,它将用逗号分隔它们( 确保在执行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

这是生成“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

如果你有兴趣,我有一个更有用的版本。 我从这里提取了一些代码http://www.dbaservices.com.au/news/drop-recreate-foreign-key-constraints/将其修改为允许在存储过程中创建一个表的数组,并将它填充到drop ,截断,在执行所有这些语句之前添加语句。 这使您可以控制决定哪些表需要截断。

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

如果您想要禁用数据库中的所有约束,只需运行以下代码:

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

要重新打开它们,请运行:(打印是可选的,当然它只是列出表格)

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

将数据从一个数据库填充到另一个数据库时,我发现它很有用。 这比删除约束要好得多。 正如你所提到的那样,当删除数据库中的所有数据并重新填充数据时(比如在测试环境中),它会很方便。

如果您要删除所有数据,您可能会发现此解决方案有帮助。

有时也可以禁用所有触发器,您可以在here看到完整的解决方案。


找到约束

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

执行由此SQL生成的SQL

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公司。

注意:添加了用于删除约束的解决方案,以便可以在没有任何约束错误的情况下删除或修改表。


标有'905'的答案看起来不错,但不起作用。

以下为我工作。 任何主键,唯一键或默认约束都无法禁用。 事实上,如果'sp_helpconstraint''在status_enabled中显示'n / a' - 意味着它不能启用/禁用。

- 将脚本生成为DISABLE

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

- 将脚本生成为ENABLE

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

要禁用约束,请使用NOCHECK更改表

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

为了使你必须使用双重CHECK

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
  • 启用时请注意双重检查
  • ALL意味着表中的所有约束。

一旦完成,如果您需要检查状态,请使用此脚本列出约束状态。 将非常有帮助:

    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 

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

哈姆林,我同意你的看法。 当您使用SSIS传输数据或想要复制数据时,似乎很有必要暂时禁用或删除外键约束,然后重新启用或重新创建它们。 在这些情况下,参照完整性不是问题,因为它已经保存在源数据库中。 因此,您可以放心关于此事。







constraints