----建立帐号---帐号表
create table account
(
aid int primary key,---卡号
acid int references customer (cid),---客户编号,引用客户表
aopendate datetime not null,----开卡日期
abalance float default(0) ---账面余额
)alter table account
add constraint CK_abalance check(abalance>=0)insert into account
select 10001,100001,'2007-12-12 12:12:12',0 union
select 10002,100002,'2007-12-12 12:12:13',0 union
select 10003,100003,'2007-12-12 12:12:14',0 union
select 10004,100004,'2007-12-12 12:12:15',0 union
select 10006,100001,'2008-06-16 06:06:17',0 ----建立交易记录表
create table record
(
rid int identity(1,1) primary key,
rtransdate datetime not null,---交易日期
raidfrom int references account(aid) not null,--从哪转出
raidto int references account(aid) not null,--转给谁
rtransmoney float default(0) not null,---交易金额
rtranstype int default(0) not null,---交易类型:转账3,存款1,取款2
rtranssummary varchar(400) ,---摘要
rabalance float not null----当前余额
)问题:怎么删除客户ID的时候这三个表的记录全部删除,因为都有外键约束,如果一个客户有多个卡号,怎么才能做到同时把该客户的所有卡号加卡号的记录一下全部删除?大虾们 ,求解释啊!!!
create table account
(
aid int primary key,---卡号
acid int references customer (cid),---客户编号,引用客户表
aopendate datetime not null,----开卡日期
abalance float default(0) ---账面余额
)alter table account
add constraint CK_abalance check(abalance>=0)insert into account
select 10001,100001,'2007-12-12 12:12:12',0 union
select 10002,100002,'2007-12-12 12:12:13',0 union
select 10003,100003,'2007-12-12 12:12:14',0 union
select 10004,100004,'2007-12-12 12:12:15',0 union
select 10006,100001,'2008-06-16 06:06:17',0 ----建立交易记录表
create table record
(
rid int identity(1,1) primary key,
rtransdate datetime not null,---交易日期
raidfrom int references account(aid) not null,--从哪转出
raidto int references account(aid) not null,--转给谁
rtransmoney float default(0) not null,---交易金额
rtranstype int default(0) not null,---交易类型:转账3,存款1,取款2
rtranssummary varchar(400) ,---摘要
rabalance float not null----当前余额
)问题:怎么删除客户ID的时候这三个表的记录全部删除,因为都有外键约束,如果一个客户有多个卡号,怎么才能做到同时把该客户的所有卡号加卡号的记录一下全部删除?大虾们 ,求解释啊!!!
标题:两表通过字段关联进行级联删除。
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-20
地点:广东深圳
*/create table ta(id int not null)
create table tb(id int , aid int)
insert into ta values(1)
insert into ta values(2)
insert into tb values(1 , 1)
insert into tb values(2 , 2)
insert into tb values(3 , 1)
go--一、查看原始数据
--ta表的原始数据
select * from ta
/*
id
-----------
1
2
*/
--tb表的原始数据
select * from tb
/*
id aid
----------- -----------
1 1
2 2
3 1
*/--二、看看没有创建级联删除时的情况(删除ta表id=1的数据,看看是否影响tb表)
delete from ta where id = 1
select * from ta
/*
id
-----------
2
*/
select * from tb
/*
id aid
----------- -----------
1 1
2 2
3 1
*/--三、恢复原始数据,创建级联删除,删除ta表id=1的数据,看看是否影响tb表
insert into ta values(1)
--为ta创建主健
alter table ta add constraint pk_ta_id primary key (id)
go
--为tb创建外健,并指定级联删除
alter table tb add constraint fk_tb_aid foreign key (aid) references ta(id) on delete cascade
go
delete from ta where id = 1
select * from ta
/*
id
-----------
2
*/
select * from tb
/*
id aid
----------- -----------
2 2
*/--删除级联约束
alter table tb drop constraint fk_tb_aid
go
--删除测试表
drop table ta , tb
go
where raidfrom in (select aid from account where acid = @acid)delete from record
where raidto in (select aid from account where acid = @acid)deleft from account where acid = @acid
ON DELETE CASCADE