2.1.1.1_4 Oracle 静态数据字典之4 CONSTRAINTS 约束

---

相关链接

• 1.Excel目录
• 2.Oralce系统表 数据字典
• 3.官方文档
  • DBA_CONSTRAINTS
  • ALL_CONSTRAINTS ★✈
  • USER_CONSTRAINTS

---

1 静态数据字典

  原文
  这类数据字典主要是由表和视图组成。应该注意的是，数据字典中的表是不能直接来访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别为：DBA_、ALL_、USER_。

• DBA_CONSTRAINTS describes all constraint definitions on all tables in the database. Its columns are the same as those in “ALL_CONSTRAINTS”.
  　　　　　　　　　　　描述数据库中的所有约束定义。
  　　　　　　　　　　　表结构和"ALL_CONSTRAINTS"一致。
• ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.
  　　　　　　　　　　　描述当前用户可访问的表上的约束定义。
• USER_CONSTRAINTS describes all constraint definitions on tables owned by the current user. Its columns are the same as those in “ALL_CONSTRAINTS”.
  　　　　　　　　　　　描述当前用户schema中表上的约束定义。
  　　　　　　　　　　　表结构和"ALL_CONSTRAINTS"一致。

2 ALL_CONSTRAINTS 数据字典

• DBA_CONSTRAINTS ，ALL_CONSTRAINTS ， USER_CONSTRAINTS 表结构完全一致

约束种类
● C - Check constraint on a table　
　条件约束 > CHECK 约束用于限制列中的值的范围。
　　　　　　如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
　　　　　　如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
● P - Primary key
　主键约束 > PRIMARY KEY 约束唯一标识数据库表中的每条记录。
　　　　　　主键必须包含唯一的值。
　　　　　　主键列不能包含 NULL 值。
　　　　　　每个表都应该有一个主键，并且每个表只能有一个主键。
●U - Unique key　
　唯一约束 > UNIQUE 约束唯一标识数据库表中的每条记录。
　　　　　　UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
　　　　　　PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
　　　　　　请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
● R - Referential integrity 　
　　　外键 > 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
● V - With check option, on a view
　视图条件约束 > 如果视图定义包括条件（譬如WHERE 子句）并且其意图是确保任何引用该视图的 INSERT 或UPDATE 语句都应用 WHERE 子句，则必须使用 WITH CHECK OPTION 定义该视图。这个选项可以确保数据库中正在修改的数据的完整性。如果在 INSERT 或 UPDATE 操作期间违反了条件，则返回 SQL 错误。
● O - With read only, on a view 　只读约束- 视图
　只读约束 > 对视图只有读的权限
● H - Hash expression　？
● F - Constraint that involves a REF column 　？（ref 就是声明变量 游标作为变量处理的意思）
● S - Supplemental logging 　补全日志库/表

• oracle中所有obj类型（库，表，字段，存储过程等）限制为30字符，字段类型为VARCHAR2(30)时则可以反推字段内容属于对象。即可以在与其他静态数据字典关联查询时作为关联条件。

Ser = Serial ：表示字段序号

Ser	Column	DataType	Null	Description
1	OWNER 库名	VARCHAR2(30)	NOT NULL	Owner of the constraint defintion 约束的所有者
2	CONSTRAINT_NAME 约束名称	VARCHAR2(30)	NOT NULL	Name of the constraint definition 约束的名称
3	CONSTRAINT_TYPE 约束种类	VARCHAR2(1)		Type of the constraint definition: 　● 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 　？（ref 就是声明变量 游标作为变量处理的意思） 　● S - Supplemental logging 　补全日志库/表
4	TABLE_NAME 表名	VARCHAR2(30)	NOT NULL	Name associated with the table (or view) with the constraint definition 与约束关联的表(或视图)的名称
5	SEARCH_CONDITION 搜索条件	LONG		Text of search condition for a check constraint 条件(CHECK)约束的搜索条件
6	R_OWNER 外键约束的所有者（表）	VARCHAR2(30)		Owner of the table referred to in a referential constraint 外键(FOREIGN KEY)约束关联的表
7	R_CONSTRAINT_NAME Relation?（表）	VARCHAR2(30)		Name of the unique constraint definition for the referenced table 唯一约束所关联的表
8	DELETE_RULE 删除（外键）规则	VARCHAR2(9)		Delete rule for a referential constraint: 　● CASCADE 　父表delete、update的时候，子表会delete、update掉关联记录； 　● SET NULL 　父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null； 　● NO ACTION L 　 如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
9	STATUS （可用）状态	VARCHAR2(8)		Enforcement status of the constraint: 　● ENABLED 　可用（默认） 　● DISABLED 　不可用
10	DEFERRABLE 是否可延迟约束	VARCHAR2(14)		Indicates whether the constraint is deferrable (DEFERRABLE) or not (NOT DEFERRABLE) 约束是否可延迟 　延迟约束 　● DEFERRABLE　　　　延迟 　● NOT DEFERRABLE 　不延迟（默认）
11	DEFERRED 延迟/不延迟约束	VARCHAR2(9)		Indicates whether the constraint was initially deferred (DEFERRED) or not (IMMEDIATE) 约束最初是否被延迟 　● DEFERRABLE　延迟 　● IMMEDIATE　　立即（默认）
12	VALIDATED （是否）数据验证	VARCHAR2(13)		Indicates whether all data obeys the constraint (VALIDATED) or not (NOT VALIDATED) 所有数据是否遵守约束 　● VALIDATED　　　验证（默认） 　● NOT VALIDATED　不验证
13	GENERATED 生成的（约束名称）	VARCHAR2(14)		Indicates whether the name of the constraint is user-generated (USER NAME) or system-generated (GENERATED NAME) 约束的名称是还是 　● USER NAME　　　　用户生成的(用户名) 　● GENERATED NAME　系统生成的(生成的名称)（默认）
14	BAD	VARCHAR2(3)		Indicates whether this constraint specifies a century in an ambiguous manner (BAD) or not (NULL). To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATE function with a four-digit year. See Also: the TO_DATE function in Oracle Database SQL Language Reference and Oracle Database Advanced Application Developer’s Guide
15	RELY 依赖	VARCHAR2(4)		Indicates whether an enabled constraint is enforced (RELY) or unenforced (NULL) See Also: the constraints in Oracle Database SQL Language eference 启用的约束是强制执行(依赖)还是不强制执行(NULL)
16	LAST_CHANGE 最后变更（日期）	DATE		When the constraint was last enabled or disabled 最后一次启用或禁用约束的时间
17	INDEX_OWNER 索引的拥有者（库）	VARCHAR2(30)		Name of the user owning the index 拥有索引的用户的名称
18	INDEX_NAME 索引的名称	VARCHAR2(30)		Name of the index (only shown for unique and primary-key constraints) 索引的名称(仅针对唯一和主键约束显示)
19	INVALID （是否）无效	VARCHAR2(7)		Indicates whether the constraint is invalid (INVALID) or not (NULL) 指示约束是否无效(无效)或不(NULL)
20	VIEW_RELATED 依赖视图	VARCHAR2(14)		Indicates whether the constraint depends on a view (DEPEND ON VIEW) or not (NULL) 约束是否依赖于视图(依赖于视图)(NULL)

