转 ORACLE约束总结
https://www.cnblogs.com/kerrycode/archive/2012/05/13/2454614.html
你对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 10 数据库的约束类型如下：
在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/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字符。所以有时候需要缩写表面或是采用其它规则。
约束操作
约束管理
创建各类约束
先看一下如何创建主键约束
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 '部门所在地方';
此时它的约束名称是系统创建的。如图所示：
如果我们以下面的脚本创建，此时约束如下所示
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
如果一个表的约束类型比较多，你会看到一堆SYS_CN这样的约束，如果不仔细核对后面的字段，很难一眼看出约束类型、约束对应字段。
主键约束的限制
文档原文如下： (翻译在下)
Restrictions on Primary Key Constraints Primary constraints are subject to the following restrictions:
A table or view can have only one primary key.
一个表或视图有且只有一个主键
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.
创建一个继承层次结构中的子视图时，你不能指定一个主键。主键可以唯一指定的顶层（根）视图。
###sample 1 查看用户下的所有索引，
###
Script: To Report Table Constraints for a User ( To BottomTo Bottom
Abstract
This script will report constraints for a particular user.
Product Name, Product Version
Oracle Server, 7.3.4 to 9.2.0
Platform Platform Independent
Date Created 11-Jul-1996
Description
The following script will report all table constraints of the current user's
schema.
References
Script
SET ECHO off
REM NAME: TFSUSCONS.SQL
REM USAGE:"@path/tfsuscons"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM N/A -- Reports only constraints owned by user
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Script to see what constraints are defined on your own tables.
REM ------------------------------------------------------------------------
REM Main text of script follows:
ttitle center 'User Constraints' skip 2
column ctabname format a10 heading 'Table Name' trunc
column cname format a15 heading 'Constraint Name' trunc
column refcol format a25 heading 'Search Condition' trunc
column ctype format a1;
column cdecode format a10 heading 'Type' trunc
column on_col format a10 heading 'On column' trunc
column cstatus format a4 heading 'Status' trunc
column r_tabname format a4
column r_colname format a6
break on ctabname
set feed off
set pause on
set pause 'Return...'
undefine naam
select uc1.table_name ctabname,
uc1.constraint_name cname,
ucc1.column_name on_col,
decode(constraint_type,'C','Check',
'P','Prim Key',
'U','Unique',
'R','Refer.',
'V','With Chck Opt') cdecode,
status cstatus
from dba_constraints uc1, dba_cons_columns ucc1
where uc1.constraint_name = ucc1.constraint_name(+)
and uc1.owner=''
and uc1.table_name=''
and uc1.owner = ucc1.owner
order by uc1.table_name
/
clear columns
clear breaks
column empno format 9999
ttitle off
set feed on
==============
Sample Output:
==============
User Constraints
Table Name Constraint Name On column Type Stat
---------- --------------- ---------- ---------- ----
CEG1 UN_NAME NAME Unique ENAB
CR_FILE_DA SYS_C001464 FILE_ID Check ENAB
SYS_C001465 FILE_CONTE Check ENAB
CUSTOMER CUSTID_ZERO CUSTID Check DISA
SYS_C001795 REPID Check ENAB
SYS_C001794 CUSTID Check ENAB
CUSTOMER_PRIMAR CUSTID Prim Key DISA
CUSTOMERS SYS_C001815 ID Check ENAB
SYS_C001817 LAST_NAME Check ENAB
Return...
User Constraints
Table Name Constraint Name On column Type Stat
---------- --------------- ---------- ---------- ----
CUSTOMERS SYS_C001819 COMM_PAID_ Check ENAB
SYS_C001818 FAX_NUMBER Check ENAB
SYS_C001820 ACCOUNT_BA Check ENAB
SYS_C001816 FIRST_NAME Check ENAB
DEPT DEPT_PRIMARY_KE DEPTNO Prim Key DISA
SYS_C001787 DEPTNO Check ENAB
EMP EMP_FOREIGN_KEY DEPTNO Refer. DISA
EMP_PRIMARY_KEY EMPNO Prim Key DISA
SYS_C001789 EMPNO Check ENAB
也可以查看某个表的所有约束
#####11g 检查到 3个约束同样的表，check 3 constaint_name in table_name ACT_FORM_CONFIG
ID_ 字段建立了2个非空约束。
ttitle center 'User Constraints' skip 2 column ctabname format a10 heading 'Table Name' trunc
column cname format a15 heading 'Constraint Name' trunc
column refcol format a25 heading 'Search Condition' trunc
column ctype format a1;
column cdecode format a10 heading 'Type' trunc
column ON_COLUMN format a10 heading 'On column' trunc
column cstatus format a4 heading 'Status' trunc
column r_tabname format a4
column r_colname format a6
break on ctabname
SQL> select uc1.table_name ctabname,
2 uc1.constraint_name cname,
3 ucc1.column_name on_col,
4 decode(constraint_type,'C','Check',
5 'P','Prim Key',
6 'U','Unique',
7 'R','Refer.',
8 'V','With Chck Opt') cdecode,
9 status cstatus
10 from dba_constraints uc1, dba_cons_columns ucc1
11 where uc1.constraint_name = ucc1.constraint_name(+)
12 and uc1.owner='PCONYWDATA'
13 and uc1.table_name='ACT_FORM_CONFIG'
14 and uc1.owner = ucc1.owner
15 order by uc1.table_name
16 /
CTABNAME CNAME ON_COL CDECODE CSTA
---------- --------------- ---------- ---------- ----
ACT_FORM_C SYS_C004153 ID_ Check ENAB
ONFIG LED
ACT_FORM_C SYS_C004233 ID_ Check ENAB
ONFIG LED
ACT_FORM_C SYS_C004234 ID_ Prim Key ENAB
ONFIG LED
SQL>
