实验7-数据控制(完整性)

实验 数据控制（完整性）

一、实验目的：

掌握什么是数据库的完整性，掌握用SQL语言定义关系模式的完整性约束条件。

二、准备工作：

1.理解三类完整性，使用SQL语言定义完整性约束；

2.掌握CHECK短语、CONSTRAIN子句；

3.掌握触发器定义和使用。

三、实验内容：

1. 实体完整性

【例4】定义表的主码。

关系模型的实体完整性在CREATE TABLE 中用PRIMARY KEY 定义。定义主码的方法

分为定义为列级约束条件和定义为表级约束条件两种。

[例4-1]定义表Student，并将其中的Sno 属性定义为主码。

CREATE TABLE STUDENT(

SNO CHAR(7) PRIMARY KEY,

SNAME CHAR(8) NOT NULL,

SSEX CHAR(2),

SAGE SMALLINT,

SDEPT CHAR(20));

或者：

CREATE TABLE STUDENT(

SNO CHAR(7),

SNAME CHAR(8),

SSEX CHAR(2),

SAGE SMALLINT,

SDEPT CHAR(20),

PRIMARY KEY (SNO));

[例4-2]定义表SC,将其中的属性Sno,Cno 定义为主码。

对于多个属性构成的码，只能够将其定义为表级约束条件，而无法用列级约束条件来实

现。

CREATE TABLE SC(

SNO CHAR(7) NOT NULL,

CNO CHAR(4) NOT NULL,

GRADE SMALLINT,

PRIMARY KEY (SNO,CNO));

2. 参照完整性。

【例5】定义表的外码。

关系模型的参照完整性是在CREATE TABLE 中用FOREIGN KEY 语句来定义的，并用

REFERENCES 来指明外码参照的是哪些表的主码。

定义表SC，其中Sno 参照表Student 的主码Sno，Cno 参照表Course 的主码Cno。

CREATE TABLE SC(

SNO CHAR(7) NOT NULL,

CNO CHAR(4) NOT NULL,

GRADE SMALLINT,

PRIMARY KEY (SNO,CNO),

FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),

FOREIGN KEY (CNO) REFERENCES COURSE(CNO));

3. 用户定义完整性。

【例6】用户定义的属性上的约束条件。

[例6-1]列值非空。在定义SC 表时，Sno、Cno 和Grade 属性都不允许取空值。

在不特别声明的情况下，非码属性的值是允许取空值的。

CREATE TABLE SC(

SNO CHAR(7) NOT NULL, /*列值非空的约束NOT NULL*/

CNO CHAR(4) NOT NULL,

GRADE SMALLINT NOT NULL);

[例6-2]列值唯一。建立部门表DEPT，要求部门名称Dname 取值唯一，部门编号Deptno 属

性为主码。

CREATE TABLE DEPT(

DEPTNO NUMERIC(7) PRIMARY KEY,

DNAME VARCHAR(9) UNIQUE, /*UNIQUE 约束要求Dname 取值唯一*/

LOCATION VARCHAR(10));

[例6-3]CHECK 短语指定列值应该满足的条件。定义表Student，属性Ssex 的值只允许取“男”

或“女”；定义表SC，属性Grade 的值定义在0－100 之间。

CREATE TABLE STUDENT(

SNO CHAR(7) PRIMARY KEY,

SNAME CHAR(8) NOT NULL,

SSEX CHAR(2) CHECK(SSEX IN(‘男’,’女’)), /*CHECK 语句约束条件*/

SAGE SMALLINT,

SDEPT CHAR(20));

CREATE TABLE SC(

SNO CHAR(7) NOT NULL,

CNO CHAR(4) NOT NULL,

GRADE SMALLINT CHECK (GRADE>0 AND GRADE<100), /*CHECK 语句约束

条件*/

PRIMARY KEY (SNO,CNO),

FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),

FOREIGN KEY (CNO) REFERENCES COURSE(CNO));

【例7】用户定义的元组上的约束条件。