2.1 Constraint_type值含义（第3列）

Type Code 参 数 值	Type Description 参 数 含 义	Acts On Level 作 用 范 围
C	Check on a table	Column
P	Primary Key	Object
U	Unique Key	Column
R	Referential integrity = Foreign Key	Column
V	Check Option, on a view	Object
O	Read Only, on a view	Object
H	Hash expression
F	Constraint that involves a REF column
S	Supplemental logging

2.2 适用关联条件

Serial	Column	DataType	Null	Description	关联表 编号与原文一致
1	OWNER 库名	VARCHAR2(30)	NOT NULL	Owner of the constraint defintion 约束的所有者	所有ALL_开头的静态字典表
2	CONSTRAINT_NAME 约束名称	VARCHAR2(30)	NOT NULL	Name of the constraint definition 约束的名称	3,4
4	TABLE_NAME 表名	VARCHAR2(30)	NOT NULL	Name associated with the table (or view) with the constraint definition 与约束关联的表(或视图)的名称	待确认
6	R_OWNER 外键约束的表	VARCHAR2(30)		Owner of the table referred to in a referential constraint 外键(FOREIGN KEY)约束关联的表	待确认
7	R_CONSTRAINT_NAME Relation?（表）	VARCHAR2(30)		Name of the unique constraint definition for the referenced table 唯一约束所关联的表	待确认
17	INDEX_OWNER 索引的拥有者（库）	VARCHAR2(30)		Name of the user owning the index 拥有索引的用户的名称	所有ALL_开头的静态字典表
18	INDEX_NAME 索引的名称	VARCHAR2(30)		Name of the index (only shown for unique and primary-key constraints) 索引的名称(仅针对唯一和主键约束显示)	6,7,10? 待确认

2.3 约束

• 约束定义
    约束用于限制加入表的数据的类型。
    可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

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

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

• 约束的局限性
  ┄┄┄过多的约束检查会大大降低数据库运行效率，在数据仓库中，就推荐禁用一些约束，如非空，外键等

• 约束状态 STATUS
  　　很多时候由于业务需要，比如我们有大量的历史数据，需要和现有数据合并，当前表存在数据库约束（如非空约束），而这些历史数据又包含违背非空约束的数据行，为了避免导入时由于违反约束而导入失败，我们通过调整约束状态来达到目的。

  • 数据库约束有两类状态
    • 启用/禁用（enable/disable）：是否对新变更的数据启用约束验证
    • 验证/非验证 (validate/novalidate) ：是否对表中已客观存在的数据进行约束验证
  • 这两类四种状态从语法角度讲可以随意组合，默认是 enable validate

  下面我们来看着四类组合会分别出现什么样的效果：

enable validate : 默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行；
enable novalidate : 无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证；
disable validate : 可以添加违反约束的数据行，但对已存在的违反约束的数据行会做约束验证
　　　　　　　　（从描述中可以看出来，这本来就是一种相互矛盾的约束组合，只不过是语法上支持这种组合罢了，造成的结果就是会导致DML失败）
disable novalidate : 可以添加违法约束的数据行，对已存在的违反约束的数据行也不做验证。

  拿上面的例子来说，我们需要上传大量违反非空约束的历史数据（从业务角度讲这些数据不会造成系统功能异常），可以临时将约束状态转为 disable novalidate，以保证这些不合要求的数据导入表中

SQL> alter table emp modify constraint emp_ename_nn disable novalidate;

  在数据导入完成之后，我们再将约束状态转为enable novalidate 以确保之后添加的数据不会再违反约束

SQL> alter table emp modify constraint emp_ename_nn enable novalidate;

参考文章

1.ORACLE约束总结
2.Oracle约束(Constraint)详解

---

20/08/28

M