数据库不仅仅是存储数据,它还必须保证所有存储数据的正确性,因为只有正确的数据才能提供有价值的信息。如果数据不准确或不一致,那么该数据的完整性就可能受到破坏,从而给数据库本身的可靠性带来问题。为了维护数据库中数据的完整性,在创建表时常常需要定义一些约束。约束可以限制列的取值范围,强制列的取值来自合理的范围。在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) --年龄,并设置检查约束
);