实验八：数据库的完整性约束

一、实验目的

1.掌握主键约束、外键约束及及check约束的用法；

2.掌握默认值约束的应用；

3.了解规则、触发器的使用。

二、实验环境

已安装SQL Server 2005 企业版的计算机；

具有局域网环境,有固定IP;

三、实验学时

2学时

四、实验要求

1.熟悉数据库完整性相关概念及约束；

2.了解约束、默认值及规则；

3.完成实验报告；

五、实验内容及步骤

以系统管理员身份登录到SQL Server服务器，并使用T-SQL语句实现以下操作；

1.请用至少2种方法定义stu数据库中student表的主键sno；

方法1：

USE student

ALTER TABLE student

ADD CONSTRAINT PK_student PRIMARY KEY (Sno)

方法2：在建表时使用PRIMARY KEY(columnID,…)

2.将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;

USE student

ALTER TABLE course

ADD CONSTRAINT cno_pk PRIMARY KEY(Cno)

3.为表course中的字段cname添加唯一值约束；

USE student

ALTER TABLE course

ADD CONSTRAINT UC_Cname UNIQUE(Cname)

4.将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;

USE student

ALTER TABLE sc

ADD CONSTRAINT sc_pk PRIMARY KEY (Sno,Cno)

5.对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：

1)删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；

2)修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；

USE student

ALTER TABLE sc

ADD CONSTRAINT FK_Sno FOREIGN KEY (Sno)

REFERENCES student(Sno) ON DELETE CASCADE --级联删除

3)修改course表cno字段值时，该字段在sc表中的对应值也应修改；

4)删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；

USE student

ALTER TABLE sc

ADD CONSTRAINT FK_Cno FOREIGN KEY (Cno)

REFERENCES course(Cno) ON UPDATE CASCADE ON DELETE CASCADE--级联删除和更新

5)向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；

6.定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；

USE student

ALTER TABLE student

ADD CONSTRAINT CHK_Sno CHECK (Sno LIKE '[1-9][0][0]%' AND LEN(Sno)=9)

7.定义stu数据库中student表中学生年龄值在16-25范围内；

USE student

ALTER TABLE student

ADD CONSTRAINT CHK_Sage CHECK (Sage>=16 AND Sage<=25)

8.定义stu数据库中student表中学生姓名长度在2-8之间；

USE student

ALTER TABLE student

ADD CONSTRAINT CHK_Sname CHECK (LEN(Sname)>=2 AND LEN(Sname)<=8)

9.定义stu数据库中student表中学生性别列中只能输入“男”或“女”；

USE student

ALTER TABLE student

ADD CONSTRAINT CHK_Ssex CHECK (Ssex='男' OR Ssex='女')

10.定义stu数据库student表中学生年龄值默认值为20；

USE student

ALTER TABLE student

ADD CONSTRAINT DEF_Sage DEFAULT('20') FOR Sage WITH VALUES --WITH VALUES使其自动填充

11.修改student表学生的年龄值约束可以为15-30范围内；

USE student

ALTER TABLE student

DROP CONSTRAINT CHK_Sage

ALTER TABLE student

ADD CONSTRAINT CHK_Sage CHECK (Sage>=15 AND Sage<=30)

12.删除上述唯一值约束、外键约束及check约束；

USE student

ALTER TABLE student

DROP CONSTRAINT CHK_Sage,CHK_Ssex,CHK_Sname,CHK_Sno

ALTER TABLE sc

DROP CONSTRAINT FK_Cno,FK_Sno

13.思考题：定义规则对象，用于保证course表中cno字段值为5个数字字符；（可选做）

由于实际情况，更改为以下限定：

CREATE TRIGGER dml_course_Cno

ON course

AFTER INSERT,UPDATE

AS

    DECLARE @Cno CHAR(4);

    SELECT @Cno=Cno FROM inserted;

    IF (LEN(@Cno)<1 OR LEN(@Cno)>2 OR (@Cno NOT LIKE '[1-9]' AND @Cno NOT LIKE '[1-9][0-9]'))

        BEGIN

        PRINT '您输入的课程号(Cno)并不符合要求!'

        ROLLBACK

        END

14.思考题：向sc表中插入或修改一条记录时，通过触发器检查记录sno字段的值在student表中是否存在，若不存在，则取消插入或修改操作；（可选做）

CREATE TRIGGER dml_sc_Sno

ON sc

AFTER INSERT,UPDATE

AS

    DECLARE @Sno CHAR(9);

    SELECT @Sno=Sno FROM inserted;

    IF NOT EXISTS (SELECT * FROM student WHERE Sno=@Sno)

        BEGIN

        PRINT '该学号不存在于student表!'

        ROLLBACK

        END

15.思考题：删除student表中一条记录的同时删除其在sc表中对应的记录；

CREATE TRIGGER dml_student_Sno

ON student

AFTER DELETE

AS

    DECLARE @Sno CHAR(9);

    SELECT @Sno=Sno FROM deleted;

    DELETE FROM sc WHERE Sno=@Sno

六、出现问题及解决办法

如：某些操作无法执行，如何解决？