Oracle数据完整性和约束性

数据库不仅仅是存储数据，它还必须保证所有存储数据的正确性，因为只有正确的数据才能提供有价值的信息。如果数据不准确或不一致，那么该数据的完整性就可能受到破坏，从而给数据库本身的可靠性带来问题。为了维护数据库中数据的完整性，在创建表时常常需要定义一些约束。约束可以限制列的取值范围，强制列的取值来自合理的范围。在Oracle 11g系统中，约束的类型包括非空约束、主键约束、唯一约束、外键约束、检查约束和默认约束。

创建BOOKS（图书）表，用于实例的操作使用。

CREATE TABLE BOOKS
(
  BOOKNO NUMBER(4) NOT NULL,           --图书编号
  BOOKNAME VARCHAR2(20),               --图书名称
  AUTHOR VARCHAR2(10),                 --作者   
  SALEPRICE NUMBER(9,2),               --定价     
  PUBLISHERNO VARCHAR2(4) NOT NULL,    --出版社编号
  PUBLISHDATE DATE,                    --出版日期
  ISBN VARCHAR2(20) NOT NULL          --ISBN
);

1、非空约束

非空约约束就是限制必须为某个列提供值。空值（NULL）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。

【实例】为BOOKS（图书）表中的BOOKNAME字段设置非空约束。

ALTER TABLE BOOKS MODIFY BOOKNAME NOT NULL;

【实例】删除BOOKS（图书）表中关于BOOKNAME列的非空约束。

ALTER TABLE BOOKS MODIFY BOOKNAME NULL;

2、主键约束

主键约束用于唯一地标识表中的每一行记录。在一个表中，最多只能有一个主键约束，主键约束既可以由一个列组成，也可以由两个或两个以上的列组成（这种称为联合主键）。对于表中的每一行数据，主键约束列都是不同的，主键约束同时也具有非空约束的特性。

如果主键约束由一列组成时，该主键约束被称为行级约束。如果主键约束由两个或两个以上的列组成时，则该主键约束被称为表级约束。若要设置某个或某些列为主键约束，通常使用CONSTRAINT…PRIMARY KEY语句来定义。

【实例】使用ALTER TABLE…ADD语句为BOOKS（图书）表增加主键约束。

ALTER TABLE BOOKS ADD CONSTRAINT BOOKS_PK PRIMARY KEY(BOOKNO);

--或者
ALTER TABLE BOOKS ADD PRIMARY KEY(BOOKNO);

说明：如果构建主键约束的列有多个（即创建表级约束），则多个列之间使用英文输入法下的逗号分隔。

3、唯一性约束

唯一性（UNIQUE）约束强调所在的列不允许有相同的值。但是，它的定义要比主键约束弱，即它所在的列允许空值（但主键约束列是不允许为空值的）。唯一性约束的主要作用是在保证除主键列外，其它列值的唯一性。

在一个表中，根据实际情况可能有多个列的数据都不允许存在相同值。例如，各种“会员表”的QQ、Email等列的值是不允许重复的（但用户可能不提供，这样就必须允许为空值），但是由于在一个表中最多只能由一个主键约束存在，那么如何解决这种多个列都不允许重复数据存在的问题呢？这就是唯一性约束的作用。若要设置某个列为UNIQUE约束，通常使用CONSTRAINT…UNIQUE标记该列。

【实例】为BOOKS（图书）表的ISBN列添加唯一约束。

ALTER TABLE BOOKS ADD CONSTRAINT ISBN_UK UNIQUE(ISBN);

说明：如果要为现有表的多个列同时添加UNIQUE约束，则在括号内使用逗号分隔多个列。

【实例】删除BOOKS（图书）表的ISBN列的唯一约束。

ALTER TABLE BOOKS DROP CONSTRAINT ISBN_UK;

4、外键约束

外键约束比较复杂，一般的外键约束会使用两个表进行关联（当然也存在同一个表自连接的情况）。外键是指“当前表”（即外键表）引用“另外一个表”（即被引用表）的某个列或某几个列，而“另外一个表”中被引用的列必须具有主键约束或者唯一性约束。在“另外一个表”中，被引用列中不存在的数据不能出现在“当前表”对应的列中。一般情况下，当删除被引用表中的数据时，该数据也不能出现在外键表的外键列中。如果外键列存储了被引用表中将要被删除的数据，那么对被引用表的删除操作将失败。

最典型的外键约束时HR模式中的EMPLOYEES和DEPARTMENT表，在该外键约束中，外键表EMPLOYEES中的外键列DEPARTMENT_ID将引用被引用表DEPARTMENTS中的DEMPARTMENT_ID列，而该列也是DEPARTMENTS表的主键。

【实例】在HR模式中，创建一个新表EMPLOYEES_TEMP（该表的结构复制自EMPLOYEES），并为其添加一个与DEPARTMENTS表关联的外键约束。

CREATE TABLE EMPLOYEES_TEMP
AS SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;

ALTER TABLE EMPLOYEES_TEMP
ADD CONSTRAINT TEMP_DEPARTID_FK
FOREIGN KEY(DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID);

5、检查约束

检查约束用来指定某列的可取值的范围，它通过限制输入到列中的值来强制域的完整性。

【实例】为BOOKS（图书）表的SALEPRICE字段添加检查约束，定价必须大于0且小于1200。

ALTER TABLE BOOKS ADD CONSTRAINT SALEPRICE_CK CHECK(SALEPRICE>0 AND SALEPRICE<1200);

6、默认约束

【实例】为BOOKS（图书）表的PUBLISHERNO列添加默认约束。

ALTER TABLE BOOKS MODIFY PUBLISHERNO DEFAULT '1001';

7、删除约束

如果不再需要某个约束时，则可以将其删除。可以使用带DROP CONSTRAINT子句的ALTER TABLE语句删除约束。删除约束与禁用约束不同，禁用的约束时可以激活的，但是删除的约束在表中就完全消失了。使用ALTER TABLE语句删除约束的语法格式如下：

ALTER TABLE TABLE_NAME DROP CONSTRAINTCON_NAME;

TABLE_NAME：表示要删除约束的表名称。

CON_NAME：表示要删除的约束名称。

【实例】删除BOOKS（图书）表的SALEPRICE字段的检查约束。

ALTER TABLE BOOKS DROP CONSTRAINT SALEPRICE_CK;

8、创建数据表同时创建约束

【实例】创建STUDENT_INFO（学生信息）表同时创建约束。

--创建“学生信息”数据表
CREATE TABLE STUDENT_INFO
(
  STU_ID INT PRIMARY KEY,                             --学号,并设置主键约束
  STU_NAME VARCHAR2(8) NOT NULL,                      --姓名,并设置非空约束
  EMAIL VARCHAR2(20) CONSTRAINT EMAIL_UK UNIQUE,      --邮箱,并设置唯一性约束
  SEX CHAR(2) DEFAULT '女',                           --性别，并设置默认约束
  AGE INT CONSTRAINT AGE_CK CHECK(AGE>0 AND AGE<120)  --年龄，并设置检查约束
);