一、实体完整性 和参照完整性
--设置课程表的唯一主键
ALter table colleges
add constraint PK_COLLEGES primary key(collegeid)
--为students设置唯一主键
ALter table students
add constraint PK_STUDENTS primary key(studentid);
--为courses设置唯一主键
ALter table courses
add constraint PK_COURSES primary key(courseid);
--为teachers设置唯一主键
ALter table teachers
add constraint PK_TEACHERS primary key(teacherid);
--------------------------------------
Alter Table COURSES
add constraint FK_COURSES_COLLEGES foreign key(college) references COLLEGES(collegeid) on update CASCADE on delete no action;
--添加SCTS 到course的参照关系
ALter table SCTS
add constraint FK_SCTS_COURSES foreign key(courseid) references COURSES(courseid) on update CASCADE on delete no action;
--添加SCTS 到STUDENTS的参照关系
ALter table SCTS
add constraint FK_SCTS_STUDENTS foreign key(studentid) references STUDENTS(studentid) on update CASCADE on delete no action;
--添加SCTS 到TEACHERS的参照关系
ALter table SCTS
add constraint FK_SCTS_TEACHERS foreign key(teacherid) references TEACHERS(teacherid) on update CASCADE on delete no action;
--------------------------------------
--测试主键不为空 ,且值唯一
Insert into STUDENTS(studentid,sname,idcard)values('100942348206','tests','530723199202100611');
--测试主键不为空 ,且值唯一
Insert into STUDENTS(studentid,sname,idcard,college)values('201121032310','test3','530723199202100611',null);
二、用户自定义完整性约束
USE Teaching
INSERT INTO STUDENTS(studentid, sname, IDcard, sex)
VALUES('201121032320', 'test5', '430322199110284530', '男');
----------------------------------------------------
--STUDENTS表中的IDcard属性列取值唯一
ALTER TABLE STUDENTS
ADD CONSTRAINT UQ_STUDENTS_IDCARD UNIQUE(IDcard)
--STUDENTS表中的nation属性列缺省值为“汉族”
ALTER TABLE STUDENTS
ADD CONSTRAINT CK_STUDENTS_SEX CHECK(Sex in('男','女'))
--向已经创建好的表中添加缺省约束
ALTER TABLE STUDENTS
ADD CONSTRAINT DE_STUDENTS_NATION default ('汉族') for nation;
--COURSES表中cname属性列取值必须唯一
ALTER TABLE COURSES
ADD CONSTRAINT UQ_COURSES_CNAME UNIQUE(cname)
--credit属性列取值大于0小于15
ALTER TABLE COURSES
ADD CONSTRAINT CK_COURSES_CREDIT CHECK(credit > 0 and credit <15)
--SCTS表中total_mark、exam_grade和
--regular_grade属性列的取值都必须大于等于0小于等于100
ALTER TABLE SCTS
ADD CONSTRAINT CK_COURSES_total_mark
CHECK(total_mark between 0 and 100)
ALTER TABLE SCTS
ADD CONSTRAINT CK_COURSES_exam_grade
CHECK(exam_grade between 0 and 100)
ALTER TABLE SCTS
ADD CONSTRAINT CK_COURSES_regular_grade
CHECK(regular_grade between 0 and 100)