你对ORACLE约束的了解如何?比较模糊还是相当透彻?如果你对下面几个问题了如指掌的话,恭喜你,你已经对约束掌握得比较好了,不用看这篇文章了。ORACLE的约束有啥功能作用? 有哪些类型约束(不同版本ORACLE是否不同)?视图有约束吗?约束是否会影响SQL性能? 约束信息存储在哪些系统视图、数据字典中?约束能否修改名称?能否禁用约束?延迟约束有啥好处.......


约束定义

约束是强加在表上的规则或条件。确保数据库满足业务规则。保证数据的完整性。当对表进行DML或DDL操作时,如果此操作会造成表中的数据违反约束条件或规则的话,系统就会拒绝执行这个操作。约束可以是列一级别的 也可以是表级别的。定义约束时没有给出约束的名字,ORACE系统将为该约束自动生成一个名字,其格式为SYS_Cn,其中n为自然数(强烈建议各位在创建表或增加约束时,给约束定义名称。).


约束功能


约束的功能:实现一些业务规则,防止无效的垃圾数据进入数据库,维护数据库的完整性(完整性指正确性与一致性)。从而使数据库的开发和维护都更加容易。


约束分类



约束分为6类: 非空(NOT NULL)约束、 唯一(UNIQUE)约束、主键(PRIMARY KEY)约束、外键(FOREIGN KEY)约束、条件(CHECK)约束、REF约束。


非空(NOT NULL)约束:顾名思义,所约束的列不能为NULL值。否则就会报错

唯一(UNIQUE)约束:在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违法约束条件。

主键(PRIMARY KEY)约束:唯一的标识表中的每一行,不能重复,不能为空。 创建主键或唯一约束后,ORACLE会自动创建一个与约束同名的索引(UNIQUENES为UNIQUE唯一索引)。需要注意的是:每个表只能有且有一个主键约束。

外键(FOREIGN KEY)约束:用来维护从表(Child Table)和主表(Parent Table)之间的引用完整性. 外键约束是个有争议性的约束,它一方面能够维护数据库的数据一致性,数据的完整性。防止错误的垃圾数据入库; 另外一方面它会增加表插入、更新等SQL性能的额外开销,不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中,就推荐禁用外键约束。

条件(CHECK)约束:表中每行都要满足该约束条件。条件约束既可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件约束。

REF约束的定义:REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.


ORACLE 11 数据库的约束类型如下:

转 ORACLE约束总结_oracle

ORACLE 10 数据库的约束类型如下:

转 ORACLE约束总结_数据库_02

在oracle 官方文档http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm下

C      (check constraint on a table)

P      (primary key)

U      (unique key)

R      (referential integrity)

V      (with check option, on a view)

O      (with read only, on a view)

http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm">​http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm​

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1044.htm#REFRN20047">​http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1044.htm#REFRN20047​

C      Check constraint on a table

P      Primary key

U      Unique key

R      Referential integrity

V      With check option, on a view

O      With read only, on a view

H      Hash expression

F      Constraint that involves a REF column

S      Supplemental logging

对于H、S类型,尚不清楚情况,而且从上面我在11g数据库实验结果看,也没发现这两种类型。(有了解的朋友可以告知一二)

约束命名规范


约束名称建议自己定义一套命名规则,否则使用系统生成的约束名,很难能把它和对应的表、字段联系起来。


    非空约束     NN_表名_列名 

    唯一约束     UK_表名_列名

    主键约束     PK_表名

    外键约束     FK_表名_列名

    条件约束     CK_表名_列名

    默认约束     DF_表名_列名

如果约束名称超过32位长度,建议应该缩写表名,而不应用NN_表名_数字。不过具体视情况而定,很多时候 DF_表名_列名 这样命名,往往超出了32字符。所以有时候需要缩写表面或是采用其它规则。


约束操作

  约束管理


   创建各类约束

先看一下如何创建主键约束

转 ORACLE约束总结_数据库_03

CREATE TABLE "DM"."DEPT"  (                    "DEPTNO"     NUMBER(2,0) PRIMARY KEY,                 "DNAME"      VARCHAR2(14),                 "LOC"        VARCHAR2(13)  )   TABLESPACE "TBS_DM_DAT" ;    COMMENT ON TABLE "DM"."DEPT" IS '部门表';                                                                                   COMMENT ON COLUMN "DM"."DEPT"."DEPTNO"        IS  '部门编号';                                                                   COMMENT ON COLUMN "DM"."DEPT"."DNAME"         IS  '部门名称';                                                                   COMMENT ON CLOUMN "DM"."DEPT"."LOC"           IS  '部门所在地方';

转 ORACLE约束总结_数据库_03

此时它的约束名称是系统创建的。如图所示:


转 ORACLE约束总结_数据库_05

如果我们以下面的脚本创建,此时约束如下所示


转 ORACLE约束总结_数据库_03

1 CREATE TABLE "DM"."DEPT"   2 (      3                "DEPTNO"           NUMBER(2,0)    ,   4                "DNAME"           VARCHAR2(14)    ,   5                "LOC"              VARCHAR2(13)    ,   6                CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX TABLESPACE TBS_DM_IN  7 )   TABLESPACE "TBS_DM_DAT" ;  8    9  10    11    12 COMMENT ON TABLE "DM"."DEPT" IS '部门表'; 13  14 COMMENT ON COLUMN "DM"."DEPT"."DEPTNO"          IS  '部门编号'; 15  16 COMMENT ON COLUMN "DM"."DEPT"."DNAME"            IS  '部门名称'; 17  18 COMMENT ON CLOUMN "DM"."DEPT"."LOC"              IS  '部门所在地方'; 19

转 ORACLE约束总结_数据库_03




转 ORACLE约束总结_数据库_08


如果一个表的约束类型比较多,你会看到一堆SYS_CN这样的约束,如果不仔细核对后面的字段,很难一眼看出约束类型、约束对应字段。

主键约束的限制


  一个表或视图有且只有一个主键

  None of the columns in the primary key can be LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.

  主键字段不能为LOB、LOG、LOG RAW、VARRAY、NESTED TABLE、BFILE、REF、TIMESTAMP WITH TIME ZONE或用户定义类型。然而它可以包含TIMESTAMP WITH LOCAL TIME ZONE类型的字段。

  The size of the primary key cannot exceed approximately one database block.

  主键大小不能超过一个数据块大小。

  A composite primary key cannot have more than 32 columns.

  主键组合键不能超过32列。

  You cannot designate the same column or combination of columns as both a primary key and a unique key.

  你不能指定一列或组合列既是主键又是唯一键。

  You cannot specify a primary key when creating a subview in an inheritance hierarchy. The primary key can be specified only for the top-level (root) view.

  创建一个继承层次结构中的子视图时,你不能指定一个主键。主键可以唯一指定的顶层(根)视图。