循序渐进讲解Oracle数据库的完整性概念

Oracle在CREATE TABLE语句中提供了PRIMARY KEY子句，供用户在建表时指定关系的主码列。例如，在学生-选课数据库中，要定义Student表的Sno为主码，可使用如下语句： CREATE TABLE student

(sno NUMBER(8),

sanme VARCHAR(20),

sage NUMBER(20),

CONSTRAINT pk_sno PRIMARY KEY(sno));

其中，PRIMARY KEY（Sno）表示是Student表的主码。PK_SNO是此主码约束名。

若要在SC表中定义（Sno，Cno）为主码，则用下面语句建立SC表： CREATE TABLE sc

(sno NUMBER(8),

cno NUMBER(2),

grade NUMBER(2),

CONSTRAINT pk_sc PRIMARY KEY(sno,cno));

用PRIMARY KEY语句定义了关系的主码后，每当用户程序对主码列进行更新操作时，系统自动进行完整性检查，凡操作使主码值为空或使主码值在表中不唯一，系统拒绝此操作，从而保证了实体完整性。

二、Oracle中的参照完整性

Oracle的CREATE TABLE语句也可以定义参照完整性规则，即用FOREIGN KEY子句定义哪些列为外码列，用REFERENCES子句指明这些外码相应于哪个表的主码，用ON DELETE CASCADE子句指明在删除被参照关系的元组时，同时删除参照关系中相应的远祖。

例如，使用如下SQL语句建立EMP表： CREATE TABLE emp

(empno NUMBER(4),

ename VARCHAR(10),

job VARCHAR2(9),

mgr NUMBER(4),

sal NUMBER(7,2),

deptno NUMBER(2),

CONSTRAINT fk_deptno

FOREIGN KEY(deptno)

REFERENCES dept(deptno));

则表明EMP是参照表，DEPT为其被参照表，EMP表中Deptno为外码，它相应于DEPT表中的主码Deptno。当删除或修改DEPT表中某个元组的主码时要检查EMP中是否有元组的DEPTNO值等于DEPT中要删除的元组的Deptno值，如没有，接受此操作；否则系统拒绝这一更新操作。

如果用如下SQL语句建立EMP表： CREATE TABLE emp

(empno NUMBER(4),

ename VARCHAR(10),

job VARCHAR2(9),

mgr NUMBER(4),

sal NUMBER(7,2),

deptno NUMBER(2),

CONSTRAINT fk_deptno

FOREIGN KEY(deptno)

REFERENCES dept(deptno)

ON DELETE CASCADE);

当要修改DEPT表中的Deptno值时，先要检查EMP表中有无元组的Deptno值与之对应，若没有，系统接受这个修改操作，否则，系统拒绝此操作。

当要删除DEPT表中某个元组时，系统也要检查EMP表，若找到相应元组则将它们也随之删除。

三、Oracle中用户定义的完整性

除实体完整性和参照完整性外，应用系统中往往还需要定义与应用有关的完整性限制。例如：要求某一列的值不能取空值，要在表中是唯一的，要在某个取值范围中等。Oracle允许用户在建表时定义下列完整性约束：

列值非空（NOT NULL短语）

列值唯一（UNIQUE短语）

检查列值是否满足一个布尔表达式（CHECK短语）

例1 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码。 CREATE TABLE dept

(deptno NUMBER,

dname VARCHAR(9) CONSTRAINT u1 UNIQUE,

loc VARCHAR(10),

CONSTRAINT pk_dept PRIMARY KEY (deptno));

其中CONSTRAINT U1 UNIQUE表示约束名为U1，该约束要求Dname列值唯一。

例2 建立学生登记表Student，要求学号在90000~99999之间，年龄<29，性别只能是“男”或“女”，姓名非空。 CREATE TABLE student

(sno NUMBER(5) CONSTRAINT c1 CHECK (sno BETWEEN 90000 AND 99999),

sname VARCHAR(20) CONSTRAINT c2 NOT NULL,

sage NUMBER(3) CONSTRAINT c3 CHECK (sage<29),

ssex VARCHAR(2) CONSTRAINT c4 CHECK(ssex IN('男','女')));

例3 建立职工表EMP，要求每个职工的应发工资不得超过3000元。应发工资实际上就是实发工资列Sal与扣除项Deduct之和。 CREATE TABLE emp

(eno NUMBER(4),

ename VARCHAR(10),

job VARCHAR(8),

sal NUMBER(7,2),

deduct NUMBER(7,2),

deptno NUMBER(2),

CONSTRAINT c1 CHECK (sal + deduct <= 3000));

在Oracle中，除列值非空、列值唯一、检查列值是否满足一个布尔表达式外，用户还可以通过触发器（Trigger）来实现其他完整性规则。所谓数据库触发器，就是一类靠事件驱动的特殊过程，一旦由某个用户定义，任何用户对该数据的增、删、改操作均由服务器自动激活相应的触发器，在核心层进行集中的完整性控制。

定义数据库触发器的语句是CREATE TRIGGER。

例4 为教师表Teacher定义完整性规则“教授的工资不得低于1000元，如果低于1000元，自动改为1000元”。 CREATE TRIGGER update_sal

BEFORE INSERT OR UPDATE OF sal, pos

ON teacher

FOR EACH ROW

WHEN (:NEW.pos = '教授') /**//* 某教员晋升为教授 */

BEGIN

IF :NEW.sal < 1000

THEN

:NEW.sal := 1000;

END IF;

END;

综上所述，Oracle提供了CREATE TABLE语句CREATE TRIGGER语句定义完整性约束条件，其中用CREATE TABLE语句可以定义很复杂的完整性约束条件。完整性约束条件一旦定义好，Oracle会自动执行相应的完整性检查，对于违反完整性约束条件的操作或者拒绝执行或者执行事先定义的操作。