13.8 为什么要引入约束及如何定义约束
引入约束的目的就是防止那些无效或有问题的数据输入到表中，使用数据库的术语就是维护数据的一致性。约束是强加在表上的规则或条件。当你对该表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，ORACLE系统就会拒绝执行这个操作。这样作的好处是当错误刚一出现时就能被ORACLE系统自动地发现，从而使数据库的开发和维护都更加容易。
ORACLE系统一共提供了以下五种约束：
? 非空（NOT NULL）约束 – 所定义的列决不能为空。
? 唯一（UNIQUE）约束– 在表中每一行中所定义的这列或这些列的值都不能相同。
? 主键（PRIMARY KEY）约束 - 唯一地标示表中的每一行。
? 外键（FOREIGN KEY）约束 – 用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性（Referential Integrity）。
? 条件（CHECK）约束 – 表中每行都要满足该约束条件。
约束是加在表上的，因为只有在表中存有数据。你可以在创建表时在CREATE TABLE 语句中定义约束，也可以在已存在的表上利用ALTER TABLE 语句中定义约束。你即可以在列一级，也可以在表一级定义约束。约束的定义存在ORACLE的数据字典中，你只能通过数据字典来浏览约束。你可以给出约束的名字。如果你在定义约束时没有给出约束的名字，ORACLE系统将为该约束自动生成一个名字。其格式为：SYS_Cn，其中n为自然数。
13.9 非空（NOT NULL）约束
一般在一个公司或机构中，所有的部门都应该有一个部门名。也就是说如果公司或机构要成立一个部门，就必需给出这个部门名，即部门名不能为空。现在你可以通过使用如下的CREATE TABLE 语句（如例13-41）来定义非空（NOT NULL）约束，以这种方法来实现上面所说的商业规则。
例13-41
SQL> CREATE TABLE deptcon(
2 deptno NUMBER(3),
3 dname VARCHAR2(15) NOT NULL,
4 loc VARCHAR2(20));
例13-41结果
表已创建。
现在你可以使用如下的SQL*PLUS命令（如例13-42）来检查一下你是否已成功地在deptcon表的dname列上定义了非空（NOT NULL）约束。
例13-42
SQL> DESC deptcon
例13-42结果
名称 是否为空? 类型
----------------------------------------- -------- ------------
DEPTNO NUMBER(3)
DNAME NOT NULL VARCHAR2(15)
LOC VARCHAR2(20)
例13-42的显示结果表明你已成功地在deptcon表的dname列上定义了非空（NOT NULL）约束。
在ORACLE系统提供的五种约束当中，非空（NOT NULL）约束是唯一的一种只能在列一级定义的约束。现在你可已通过在deptcon表上的DML操作来进一步理解非空（NOT NULL）约束的含意。首先你使用如下的DML 语句（如例13-43）往deptcon表中插入一行正常的数据。
例13-43
SQL> INSERT INTO deptcon(deptno, dname, loc)
2 VALUES (10, 'ACCOUNTING', 'BEIJING');
例13-43结果
已创建 1 行。
例13-43的显示结果表明你已经把一行数据成功地插入到deptcon表中，因为该行数据中部门名不为空它满足在此列上所定义的约束。
现在你再使用如下的DML 语句（如例13-44）往deptcon表中插入一行非正常（部门名为空）的数据。
例13-44
SQL> INSERT INTO deptcon(deptno, dname, loc)
2 VALUES (20, '' , 'GUANGZGOU');
例13-44结果
INSERT INTO deptcon(deptno, dname, loc)
*
ERROR 位于第 1 行:
例13-44的显示结果表明ORACLE系统拒绝执行你的插入操作，因为该行数据中部门名为空它不满足在此列上所定义的约束。你如果将该行数据中部门名改为UNKNOWN或任何非空的字符串，ORACLE系统就会执行你的插入操作如（例13-45）。
例13-45
SQL> INSERT INTO deptcon(deptno, dname, loc)
2 VALUES (20, 'UNKNOWN', 'GUANGZGOU');
例13-45结果
已创建 1 行。
现在你可以使用如下的查寻 语句（如例13-46）来看一下deptcon中的内容。
例13-46
SQL> SELECT *
2 FROM deptcon;
例13-46结果
DEPTNO DNAME LOC
---------- --------------- ---------
10 ACCOUNTING BEIJING
20 UNKNOWN GUANGZGOU
测试完了INSERT语句你该测试UPDATE语句了。你可以使用如下的DML 语句（如例13-47）试着将第20号部门的名字改为空。
例13-47
SQL> UPDATE deptcon
2 SET dname = NULL
3 WHERE deptno = 20;
例13-47结果
UPDATE deptcon
*
ERROR 位于第 1 行:
ORA-01407: 无法更新 ("SCOTT"."DEPTCON"."DNAME"
为 NULL
例13-47的显示结果表明ORACLE系统拒绝执行你的修改操作，因为在该行数据中修改后的部门名为空它不满足在此列上所定义的约束。
如果现在你使用与例13-47相似的UPDATE语句（如例13-48）将第20号部门的名字改为ACCOUNTING，ORACLE系统又该如何处理呢？
例13-48
SQL> UPDATE deptcon
2 SET dname = 'ACCOUNTING'
3 WHERE deptno = 20;
例13-48结果
已更新 1 行。
例13-48的显示结果表明你已经成功地把deptcon表中的第20号部门的名字改为ACCOUNTING，因为该行数据中部门名不为空它满足在此列上所定义的约束。
现在你可以使用如下的查寻 语句（如例13-49）来看一下deptcon中的内容。
例13-49
SQL> SELECT *
2 FROM deptcon;
例13-49结果
DEPTNO DNAME LOC
---------- --------------- ---------
10 ACCOUNTING BEIJING
20 ACCOUNTING GUANGZGOU
例13-49的显示结果表明，在deptcon表中有两个叫ACCOUNTING的部门。在实际的商业运作中这几乎是不可能的，但ORACLE系统不管因为这并未违反你在DNAME列上定义的非空约束。你如果不想让deptcon表中的部门重复，你就得再加上唯一（UNIQUE约束）。所谓“铁路警察各管一段。”
你不用测试DELETE语句，因为该语句不会产生数据违反非空（NOT NULL）约束的情形。
13.10 查看有关约束的信息
对于非空（NOT NULL）约束，你有时可以使用SQL*PLUS的DESC命令来得到一些有关这种约束的信息。但如果你想知道约束名字，或者你想查看的约束不是非空（NOT NULL）约束，你又该怎么办呢？
你可以使用数据字典USER_CONSTRAINTS来得到这些信息。
为什么要使用以下的SQL*PLUS命令（例13-50和例13-51）现在你应该比较清楚了，在这里就不再解释了。
例13-50
SQL> COL owner FOR A10
例13-51
SQL> COL table_name FOR A10
之后你可以使用如下的查寻 语句（如例13-52）来看一下SCOTT用户所创建的所有的约束。
例13-52
SQL> SELECT owner, constraint_name, constraint_type, table_name
2 FROM user_constraints;
例13-52结果
OWNER CONSTRAINT_NAME C TABLE_NAME
---------- ------------------------------ - ----------
SCOTT PK_DEPT P DEPT
SCOTT SYS_C002719 C DEPTCON
SCOTT PK_EMP P EMP
SCOTT FK_DEPTNO R EMP
SCOTT SYS_C002718 P E_M_SHELL
SCOTT SYS_C002715 C MANAGER
SCOTT SYS_C002716 C MANAGER
SCOTT SYS_C002717 C MANAGER
已选择8行。
例13-52的显示结果表明：在deptcon表上定义了一个约束，约束的类型为C，它的名字是ORACLE系统自动生成的，为SYS_C002719。
现在我们来解释例13-52的显示结果中第三列C（constraint_type）中每个字母所代表的含意：
2 C 代表CHECK（条件约束）和NOT NULL（非空约束）。
2 P 代表PRIMARY KEY（主键约束）
2 R 代表REFERENTIAL INTEGRITY，即FOREIGN KEY（外键约束）
2 U 代表UNIQUE（唯一约束）
如果你想知道约束是定义在哪一个表的哪一列上的话，你又该怎么办呢？你可以使用数据字典USER_CONS_COLUMNS来得到这些信息。现在你可以使用如例13-54的查寻 语句来看一下SCOTT用户所创建的所有约束的有关信息。
例13-53
SQL> COL column_name for a15
例13-54
SQL> SELECT owner, constraint_name, table_name, column_name
2 FROM user_cons_columns;
例13-54结果
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
---------- ------------------------------ ---------- -----------
SCOTT FK_DEPTNO EMP DEPTNO
SCOTT PK_DEPT DEPT DEPTNO
SCOTT PK_EMP EMP EMPNO
SCOTT SYS_C002715 MANAGER EMPNO
SCOTT SYS_C002716 MANAGER ENAME
SCOTT SYS_C002717 MANAGER HIREDATE
SCOTT SYS_C002718 E_M_SHELL E_ID
SCOTT SYS_C002719 DEPTCON DNAME
已选择8行。
例13-54的显示结果表明：在deptcon表上定义了一个约束，该约束的是定义在DNAME上的，它的名字是ORACLE系统自动生成的，为SYS_C002719。
13.11 唯一（UNIQUE）约束
如果你的公司要求所有的部门的名字都不能相同，那你该怎么办呢？你可以利用在DNAME上定义唯一（UNIQUE）约束来实现公司的这一商业规则。
为了操作方便，你可以先使用下面的DDL语句（例13-55）将加在deptcon表的DNAME上的NOT NULL（非空约束）删除掉。
例13-55
SQL> ALTER TABLE deptcon
2 DROP CONSTRAINT SYS_C002719;
例13-55结果
表已更改。
现在你应该使用与例13-54的查寻 语句完全一样的查寻 语句（例13-56）来看一下你所在的用户所创建的所有约束信息。
例13-56
SQL> SELECT owner, constraint_name, table_name, column_name
2 FROM user_cons_columns;
例13-56结果
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
---------- ------------------------------ ---------- -----------
SCOTT FK_DEPTNO EMP DEPTNO
SCOTT PK_DEPT DEPT DEPTNO
SCOTT PK_EMP EMP EMPNO
SCOTT SYS_C002715 MANAGER EMPNO
SCOTT SYS_C002716 MANAGER ENAME
SCOTT SYS_C002717 MANAGER HIREDATE
SCOTT SYS_C002718 E_M_SHELL E_ID
已选择7行。
例13-56的显示结果表明你已经成功地删掉了在deptcon表上所定义的全部约束。
现在你可以试着使用如下的DDL语句（例13-57）在deptcon表的dname上添加一个唯一（UNIQUE）约束。
例13-57
SQL> ALTER TABLE deptcon
2 ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname);
例13-57结果
ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname)
*
ERROR 位于第 2 行:
ORA-02299: 无法验证 (SCOTT.DEPTCON_DNAME_UK) - 未找到重复关键字
例13-57的显示输出可能令你感到困惑。不过你用不着担心，你可以使用如例13-58的查寻 语句看看到底发生了什么？
例13-58
SQL> SELECT * FROM DEPTCON；
例13-58结果
DEPTNO DNAME LOC
---------- --------------- ---------
10 ACCOUNTING BEIJING
20 ACCOUNTING GUANGZGOU
看到例13-58的结果，你立刻明白了。这是因为在deptcon表中有两个叫ACCOUNTING的部门。毫无疑问这违反了唯一（UNIQUE）约束条件。现在可以用下面的DML语句（例13-59）删除第二行部门名为ACCOUNTING的记录。
例13-59
SQL> DELETE FROM deptcon
2 WHERE deptno = 20;
例13-59结果
已删除 1 行。
当看到例13-59的结果“已删除 1 行。”后，你可以再使用与例13-57完全相同的DDL语句（例13-60）在deptcon表的dname上添加一个唯一（UNIQUE）约束。
例13-60
SQL> ALTER TABLE deptcon
2 ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname);
例13-60结果
表已更改。
例13-60的显示结果表明你已成功地在deptcon表的dname列上定义了唯一（UNIQUE）约束。为了慎重起见，你应该使用如下的查寻 语句（例13-63）来看一下你所在的用户所创建的所有约束信息。
例13-61
SQL> COL SEARCH_CONDITION FOR A23
例13-62
SQL> COL CONSTRAINT_NAME FOR A20
例13-63
SQL> SELECT owner, constraint_name, constraint_type, table_name,
2 search_condition
3 FROM user_constraints;
例13-63结果
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
---------- -------------------- - ---------- ----------------------
SCOTT PK_DEPT P DEPT
SCOTT DEPTCON_DNAME_UK U DEPTCON
SCOTT PK_EMP P EMP
SCOTT FK_DEPTNO R EMP
SCOTT SYS_C002718 P E_M_SHELL
SCOTT SYS_C002715 C MANAGER "EMPNO" IS NOT NULL
SCOTT SYS_C002716 C MANAGER "ENAME" IS NOT NULL
SCOTT SYS_C002717 C MANAGER "HIREDATE" IS NOT NULL
已选择8行。
例13-63的显示结果表明：在deptcon表上定义了一个约束，该约束的类型为UNIQUE，它的名字是DEPTCON_ DNAME_UK。
其实在这个例子中只要看到了约束名DEPTCON_DNAME_UK，你应该已经猜到了该约束是定义在DEPTCON表的DNAME列上，并且是一个唯一（UNIQUE）约束。这种约束的命名法是ORACLE推荐的，它由三部分组成：表名，列名，和约束的类型。它们之间由下划线（_）连接。其中约束的类型的表示如下：
2 UK UNIQUE KEY（唯一）约束
2 PK PRIMARY KEY（主键）约束
2 FK FOREIGN KEY（外键）约束
2 CK CHECK（条件）约束
2 NN NOT NULL（非空）约束
例13-57已经验正了唯一（UNIQUE）约束不允许表中有两行相同的数据。那么UNIQUE KEY（唯一）约束允许插入空值（NULL）吗？我想最好的办法是用例子来说明。首先你使用如下的DML 语句（如例13-64）往deptcon表中插入一行部门名为空的数据。
例13-64
SQL> INSERT INTO deptcon(deptno, dname, loc)
2 VALUES (20, NULL, '牛街');
例13-64结果
已创建 1 行。
例13-64的显示结果表明UNIQUE KEY（唯一）约束允许插入一个空值（NULL）。现在你可能又要问UNIQUE KEY（唯一）约束允许插入多个空值（NULL）吗？为了回答这个问题，我们还是使用老办法用例子来说明。你再使用与例13-64相似的DML 语句（如例13-65）往deptcon表中插入一行部门名为空的数据。
例13-65
SQL> INSERT INTO deptcon(deptno, dname, loc)
2 VALUES (20, NULL, '狼山镇');
例13-65
已创建 1 行。
例13-65的显示结果表明UNIQUE KEY（唯一）约束允许插入第二个空值（NULL）。其实UNIQUE KEY（唯一）约束允许插入任意多个空值（NULL）。因为空值（NULL）不等于任何值，所以每个空值（NULL）都不等于任何其它的空值（NULL），即每个空值（NULL）都是唯一的。你可以重温第五章中所讲的那个世俗的故事。假设有N位白马王子向那位美丽动人的少女发下了同样的爱的誓言。谁能说出他们的誓言是相等还是不等呢？我相信没人能回答这个问题。
我们并未给出利用UPDATE语句来测试UNIQUE KEY（唯一）约束的例子，如果读者感兴趣可自己试着作一下。
与非空（NOT NULL）约束一样，你不用测试DELETE语句，因为该语句不会产生数据违反UNIQUE KEY（唯一）约束的情形。
13.12 条件（CHECK）约束
你可以利用条件（CHECK）约束来实现公司中一些比较复杂的商业规则。条件（CHECK）约束定义了表中每一行数据都必需满足的条件。条件（CHECK）约束中的条件与查寻语句中的条件相同，但是不能包括以下的内容：
? CURRVAL，NEXTVAL，LEVEL，和ROWNUM这样的伪列（PSEUDOCOLUMNS）。
? 引用其它行中的值的查寻语句。
? SYSDATE，USER，USERENV，和UID的函数调用。
条件（CHECK）约束即可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件（CHECK）约束。
为了使读者容易理解，还是老办法用例子来说明。如果你留意过招工广告的话，你可能有印象一般招前台或文秘的招工广告都要求应征者要满足以下条件：
2 女性
2 年龄在18到35岁之间
2 最好大学或以上学历
2 相貌端庄
2 未婚
2 等等
你可以使用如下的DDL 语句（如例13-66）来实现上述的第一和第二个条件（有些条件很难在计算机上实现，如相貌端庄）。
例13-66
SQL> CREATE TABLE person(
2 id VARCHAR2(10),
3 name VARCHAR2(20),
4 gender CHAR(1),
5 age NUMBER,
6 CONSTRAINT person_gender_ck
7 CHECK(gender = 'F'),
8 CONSTRAINT person_age_ck
9 CHECK(age BETWEEN 18 AND 35));
例13-66结果
表已创建。
虽然你看到了例13-66结果“表已创建。”，但做为一名老练的ORACLE数据库管理员（DBA），你还是应该使用如下的SQL*PLUS命令（例13-67）来检查一下你是否已经成功地创建了表person。
例13-67
SQL> DESC person
例13-67结果
名称 是否为空? 类型
----------------------------------------------------- -------- -------------
ID VARCHAR2(10)
NAME VARCHAR2(20)
GENDER CHAR(1)
AGE NUMBER
当看到了例13-66结果之后，你的心里一定踏实多了。接下去，你还应该使用如下的查寻语句（例13-72）来检查一下你是否已经成功在person表上定义了你所需要的条件（CHECK）约束。例13-68到例13-71的作用你应该比较清楚了，这里就不再解释了。
例13-68
SQL> COL owner FOR A8
例13-69
SQL> COL CONSTRAINT_NAME FOR A20
例13-70
SQL> COL TABLE_NAME FOR A10
例13-71
SQL> COL SEARCH_CONDITION FOR A25
例13-72
SQL> SELECT owner, constraint_name, constraint_type, table_name,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'PERSON';
例13-72结果
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
-------- -------------------- - ---------- ---------------------
SCOTT PERSON_GENDER_CK C PERSON gender = 'F'
SCOTT PERSON_AGE_CK C PERSON age BETWEEN 18 AND 35
例13-72的显示结果表明：在SCOTT 用户的PERSON表上定义了两个约束。第一个约束的名字是PERSON_GENDER_CK，类型为CHECK，约束条件为gender = 'F'。第二个约束的名字是PERSON_AGE_CK，类型也为CHECK，约束条件为age BETWEEN 18 AND 35。其实在这个例子中只要看到了约束名，除了约束条件之外，你应该已经猜出了这两个约束的其它信息。
现在你可以通过往person表中插入数据来测试你刚刚创建的条件（CHECK）约束。你可以使用如下的DML 语句（如例13-73）试着往person表中插入一行正常的数据（满足约束条件的数据）。
例13-73
SQL> INSERT INTO person(id, name, gender, age)
2 VALUES (1001, '白小丫','F', 22);
例13-73结果
已创建 1 行。
例13-73的显示结果表明你已经成功把这行数据插入到person表中，这是因为白小丫是一位年仅20出头的妙龄女郎所以满足公司的招工要求（即满足约束条件）。
现在你可以使用如下的DML 语句（如例13-74）试着往person表中插入一行非正常的数据（不满足约束条件的数据）。
例13-74
SQL> INSERT INTO person(id, name, gender, age)
2 VALUES (1002, '王老五', 'M', 19);
例13-74结果
INSERT INTO person(id, name, gender, age)
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SCOTT.PERSON_GENDER_CK)
例13-74的显示结果表明ORACLE系统拒绝执行你所发的INSERT语句。当你看完了ORACLE系统显示的出错提示之后，你重新审查了一下你的INSERT语句，终于恍然大悟。虽然王老五年龄合适，但他是一位男士。这当然不符合公司的招工要求（即不满足约束条件）。
现在你再使用如下的INSERT 语句（如例13-75）试着往person表中插入另一行非正常的数据（不满足约束条件的数据）。看看ORACLE系统会有什么反应。
例13-75
SQL> INSERT INTO person(id, name, gender, age)
2 VALUES (1001, '李媛媛', 'F', 36);
例13-75结果
INSERT INTO person(id, name, gender, age)
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SCOTT.PERSON_AGE_CK)
例13-75的显示结果表明ORACLE系统又一次拒绝执行你所发的INSERT语句。虽然李媛媛是一位相貌端庄的女士，但按公司的标准已是年龄超过35岁的老姑婆。这当然不符合公司的招工要求（即不满足约束条件）。
现在你再使用如下的INSERT 语句（如例13-76）试着往person表中插入另一行非正常的数据（不满足约束条件的数据）。看看ORACLE系统会有什么反应。
例13-76
SQL> INSERT INTO person(id, name, gender, age)
2 VALUES (1001, '赵莺莺', 'F', 17);
例13-76结果
INSERT INTO person(id, name, gender, age)
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SCOTT.PERSON_AGE_CK)
例13-76的显示结果表明ORACLE系统又一次拒绝执行你所发的INSERT语句。虽然赵莺莺是一位相貌端庄的妙龄女郎，但是一位年龄不足18的未成年少女。雇佣未成年少女是违法的，这当然不符合公司的招工要求（即不满足约束条件），因为公司不想因此而吃官司。
我们并未给出利用UPDATE语句来测试条件（CHECK）约束的例子，如果读者感兴趣可自己试着作一下。
与非空（NOT NULL）和唯一（UNIQUE KEY）约束一样，你不用测试DELETE语句，因为该语句不会产生数据违反条件（CHECK）约束的情形。
摘自：《从实践中学习Oracle/SQL》，清华大学出版社出版。
[此贴子已经被作者于2010-12-11 19:58:17编辑过]
加好友 
发短信
管理员
Post By：2008/10/24 22:06:55 [只看该作者]
