级联引用完整性约束create table t2( ston int not null primary key nonclustered, addr varchar(30))gocreate table t1( tid int references t2 (ston) ON DELETE CASCADE ON UPDATE CASCADE , insto int references t2 (ston) , --再加上 ON DELETE CASCADE ON UPDATE CASCADE 是错的,将导致循环或多重级联路径 outsto int)
对,触发器也不是那么容易解决如果就这样写,还是不行 create trigger T_update on t2 for update as update t1 set insto=a.ston from inserted a where t1.insto=a.ston
用触发器和外键(将T1.InSto外键关联T2.StoN) create trigger T_update on t2 for insertdelete from t2 where OutSto not in(select InSto from t1)
修改和删除触发器 create trigger T_update on t2 for delete as delete from t2 where OutSto in(select StoN from deleted) or InSto in(select StoN from deleted) create trigger T_update on t2 for update update t1 set OutSto= StoN ,InSto = StoN from deleted ,insered where ....
用触发器和外键(将T1.InSto外键关联T2.StoN) 用问题,修改 create trigger T_update on t1 for insertdelete from t1 where OutSto not in(select InSto from t2)
T1.InSto和T1.OutSto都必须取T2中的StoN的值,也就是说T1.InSto和T1.OutSto的取值都是T2.StoN的值,只是在T1中某条具体的记录里,InSto和OutSto可能分别对应着T2中两条不同纪录的StoN值,而不是说“可能T1.InSto=T2.StoN也可能T1.OutSto=T2.StoN”
而且在任何时候都要保证T1.InSto和T1.OutSto能与T2.StoN保持在DELETE和UPDATE上的数据一致
create table t1(
tid int,
insto int,
outsto int)drop table t2
create table t2(
ston int not null,
addr varchar(30))alter table t2
add constraint pk_t2 primary key nonclustered
(ston)alter table t1
add constraint fk_t1_1 foreign key (insto) references t2 (ston)alter table t1
add constraint fk_t1_2 foreign key (outsto) references t2 (ston)将数据表清空后,建立外键关联!
没有办法保证DELETE和UPDATE级联
'T1' 表
- 不能创建关系 'FK_T1_T2_2'。
ODBC 错误: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE 语句与 COLUMN FOREIGN KEY 约束 'FK_T1_T2_2' 冲突。该冲突发生于数据库 'DaR',表 'T2', column 'StoN'。
ston int not null primary key nonclustered,
addr varchar(30))gocreate table t1(
tid int references t2 (ston) ON DELETE CASCADE ON UPDATE CASCADE ,
insto int references t2 (ston) ,
--再加上 ON DELETE CASCADE ON UPDATE CASCADE 是错的,将导致循环或多重级联路径
outsto int)
create trigger T_update on t2
for update
as update t1
set insto=a.ston
from inserted a
where t1.insto=a.ston
create trigger T_update on t2
for insertdelete from t2 where OutSto not in(select InSto from t1)
create trigger T_update on t2
for delete
as
delete from t2 where
OutSto in(select StoN from deleted) or InSto in(select StoN from deleted) create trigger T_update on t2
for update
update t1 set OutSto= StoN ,InSto = StoN
from deleted ,insered where ....
用问题,修改
create trigger T_update on t1
for insertdelete from t1 where OutSto not in(select InSto from t2)