--OrderMaster 添加级联更新/删除 ALTER TABLE dbo.OrderMaster ADD CONSTRAINT FK_OrderMaster_Customers FOREIGN KEY ( CustID ) REFERENCES dbo.Customers ( CustID ) ON UPDATE CASCADE ON DELETE CASCADEgo--OutstockMaster 添加级联更新 ALTER TABLE dbo.OutstockMaster ADD CONSTRAINT FK_OutstockMaster_Customers FOREIGN KEY ( CustID ) REFERENCES dbo.Customers ( CustID ) ON UPDATE CASCADE ON DELETE CASCADE go
--下面是例子--检查对象是否存在,如果存在,删除 if exists (select * from dbo.sysobjects where id = object_id(N'[OrderMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [OrderMaster] GOif exists (select * from dbo.sysobjects where id = object_id(N'[OutstockMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [OutstockMaster] GOif exists (select * from dbo.sysobjects where id = object_id(N'[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Customers] GO--创建表环境 --主表:Customers create table Customers(CustID varchar(10) not null constraint PK_Customers primary key ,aa int)--从表:OrderMaster create table OrderMaster(CustID varchar(10) not null ,indexid varchar(10) not null ,value varchar(50)) alter table OrderMaster add constraint PK_OrderMaster PRIMARY KEY CLUSTERED (CustID,indexid)--添加级联更新 ALTER TABLE dbo.OrderMaster ADD CONSTRAINT FK_OrderMaster_Customers FOREIGN KEY ( CustID ) REFERENCES dbo.Customers ( CustID ) ON UPDATE CASCADE ON DELETE CASCADE--从表:OutstockMaster create table OutstockMaster(CustID varchar(10) not null ,indexid varchar(10) not null ,value varchar(50)) alter table OutstockMaster add constraint PK_OutstockMaster PRIMARY KEY CLUSTERED (CustID,indexid)--添加级联更新 ALTER TABLE dbo.OutstockMaster ADD CONSTRAINT FK_OutstockMaster_Customers FOREIGN KEY ( CustID ) REFERENCES dbo.Customers ( CustID ) ON UPDATE CASCADE ON DELETE CASCADE go--插入数据 insert into Customers values(1,1) insert into OrderMaster values(1,1,'aa') insert into OutstockMaster values(1,1,'bb')--显示结果 select * from Customers a join OrderMaster b on a.CustID=b.CustID join OutstockMaster c on a.CustID=c.CustID--更新,验证更新级联关系 update Customers set CustID=12--显示结果 select * from Customers a join OrderMaster b on a.CustID=b.CustID join OutstockMaster c on a.CustID=c.CustID--删除,验证删除级联关系 delete from Customers where CustID=12--显示结果 select * from Customers a join OrderMaster b on a.CustID=b.CustID join OutstockMaster c on a.CustID=c.CustID/*--测试结果 CustID aa CustID indexid value CustID indexid value ------ --- ------- -------- ------ ------- -------- ------- 1 1 1 1 aa 1 1 bb(所影响的行数为 1 行) (所影响的行数为 1 行)CustID aa CustID indexid value CustID indexid value ------ --- ------- -------- ------ ------- -------- ------- 12 1 1 1 aa 1 1 bb(所影响的行数为 1 行) (所影响的行数为 1 行)CustID aa CustID indexid value CustID indexid value ------ --- ------- -------- ------ ------- -------- -------(所影响的行数为 0 行) --*/
将 FOREIGN KEY 约束 'FK_Outstockbill_Customer' 引入表 'OutStockBill' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。
服务器: 消息 1750,级别 16,状态 1,行 1
未能创建约束。请参阅前面的错误信息。
ALTER TABLE dbo.OrderMaster ADD CONSTRAINT
FK_OrderMaster_Customers FOREIGN KEY
(
CustID
) REFERENCES dbo.Customers
(
CustID
) ON UPDATE CASCADE
ON DELETE CASCADEgo--OutstockMaster 添加级联更新
ALTER TABLE dbo.OutstockMaster ADD CONSTRAINT
FK_OutstockMaster_Customers FOREIGN KEY
(
CustID
) REFERENCES dbo.Customers
(
CustID
) ON UPDATE CASCADE
ON DELETE CASCADE
go
if exists (select * from dbo.sysobjects where id = object_id(N'[OrderMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OrderMaster]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[OutstockMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [OutstockMaster]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Customers]
GO--创建表环境
--主表:Customers
create table Customers(CustID varchar(10) not null constraint PK_Customers primary key
,aa int)--从表:OrderMaster
create table OrderMaster(CustID varchar(10) not null
,indexid varchar(10) not null
,value varchar(50))
alter table OrderMaster add constraint PK_OrderMaster PRIMARY KEY CLUSTERED (CustID,indexid)--添加级联更新
ALTER TABLE dbo.OrderMaster ADD CONSTRAINT
FK_OrderMaster_Customers FOREIGN KEY
(
CustID
) REFERENCES dbo.Customers
(
CustID
) ON UPDATE CASCADE
ON DELETE CASCADE--从表:OutstockMaster
create table OutstockMaster(CustID varchar(10) not null
,indexid varchar(10) not null
,value varchar(50))
alter table OutstockMaster add constraint PK_OutstockMaster PRIMARY KEY CLUSTERED (CustID,indexid)--添加级联更新
ALTER TABLE dbo.OutstockMaster ADD CONSTRAINT
FK_OutstockMaster_Customers FOREIGN KEY
(
CustID
) REFERENCES dbo.Customers
(
CustID
) ON UPDATE CASCADE
ON DELETE CASCADE
go--插入数据
insert into Customers values(1,1)
insert into OrderMaster values(1,1,'aa')
insert into OutstockMaster values(1,1,'bb')--显示结果
select * from Customers a
join OrderMaster b on a.CustID=b.CustID
join OutstockMaster c on a.CustID=c.CustID--更新,验证更新级联关系
update Customers set CustID=12--显示结果
select * from Customers a
join OrderMaster b on a.CustID=b.CustID
join OutstockMaster c on a.CustID=c.CustID--删除,验证删除级联关系
delete from Customers where CustID=12--显示结果
select * from Customers a
join OrderMaster b on a.CustID=b.CustID
join OutstockMaster c on a.CustID=c.CustID/*--测试结果
CustID aa CustID indexid value CustID indexid value
------ --- ------- -------- ------ ------- -------- -------
1 1 1 1 aa 1 1 bb(所影响的行数为 1 行)
(所影响的行数为 1 行)CustID aa CustID indexid value CustID indexid value
------ --- ------- -------- ------ ------- -------- -------
12 1 1 1 aa 1 1 bb(所影响的行数为 1 行)
(所影响的行数为 1 行)CustID aa CustID indexid value CustID indexid value
------ --- ------- -------- ------ ------- -------- -------(所影响的行数为 0 行)
--*/
OrderMaster和OutstockMaster 中的CustID也做对应改变还是:
Customers 中的CustID改变后
OrderMaster 中的CustID也做对应改变
OrderMaster 中的CustID 改变后
OutstockMaster 中的CustID也做对应改变
如果是第一种,就没问题,你仔细看我给的例子,在我的电脑上是测试通过的.如果是第二种,那没办法,只能用触发器来实现:
OrderMaster 中的CustID 改变后
OutstockMaster 中的CustID也做对应改变
ODBC 错误: [Microsoft][ODBC SQL Server Driver][SQL Server]将 FOREIGN KEY 约束 'FK_OutstockMaster_Customer' 引入表 'OutstockMaster' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。
OrderMaster
OutstockMaster表中创建关系.如果实在不行.你可以在查询分析器中用SQL语句来建立关系(如我上面写的)