数据库完整性控制

一、实体完整性 和参照完整性

--设置课程表的唯一主键
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)