删除sc表与stu、course表上的参照完整性
1.
CREATE TABLE SC1
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade INT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Stu(Sno)
ON DELETE CASCADE /*级联删除SC表中相应的元组*/
ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE /*当更新course表中的cno时,级联更新SC表中相应的元组*/
)
2.
insert into sc1 select * from sc
3.
insert into sc1 values('200215120','1',90)
4.
insert into sc1 values('200215121','1',90)
5.
select * from sc1
6.
update stu set sno='200215120' where sno='200215121'
select * from sc1
7.
delete from stu where sno='200215120'
select * from sc1
8.
delete from course where cno='2'
select * from sc1
9.
update course set cno='9' where cno='2'
select * from sc1
10.
CREATE TABLE SC2
( Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT NOT NULL,
PRIMARY KEY (Sno,Cno))
11.
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK (Ssex IN ('男','女') ),/*性别只允许取'男'或'女' */
Sage INT,
Sdept CHAR(20) )
12.