首页 新闻 搜索 专区 学院

数据库怎么通过写代码级联删除,代码尽量简单?

-1
悬赏园豆:20 [已解决问题] 解决于 2015-05-21 15:43

create table student(
sno char(9) primary key,
sname varchar(20),
ssex char(2),
sage smallint,
sdept varchar(20));

create table course(
cno char(4) primary key,
cname varchar(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));

 


insert into student values('200215121','李勇','男',20,'CS');
insert into student values('200215122','刘晨','女',19,'CS');
insert into student values('200215123','王敏','女',18,'MA');
insert into student values('200215124','张立','男',19,'IS');

insert into COURSE values('0002','数学',NULL,2);
insert into COURSE values('0006','数据处理',NULL,2);
insert into COURSE values('0007','PASCAL语言','0006',4);
insert into COURSE values('0005','数据结构','0007',4);
insert into COURSE values('0001','数据库','0005',4);
insert into COURSE values('0003','信息系统','0001',4);
insert into COURSE values('0004','操作系统','0006',3);

insert into sc values('200215121','0001',92);
insert into sc values('200215121','0002',85);
insert into sc values('200215121','0003',88);
insert into sc values('200215122','0002',90);
insert into sc values('200215122','0003',80);
insert into sc values('200215123','0001',93);
insert into sc values('200215124','0001',90);

 

然后我要删除6号课程记录会报错怎么删除求大神指导

米老鬼的主页 米老鬼 | 初学一级 | 园豆:140
提问于:2015-05-11 22:13
< >
分享
最佳答案
0
---- 1、删除

USE testdb2
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[student]') AND type IN (N'U')) DROP TABLE [student];

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[course]') AND type IN (N'U')) DROP TABLE [course];

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sc]') AND type IN (N'U')) DROP TABLE [sc];



create table student(sno char(9) primary key,sname varchar(20),ssex char(2),sage smallint,sdept varchar(20));

create table course(cno char(4) primary key,cname varchar(40),cpno char(4),ccredit smallint);

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) on delete cascade );


 

insert into student values('200215121','李勇','',20,'CS');
insert into student values('200215122','刘晨','',19,'CS');
insert into student values('200215123','王敏','',18,'MA');
insert into student values('200215124','张立','',19,'IS');

insert into COURSE values('0002','数学',NULL,2);
insert into COURSE values('0006','数据处理',NULL,2);
insert into COURSE values('0007','PASCAL语言','0006',4);
insert into COURSE values('0005','数据结构','0007',4);
insert into COURSE values('0001','数据库','0005',4);
insert into COURSE values('0003','信息系统','0001',4);
insert into COURSE values('0004','操作系统','0006',3);

insert into sc values('200215121','0001',92);
insert into sc values('200215121','0002',85);
insert into sc values('200215121','0003',88);
insert into sc values('200215122','0002',90);
insert into sc values('200215122','0003',80);
insert into sc values('200215123','0001',93);
insert into sc values('200215124','0006',90);


DELETE FROM course WHERE cno = '0006' ;

select * from course c 
select * from student s 
select * from sc
收获园豆:20
邀月 | 高人七级 |园豆:25375 | 2015-05-12 11:38

有错

米老鬼 | 园豆:140 (初学一级) | 2015-05-12 16:29
其他回答(3)
0

你要先删外建表的记录,如sc 、student表对应的外建记录

小小高 | 园豆:1017 (小虾三级) | 2015-05-12 09:11
0

触发器试试

Froyo | 园豆:494 (菜鸟二级) | 2015-05-12 09:13
0

你在外键加上 on delete cascade

刘宏玺 | 园豆:14016 (专家六级) | 2015-05-12 09:16

具体在哪里,说清楚一点谢谢,本人刚学

支持(0) 反对(0) 米老鬼 | 园豆:140 (初学一级) | 2015-05-12 10:51

@米老鬼: 

支持(0) 反对(0) 刘宏玺 | 园豆:14016 (专家六级) | 2015-05-12 11:03

@刘宏玺: 我用的不是这个软件,是Microsoft sql好像只能写代码吧,能不能说一下怎么通过写代码实现级联删除还有写在什么地方

支持(0) 反对(0) 米老鬼 | 园豆:140 (初学一级) | 2015-05-12 11:17

@米老鬼: …………

支持(0) 反对(0) +小马哥++ | 园豆:906 (小虾三级) | 2015-05-13 13:38
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册