CHECK 短语不光能够定义属性列上的约束条件，还允许用户定义元组级的约束条件。

定义表Student，要求当学生性别为男时，其名字不能以Ms.打头。

CREATE TABLE STUDENT(

SNO CHAR(7) PRIMARY KEY,

SNAME CHAR(8) NOT NULL,

SSEX CHAR(2),

SAGE SMALLINT,

SDEPT CHAR(20),

CHECK (SSEX = ‘女’ OR SNAME NOT LIKE ‘Ms.%’));

/*定义了Sname 和Ssex 之间的约束条件*/

4. CONSTRAINT 完整性约束命名子句。

【例8】在定义表时利用约束命名子句对完整性约束条件命名，能够灵活地增加或删除一个

完整性约束条件。

[例8-1]定义表Student，要求学号在90000－99999 之间，姓名不能取空值，年龄小于30，

性别只能是“男”或“女”。要求全部用约束命名子句实现。

CREATE TABLE STUDENT(

SNO NUMERIC(5)

CONSTRAINT C1 CHECK (SNO BETWEEN 90000 AND 99999),

SNAME VARCHAR(20)

CONSTRAINT C2 NOT NULL,

SAGE NUMERIC(3)

CONSTRAINT C3 CHECK (SAGE < 30),

SSEX VARCHAR(2)

CONSTRAINT C4 CHECK (SSEX IN (‘男’,’女’)),

CONSTRAINT StudentKey PRIMARY KEY(SNO));

在表Student 上共定义了5 个约束条件，包括主码约束以及C1、C2、C3、C4 四个列级

约束。

[例8-2]修改表Student 中的完整性，去掉对性别的，并将年龄的由小于30 改

为小于40。

ALTER TABLE STUDENT /*去掉对性别的条件C4*/

DROP CONSTRAINT C4;

ALTER TABLE STUDENT /*先删掉原来的约束条件再增加一个新的约束条件*/

DROP CONSTRAINT C3;

ALTER TABLE STUDENT

ADD CONSTRAINT C3 CHECK(SAGE <40);

5. 触发器。

触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自动触发执行，是提高

数据库服务器性能的有力工具。

触发器分为三类，更新触发器、插入触发器和删除触发器。

能够定义触发器的用户有：

1） 表的所有者；

2） 系统管理员；

3） 拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。

【例9】定义表TAB,并在其上定义触发器TRI，在对TAB 的插入和更新前检查，如果插入

或更新的值在100－1000 之间的话，将值置为50；如果值大于1000 的话，则给出新值不允

许大于1000 的提示。

CREATE TABLE tab (col int);

INSERT INTO tab VALUES(10);

INSERT INTO tab VALUES(20);

CREATE TRIGGER tri BEFORE UPDATE OR INSERT ON tab

FOR EACH ROW

AS

BEGIN

IF new.col > 100 AND new.col < 1000 THEN

new.col := 50;

END IF;

IF new.col > 1000 THEN

RAISE EXCEPTION ‘New values can not more than 1000’;END IF;

END;

检测：1）向表TAB 插入数据（150）。

执行：INSERT INTO TAB VALUES(150);

结果：插入的数据为150。值在100－1000 之间，触发器TRI 自动执行，将插入

的值置为50。

2）对表中的数据进行更新，将20 更新为1500。

执行：UPDATE TAB SET COL = 1500 WHERE COL = 20;

结果：系统报出‘New values can not more than 1000’的错误，

对表进行查询后发现，表中的数据并没有发生改变。说明在更新数据时，值大于1000

则触发触发器TRI 自动执行，系统报错，更新无效。

【例10】删除触发器TRI。

DROP TRIGGER TRI ON TAB;

删除触发器TRI 之后，在对TAB 进行插入和更新就不再有以上了。

四、思考题：

1.什么是数据库的完整性？

2.数据库的完整性概念和数据库的安全性概念有什么区别和联系？

3.什么是数据库的完整性约束条件？