--例如 create table zb2 --主表 ( zb_id int primary key, )go create table zb1 --主表 ( zb_id int primary key, )go create table cb --从表 ( cb_id int primary key FOREIGN KEY REFERENCES zb1(zb_id) ON DELETE CASCADE, --指定级联删除 cb_id1 int FOREIGN KEY REFERENCES zb2(zb_id) ON DELETE CASCADE) godrop table cb,zb1 ,zb2
海阔天空,loworth,请告诉我具体做法,谢谢!
同一列上不 可以--例如 create table zb2 --主表 ( zb_id int primary key, )go create table zb1 --主表 ( zb_id int primary key, )go create table cb --从表 ( cb_id int primary key FOREIGN KEY REFERENCES zb1(zb_id) ON DELETE CASCADE, --指定级联删除 cb_id1 int FOREIGN KEY REFERENCES zb2(zb_id) ON DELETE CASCADE) go alter table cb add constraint fk_cb foreign key(cb_id) references zb2(zb_id) on delete cascadego /* 服务器: 消息 1785,级别 16,状态 1,行 1 将 FOREIGN KEY 约束 'fk_cb' 引入表 'cb' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。 */drop table cb,zb1 ,zb2
可以呀Create table TM1 ( Id int not null PRIMARY KEY )Create table TM2 ( Id int not null PRIMARY KEY )Create table TS1 ( Id int not null PRIMARY KEY, Id1 int null , Id2 int null , CONSTRAINT FK1 FOREIGN KEY (id1) REFERENCES TM1(Id ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT FK2 FOREIGN KEY (id2) REFERENCES TM2(Id ) ON DELETE CASCADE ON UPDATE CASCADE )
原来都可以,不过这样设置更新变得条件很多Create table TM1 ( Id int not null PRIMARY KEY )Create table TM2 ( Id int not null PRIMARY KEY )Create table TS1 ( Id int not null PRIMARY KEY, Id1 int null , Id2 int null , CONSTRAINT FK1 FOREIGN KEY (id1) REFERENCES TM1(Id ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT FK2 FOREIGN KEY (id1) REFERENCES TM2(Id ) ON DELETE CASCADE ON UPDATE CASCADE )insert TM1 values (1)insert TM2 values (2) insert TM2 values (1)insert TS1 values (1,1,0)update tm1 set id=2 where id=1
上面如果update tm1 set id=2 where id=1 改成 update tm1 set id=3 where id=1 则报错
haiwer 同是级联删除 或同是级联更新就不行了吧 --例如 create table zb2 --主表 ( zb_id int primary key, )go create table zb1 --主表 ( zb_id int primary key, )go create table cb --从表 ( cb_id int primary key FOREIGN KEY REFERENCES zb1(zb_id) ON DELETE CASCADE, --指定级联删除 cb_id1 int FOREIGN KEY REFERENCES zb2(zb_id) ON DELETE CASCADE) go alter table cb add constraint fk_cb foreign key(cb_id) references zb2(zb_id) on update cascadego /* OK */ alter table cb add constraint fk_cbeee foreign key(cb_id) references zb2(zb_id) on delete cascadego /* 服务器: 消息 1785,级别 16,状态 1,行 5 将 FOREIGN KEY 约束 'fk_cbeee' 引入表 'cb' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。 */drop table cb,zb1 ,zb2
有错误!
create table zb2 --主表
(
zb_id int primary key,
)go
create table zb1 --主表
(
zb_id int primary key,
)go
create table cb --从表
(
cb_id int primary key
FOREIGN KEY REFERENCES zb1(zb_id) ON DELETE CASCADE, --指定级联删除
cb_id1 int FOREIGN KEY REFERENCES zb2(zb_id) ON DELETE CASCADE)
godrop table cb,zb1 ,zb2
create table zb2 --主表
(
zb_id int primary key,
)go
create table zb1 --主表
(
zb_id int primary key,
)go
create table cb --从表
(
cb_id int primary key
FOREIGN KEY REFERENCES zb1(zb_id) ON DELETE CASCADE, --指定级联删除
cb_id1 int FOREIGN KEY REFERENCES zb2(zb_id) ON DELETE CASCADE)
go
alter table cb add constraint fk_cb foreign key(cb_id) references zb2(zb_id) on delete cascadego
/*
服务器: 消息 1785,级别 16,状态 1,行 1
将 FOREIGN KEY 约束 'fk_cb' 引入表 'cb' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。
*/drop table cb,zb1 ,zb2
Id int not null PRIMARY KEY
)Create table TM2 (
Id int not null PRIMARY KEY
)Create table TS1 (
Id int not null PRIMARY KEY,
Id1 int null ,
Id2 int null ,
CONSTRAINT FK1
FOREIGN KEY (id1) REFERENCES TM1(Id )
ON DELETE CASCADE
ON UPDATE CASCADE ,
CONSTRAINT FK2
FOREIGN KEY (id2) REFERENCES TM2(Id )
ON DELETE CASCADE
ON UPDATE CASCADE
)
Id int not null PRIMARY KEY
)Create table TM2 (
Id int not null PRIMARY KEY
)Create table TS1 (
Id int not null PRIMARY KEY,
Id1 int null ,
Id2 int null ,
CONSTRAINT FK1
FOREIGN KEY (id1) REFERENCES TM1(Id )
ON DELETE CASCADE
ON UPDATE CASCADE
,
CONSTRAINT FK2
FOREIGN KEY (id1) REFERENCES TM2(Id )
ON DELETE CASCADE
ON UPDATE CASCADE
)insert TM1 values (1)insert TM2 values (2)
insert TM2 values (1)insert TS1 values (1,1,0)update tm1 set id=2 where id=1
改成
update tm1 set id=3 where id=1
则报错
--例如
create table zb2 --主表
(
zb_id int primary key,
)go
create table zb1 --主表
(
zb_id int primary key,
)go
create table cb --从表
(
cb_id int primary key
FOREIGN KEY REFERENCES zb1(zb_id) ON DELETE CASCADE, --指定级联删除
cb_id1 int FOREIGN KEY REFERENCES zb2(zb_id) ON DELETE CASCADE)
go
alter table cb add constraint fk_cb foreign key(cb_id) references zb2(zb_id) on update cascadego
/*
OK
*/
alter table cb add constraint fk_cbeee foreign key(cb_id) references zb2(zb_id) on delete cascadego
/*
服务器: 消息 1785,级别 16,状态 1,行 5
将 FOREIGN KEY 约束 'fk_cbeee' 引入表 'cb' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。
*/drop table cb,zb1 ,zb2