创建三个表如下:create table a(
aid varchar(10) primary key,
shuxing_a varchar(10)
)
create table b(
bid varchar(10) primary key,
shuxing_b varchar(10)
)
create table a_b(
aid varchar(10) foreign key references a(aid) ,
bid varchar(10) foreign key references b(bid),
)
--插入数据:
insert into a values('01', 'aa')
insert into a values('02', 'bb')
insert into a values('03', 'cc')insert into b values('10', 'dd')
insert into b values('20', 'ee')
insert into b values('30', 'ff')insert into a_b values('01', '10')
insert into a_b values('02', '20')
insert into a_b values('02', '30')
此时我要删除a表中的‘01’,‘aa’怎么删除?还有,我要同时删除‘01’,‘aa’,和a_b表中的‘01’,‘10’又怎么删除?求大侠指点,在线等待
aid varchar(10) primary key,
shuxing_a varchar(10)
)
create table b(
bid varchar(10) primary key,
shuxing_b varchar(10)
)
create table a_b(
aid varchar(10) foreign key references a(aid) ,
bid varchar(10) foreign key references b(bid),
)
--插入数据:
insert into a values('01', 'aa')
insert into a values('02', 'bb')
insert into a values('03', 'cc')insert into b values('10', 'dd')
insert into b values('20', 'ee')
insert into b values('30', 'ff')insert into a_b values('01', '10')
insert into a_b values('02', '20')
insert into a_b values('02', '30')
此时我要删除a表中的‘01’,‘aa’怎么删除?还有,我要同时删除‘01’,‘aa’,和a_b表中的‘01’,‘10’又怎么删除?求大侠指点,在线等待
aid varchar(10) constraint [fk_a_b_aid] foreign key references a(aid) on delete cascade,
bid varchar(10) constraint [fk_a_b_bid] foreign key references b(bid) on delete cascade
)
--如果你这样创建
create table a_b(
aid varchar(10) constraint [fk_a_b_aid] foreign key references a(aid) ,
bid varchar(10) constraint [fk_a_b_bid] foreign key references b(bid)
)
--即不加级联删除,还可以这样删,
alter table a_b nocheck constraint [fk_a_b_aid];
delete a where aid='01';
--删除完后你要改回去,如下,不然会一直不检查外键约束
alter table a_b check constraint [fk_a_b_aid];
这句话不是很理解额能解释一下吗?为什么要加这句话呀?
alter table a_b nocheck constraint [fk_a_b_aid];
delete a where aid='01';
有什么区别吗?我SQL学的不是很透,指教一下~~谢谢了
--加了以后,则不会出错,但不会删除表a_b中对应的值
--如果加了on delete cascade则为级联删除,删除a的同时,a_b中的值也会直接删除
alter table a_b nocheck constraint [fk_a_b_aid];
delete a where aid='01';基本的知识还是要自已多看的