我一共建了三个表:
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
) ;我现在要删掉Student表,用DROP TABLE Student CASCADE;会出现CASCADE错误,我在网上查要先删除外码,但是我那个外码(Sno)是第三个表的主码,根据完整性,不能删吧。该怎么做啊,谢谢大家啦!!!
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
) ;我现在要删掉Student表,用DROP TABLE Student CASCADE;会出现CASCADE错误,我在网上查要先删除外码,但是我那个外码(Sno)是第三个表的主码,根据完整性,不能删吧。该怎么做啊,谢谢大家啦!!!
create table zb --主表
(
zb_id int primary key,
)gocreate table cb --从表
(
cb_id int primary key
FOREIGN KEY REFERENCES zb(zb_id) ON DELETE CASCADE , --指定级联删除
)
go--主表数据
insert zb values(1)
insert zb values(2)
insert zb values(3)
--从表数据
insert cb values(1)
insert cb values(2)
insert cb values(3)
select * from zb
select * from cb
zb_id
-----------
1
2
3(所影响的行数为 3 行)mx_id
-----------
1
2
3(所影响的行数为 3 行)
--级联删除
delete from zb where zb_id = 1
select * from zb
select * from cb
zb_id
-----------
2
3(所影响的行数为 2 行)mx_id
-----------
2
3
(所影响的行数为 2 行)alter table cb add constraint fk_cb foreign key(mx_id) references zb(zb_id) on delete cascadealter table 从表 add constraint 外键名 foreign key(从表字段) references 主表(主表字段) on delete cascade