完整性控制

文章目录

完整性控制一、约束1.创建约束的方法(1)创建基本表时定义表的完整性约束条件(2)修改表时定义表的完整性约束条件2.创建约束实例二、触发器(1)创建触发器语法(2)创建触发器实例

一、约束

1.创建约束的方法

(1)创建基本表时定义表的完整性约束条件

SQL语言使用CREATE TABLE语句创建基本表，其基本格式如下：

CREATE TABLE <表名＞(＜列名＞＜数据类型> DEFAULT <缺省值＞] [列级约束定义]，＜列名＞＜数据类型> DEFAULT <缺省值>] [列级约束定义],...,［＜表级约束定义＞， ，＜表级约束定义＞］);

列约束定义

列级约束定义形式如下:

[CONSTRAINT <约束名>]<列约束>

常用的列约束包括以下几种：

NOT NULL: 不允许该列取空值；不加NOT NULL限制时，该列可以取空值。PRIMARY KEY: 指明该列是主码，其值非空、唯一。UNIQUE: 该列上的值必须唯一。这相当于说明该列为候选码。CHECK (<条件>)：指明该列的值必须满足的条件，其中＜条件＞是一个涉及该列的布尔表达式。

表约束定义

一个表可以包含零个或多个<表约束定义＞，用于定义主码、其他候选码、外码和表上的其他约束。

表约束定义形式如下：

[CONSTRAINT <约束名>] <表约束>

PRIMARY KEY(A1, …, Ak)：说明属性列A1, …, Ak构成该关系的主码。UNIQUE(A1, …, Ak)：说明属性列A1, …, Ak上的值必须惟一，这相当于说明A1, …, Ak构成该关系的候选码。CHECK(<条件>)：说明该表上的一个完整性约束条件。FOREIGN KEY(A1, …, Ak) REFERENCES <外表名> (<外表主码>) [<参照触发动作>]它说明属性A1, …, Ak是关系（表） 的外码，＜外表名＞给出被参照关系的表名，<外表主码＞给出被参照系的主码 ，而＜参照触发动作＞说明违反参照完整性时需要采取的措施。

创建选课信息表并定义约束：

CREATE TABLE SC (Sno CHAR (20) , Cno CHAR (20), Grade SMALLINT CHECK (Grade>=0 AND Grade<=100) , PRIMARY KEY (Sno , Cno) , FOREIGN KEY (Sno) REFERENCES Students (Sno) , FOREIGN KEY (Cno) REFERENCES Courses (Cno)) ;

(2)修改表时定义表的完整性约束条件

SQL 使用ALTER TABLE语句修改基本表，其基本格式如下：

ALTER TABLE <表名> [ADD [COLUMN] <列名><数据类型>[列级约束定义]][ALTER [COLUMN] <列名> {SET DEFAULT <缺省值> | DROP DEFAULT}][DROP [ COLUMN ] <列名> {CASCADE | RESTRICT}][ADD <表约束定义>][DROP CONSTRAINT <约束名>{CASCADE | RESTRICT}]

2.创建约束实例

将STUDENTS表中的SNAME列设为唯一键约束：

ALTER TABLE Students ADD UNIQUE(Sname);

COURSES表中的CNAME列设为唯一键约束：

ALTER TABLE Courses ADD UNIQUE(Cname);

二、触发器

(1)创建触发器语法

创建触发器语句的一般格式如下：

CREATE TRIGGER <触发器名> <触发时间> <触发事件> ON <表名> [REFERENCING <旧/新值别名>, …, <旧/新值别名>] [FOR EACH {ROW | STATEMENT}][WHEN (<触发条件>)]<被触发的SQL语句>

<触发时间>可以是BEFORE：事件前，AFTER：事件后

<触发事件>可以是T上的INSERT、DELETE、UPDATE或UPDATE OF<触发列>, …, <触发列>

REFERENCING子句创建一些过渡变量用来存放表T和表T的行更新前的旧值和更新后的新值。<旧/新值别名>可以是如下形式之一：

OLD/NEW [ROW] [AS] <变量>：创建行过渡变量<变量>存放表T的行更新前/后的值OLD/NEW TABLE [AS] <变量>：创建表过渡变量<变量>存放表T更新前/后的值

FOR EACH ROW定义行级触发器，而FOR EACH STATEMENT定义语

句级触发器缺省时为语句级触发器

WHEN子句说明触发条件，缺省时无条件触发。<触发条件>是一个任意

布尔表达式。

<被触发的SQL语句>是触发动作体，具有如下形式：

BEGIN ATOMIC<可执行的SQL语句>;...END

(2)创建触发器实例

在STUDENTS表上建立触发器，当用户修改学生的学号时给出提示信息：

创建函数

CREATE OR REPLACE FUNCTION UpdateStudents() RETURNS TRIGGER AS$$ DECLAREBEGINRAISE NOTICE 'Trigger is working';RETURN NEW;END;$$ LANGUAGE PLPGSQL;

创建触发器

CREATE TRIGGER UpdateSnoAFTER UPDATE OF Sno ON StudentsFOR EACH ROWEXECUTE PROCEDURE UpdateStudents();

测试：

UPDATE StudentsSET Sno ='05001'WHERE Sno='06003';