create table ta(id int not null) go create table tb(id int not null ,aid int not null) goinsert ta select 1 union all select 2insert tb select 1,1 union all select 2,2 union all select 3,1 goselect * from ta /* id 1 2 */ select * from tb /* id aid 1 1 2 2 3 1 */ go ------------------------------------------------------------- --看看未创建级联引用时的delete ------------------------------------------------------------- delete from ta where id=1 select * from ta /* id 2 */ select * from tb /* id aid 1 1 2 2 3 1 可以看出删除ta,未影响tb */ go -------------------------------------------------------------- --将表里记录复原再次测试 -------------------------------------------------------------- insert ta select 1 go select * from ta /* id 1 2 */ select * from tb /* id aid 1 1 2 2 3 1 */ go--为ta创建主健 alter table ta add constraint a_pk primary key (id) go --为tb创建外健,并指定级联删除 alter table tb add constraint b_fk foreign key (aid) references ta(id) on delete cascade go --这次再试试删除ta的记录 delete from ta where id=1 select * from ta /* id 2 */ select * from tb /* id aid 2 2 这里可以看到级联删除已经工作了 */ goalter table tb drop constraint b_fk go drop table ta,tb go
ADD CONSTRAINT FK_tblShipping
FOREIGN KEY (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON DELETE CASCADE
go
create table tb(id int not null ,aid int not null)
goinsert ta select 1
union all select 2insert tb select 1,1
union all select 2,2
union all select 3,1
goselect * from ta
/*
id
1
2
*/
select * from tb
/*
id aid
1 1
2 2
3 1
*/
go
-------------------------------------------------------------
--看看未创建级联引用时的delete
-------------------------------------------------------------
delete from ta where id=1
select * from ta
/*
id
2
*/
select * from tb
/*
id aid
1 1
2 2
3 1
可以看出删除ta,未影响tb
*/
go
--------------------------------------------------------------
--将表里记录复原再次测试
--------------------------------------------------------------
insert ta select 1
go
select * from ta
/*
id
1
2
*/
select * from tb
/*
id aid
1 1
2 2
3 1
*/
go--为ta创建主健
alter table ta add constraint a_pk primary key (id)
go
--为tb创建外健,并指定级联删除
alter table tb add constraint b_fk foreign key (aid) references ta(id) on delete cascade
go
--这次再试试删除ta的记录
delete from ta where id=1
select * from ta
/*
id
2
*/
select * from tb
/*
id aid
2 2
这里可以看到级联删除已经工作了
*/
goalter table tb drop constraint b_fk
go
drop table ta,tb
go