右键单击表设计，进入“关系”，在左侧窗格中选择外键，在右侧窗格中，将强制外键约束设置为“Yes”(启用外键约束)或“No”(禁用外键约束)。

要禁用约束，您可以使用NOCHECK更改表

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

要使您必须使用双重检查:

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

启用时请注意双重CHECK CHECK。 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 

几乎可以肯定，检查是必须的!

在一些回答和评论中提出了这一点，但我认为再次提出这一点非常重要。

使用以下命令(不使用WITH CHECK)重新启用约束会有一些严重的缺陷。

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;

WITH CHECK | WITH NOCHECK Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints. If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint. The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

注意:WITH NOCHECK是重新启用约束的默认值。我想知道为什么……

No existing data in the table will be evaluated during the execution of this command - successful completion is no guarantee that the data in the table is valid according to the constraint. During the next update of the invalid records, the constraint will be evaluated and will fail - resulting in errors that may be unrelated to the actual update that is made. Application logic that relies on the constraint to ensure that data is valid may fail. The query optimizer will not make use of any constraint that is enabled in this way.

sys。Foreign_keys系统视图提供了一些问题的可见性。注意，它有一个is_disabled列和一个is_not_trusted列。Is_disabled指示是否根据约束验证未来的数据操作。Is_not_trusted指示表中当前的所有数据是否已经根据约束进行了验证。

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;

你的约束是可信的吗?找出……

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;

你可以使用以下命令轻松切换CONSTRAINT: 修改表TableName不检查约束所有

完成交易后，不要忘记使用以下方法再次开启: 修改表表名检查约束全部

如果你感兴趣，我有一个更有用的版本。我从这里提取了一些代码，一个网站的链接不再活跃。我修改了它，以允许存储过程中有一个表数组，并在执行所有语句之前填充删除、截断和添加语句。这使您可以控制决定哪些表需要截断。

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

找到约束条件

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

西夫韦。

注意:增加了删除约束的解决方案，这样可以删除或修改表而没有任何约束错误。