Oracle SQL 学习笔记14- 约束

约束（Constraint）概要

约束是强加在表上的规则或条件。确保数据库满足业务规则。保证数据的完整性。当对表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，系统就会拒绝执行这个操作。

约束可以是列一级别的，也可以是表级别的，还可以定义表与表之间的数据依赖关系。

约束的类型：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

约束的指导原则

• 可以自定义或者系统自动命名为SYS_Cn的格式
• 可以在建表时或者建表之后创建约束
• 可以在列或者表级别定义约束
• 可以通过数据字典查询约束

定义约束的语法

CREATE  TABLE  [schema.]table
  (column datatype [DEFAULT expr]
  [column_constraint],
  ...
  [table constraint][,...]);

column  [CONSTRAINT constraint_name]  constraint_type,

column,...
  [CONSTRAINT constraint_name]  constraint_type
(column, ...),

实例

列级别的约束：

CREATE TABLE employees(
  employee_id NUMBER(6)
    CONSTRAINT emp_emp_id_pk PRIMARY KEY,
  first_name  VARCHAR2(20),
  ....)

表级别的约束：

CREATE  TABLE  employees(
  employee_id NUMBER(6),
  first_name  VARCHAR2(20),
  ...
  job_id  VARCHAR2(10) NOT NULL,
  CONSTRAINT emp_emp_id_pk
    PRIMARY  KEY  (EMPLOYEE_ID));

外键约束（FOREIGN KEY Constraint）

关键字

• FOREIGN KEY：定义字表的列
• REFERENCES：定于父表和列
• ON DELETE CASCADE：递归删除字表数据
• ON DELETE SET NULL：设置字表列值为空

CHECK 约束

CHECK约束定义数据条件。例如，规定员工的工资必须大于0：

..., salary NUMBER(2)
    CONSTRAINT emp_salary_min
        CHECK (salary > 0), ...

CHECK 中的限制

• 不能引用CURRVAL，NEXTVAL，LEVEL，ROWNUM
• 不能调用SYSDATE，UID，USER 和 USERENV函数
• 不能查询其他行的值

增加约束的语法

用 Alter table 语句可以管理约束：

1. 增加或删除约束，但不能改变约束的结构
2. 启用或者禁用约束
3. 使用Modify子句来定义非空约束

ALTER TABLE <table_name>
ADD [CONSTRAINT <constraint_name>]
type  (<column_name>);

延迟约束

约束可以定义为

• DEFERRABLE 或者 NOT DEFERRABLE
  DEFERRABLE 和 NOT DEFERRABLE 意味着在接下来的事务中，constraint check 是否可以通过设置 SET CONSTRAINT(S) 推迟到事务的结束。如果删除这一语句，那么默认为NOT DEFERRABLE。
  如果声明了一个新的 constraint 为NOT DEFERRABLE，那么在CREATE TABLE 或者 ALTER TABLE 语句提交的时候，它必须是valid状态。
• INITIALLY DEFERRED 或者 INITIALLY IMMEDIATE
  如果设置了 DEFERRABLE，那么在之后的事务中，可以用 SET CONSTRAINT[S] 子句将这一约束的 Check 推迟到事务提交的时候【1】。

例子：
在创建约束的时候设置为将其推迟到事务结束：

ALTER TABLE dept2
ADD CONSTRAINT dept2_id_pk
PRIMARY KEY (department_id)
DEFERRABLE INITIALLY DEFERRED;

修改约束，使其在每个SQL语句的结尾生效：

SET CONSTRAINTS dept2_id_pk IMMEDIATE

通过修改Session的设置，使得Session内所有的约束都在每个SQL语句的结尾生效：：

ALTER SESSION
SET CONSTRAINTS =  IMMEDIATE

启用约束

启用约束：

ALTER TABLE  emp2
ENABLE  CONSTRAINT  emp_dt_fk;

级联约束

在DROP COLUMN子句中可以加上CASCADE CONSTRAINTS子句，可以删除所有依赖被删除的主键列的外键约束，同时也会删除所有依赖的多列约束。

【1】https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#i1002038