ALL_CONSTRAINTS
📄字数 1.3K
👁️阅读量 加载中...
功能描述
ALL_CONSTRAINTS系统视图用于存储和管理表的约束信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | TABLE_ID | INTEGER | 表ID | √ | √ |
| 2 | REF_TABLE_ID | INTEGER | 引用的表的ID（对于外键约束有效） | √ | √ |
| 3 | CONS_NAME | VARCHAR | 约束名 | √ | √ |
| 4 | CONS_TYPE | CHAR | 约束类型('F'：外键；'R'：引用外键；'C'：值检查；'U'：唯一值；'P'：主键) | √ | √ |
| 5 | MATCH_TYPE | CHAR | 外键匹配方式('A'：全部匹配；'P'：部分匹配; 'U': 简单匹配(只要约束字段中含有空值，则不作外键匹配检查)) | √ | √ |
| 6 | DEFERRABLE | BOOLEAN | 延期特性 | √ | √ |
| 7 | INITDEFERRED | BOOLEAN | 延期特性的初始设置 | √ | √ |
| 8 | DEFINE | VARCHAR | 约束定义 | √ | √ |
| 9 | UPDATE_ACTION | CHAR | 当引用的记录更改时的反向动作 | √ | √ |
| 10 | DELETE_ACTION | CHAR | 当引用的记录删除时的反向动作 | √ | √ |
| 11 | ENABLE | BOOLEAN | 值检查约束是否可用（对于值检查约束有效） | √ | √ |
| 12 | VALID | BOOLEAN | 是否有效 | √ | √ |
| 13 | IS_SYS | BOOLEAN | 是否系统内建 | √ | √ |
| 14 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 15 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
删除（DELETE_ACTION）或更改（UPDATE_ACTION）的迭代性
| key_actions | 含义 | 解释 |
|---|---|---|
| n | NO ACTION | 父表变更使子表不符合约束时，变更操作将被拒绝并回滚 |
| r | RESTRICT | 父表变更时，子表字段值随之变更；父表删除时会被拒绝 |
| c | CASCADE | 父表变更或删除时，子表字段值随之变更或删除 |
| u | SET NULL | 父表变更或删除时，子表字段值置为空值 |
| d | SET DEFAULT | 父表变更或删除时，子表字段值置为默认值或 NULL |
应用举例
sql
SQL> CREATE TABLE tab_foreign_key(c3 INT PRIMARY KEY);
-- 创建带有主键约束、外键约束、唯一值约束、值检查约束的表
SQL> CREATE TABLE tab_cons_info(id INT, c1 VARCHAR, c2 INT UNIQUE, c3 INT, CONSTRAINT consn PRIMARY KEY(id),CONSTRAINT c1 CHECK(c1='M' OR c1='F'), CONSTRAINT fk1 FOREIGN KEY(c3) REFERENCES tab_foreign_key(c3));
SQL> SELECT * FROM ALL_CONSTRAINTS;
+-------+----------+--------------+-----------------------+-----------+------------+------------+--------------+----------------------------------+---------------+---------------+--------+-------+--------+-----------+-----------+
| DB_ID | TABLE_ID | REF_TABLE_ID | CONS_NAME | CONS_TYPE | MATCH_TYPE | DEFERRABLE | INITDEFERRED | DEFINE | UPDATE_ACTION | DELETE_ACTION | ENABLE | VALID | IS_SYS | RESERVED1 | RESERVED2 |
+-------+----------+--------------+-----------------------+-----------+------------+------------+--------------+----------------------------------+---------------+---------------+--------+-------+--------+-----------+-----------+
| 1 | 1048609 | <NULL> | PK_S25109175069214915 | P | <NULL> | <NULL> | <NULL> | "C3" | <NULL> | <NULL> | T | T | F | <NULL> | <NULL> |
| 1 | 1048611 | 1048609 | FK1 | F | U | F | F | ("C3")("C3") | n | n | T | T | <NULL> | <NULL> | <NULL> |
| 1 | 1048611 | <NULL> | UK_S15042175069215217 | U | <NULL> | <NULL> | <NULL> | "C2" | <NULL> | <NULL> | T | T | F | <NULL> | <NULL> |
| 1 | 1048611 | <NULL> | CONSN | P | <NULL> | <NULL> | <NULL> | "ID" | <NULL> | <NULL> | T | T | F | <NULL> | <NULL> |
| 1 | 1048611 | <NULL> | C1 | C | <NULL> | <NULL> | <NULL> | ("C1") = ('M') OR ("C1") = ('F') | <NULL> | <NULL> | T | T | F | <NULL> | <NULL> |
+-------+----------+--------------+-----------------------+-----------+------------+------------+--------------+----------------------------------+---------------+---------------+--------+-------+--------+-----------+-----------+