先删除主键与外建约束,再加上相关的级联更新.参考
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_A0630_a0620]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[A0630] DROP CONSTRAINT FK_A0630_a0620 ---先删除
GO---再增加
ALTER TABLE [dbo].[A0630] ADD
CONSTRAINT [FK_A0630_a0620] FOREIGN KEY
(
[a]
) REFERENCES [dbo].[a0620] (
[a]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_A0630_a0620]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[A0630] DROP CONSTRAINT FK_A0630_a0620 ---先删除
GO---再增加
ALTER TABLE [dbo].[A0630] ADD
CONSTRAINT [FK_A0630_a0620] FOREIGN KEY
(
[a]
) REFERENCES [dbo].[a0620] (
[a]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
modify ...(constraint) on delete(uplete) no action (cascade)
建议你查阅在线资料
"对复制禁用外键约束"
"创建和修改 FOREIGN KEY 约束"生成"禁用并重新启用一个约束"sql:
select 'ALTER TABLE ' + a.name + ' NOCHECK CONSTRAINT ' + b.name as 禁用约束
,'ALTER TABLE ' + a.name + ' CHECK CONSTRAINT ' + b.name as 启用约束
from sysobjects a
,sysobjects b
where a.xtype = 'F'
and a.parent_obj = b.id
生成"禁用并重新启用一个约束"sql:
select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name as 禁用约束
,'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name as 启用约束
from sysobjects a
,sysobjects b
where a.xtype = 'f'
and a.parent_obj = b.id
set @ = ''
select top 3 @ = @ + 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name + char(13)
-- ,'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name as 启用约束
from sysobjects a
,sysobjects b
where a.xtype = 'f'
and a.parent_obj = b.id
exec (@)
/*
你就在这里干坏事儿...
*/
set @ = ''
select top 3 @ = @ + 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name + char(13)
from sysobjects a
,sysobjects b
where a.xtype = 'f'
and a.parent_obj = b.id
exec (@)