---- 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