(1) Student表的Ssex列只能取“男”或“女”
ALTER TABLE Student ADD CONSTRAINT C1 CHECK(Ssex IN('男','女'));
(2)SC表的Grade列的值在0~100之间;
ALTER TABLE SC ADD CONSTRAINT C2 CHECK(Grade>=0 and Grade<=100);
(3)Student表的Sdept列的值只能为两个字母
ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sdept);
(4)SC表的3号课程的成绩不得高于80;
ALTER TABLE SC WITH NOCHECK ADD CONSTRAINT C4 CHECK(Cno NOT LIKE '3' OR Grade<=80);
(5)要求Student表中,男生的年龄大于18岁,女生的年龄小于18岁;
ALTER TABLE student ADD CONSTRAINT c5 CHECK ( case WHEN ssex='男' AND sage>18 THEN 1
WHEN ssex='女' AND sage<18 THEN 1 ELSE 0 END = 1 );
(6)Teacher1表的Job(职称)值只能为:助教、讲师、教授、副教授,不能为工程师等;
alter table Teacher1 add constraint c7 check (job='教授' or job='助教' or job='副教授' or job='讲师')
(7)Teacher1表中,教授的工资不得低于4000元
alter table Teacher1 add constraint c7 check (job='教授' and sal>=4000)
(8)在SC中不允许200215122学生选修5号课程;
alter table sc add constraint c8 check (sno='200215122' and cno<>5)
(9)删除以上约束;
drop constraint c1(c2,c3,c4,c5,c6,c7,c8)