Oracle 19C学习 - 14. 约束

什么是约束

约束就是在表上强制执行的规则。约束可以防止存在相关性时产生误删除或者误操作。

Oracle包括以下约束：

1. NOT NULL 非空约束，此列不允许有空值。
2. UNIQUE 唯一约束，不能有重复项。
3. PRIMARY KEY 主键约束，一个表具有标识性的列，非空且唯一。
4. FOREIGN KEY 外键约束，在列和被引用的表的列之间建立一个外键关系。
5. CHECK 检查约束，当条件满足时，才能向改列插入数据或者更新数据。
   
   
   

约束的相关规则：

1. 约束的名字描述性好
2. 可以在一下任何时刻创建约束：
   • 创建表的时候创建
   • 创建表后，可以补充约束的定义
3. 可以在列或者表级别定义约束。
4. 可以在数据字典中查看相关约束的定义。
   
   
   

定义列级约束

引用一个列，并在有用列的范围内定义，可以定义任何类型的约束条件。

CREATE TABLE TEST_1 (
ID number(6) PRIMARY KEY,
NAME VARCHAR2(50));

定义表级约束

引用一个或者多个列，并在该表的列定义中分别进行定义，可以定义除了NOT NULL的任何约束。

-- 先创建列，再定义约束
CREATE TABLE TEST_2 (
ID NUMBER(6),
FIRST_NAME VARCHAR2(50),
CONSTRAINT TEST_2_ID_PK PRIMARY KEY (ID));

非空约束的定义

CREATE TABLE NAME (
FIELD_NAME TYPE NOT NULL);

或者

CREATE TABLE NAME(
FIELD_NAME TYPE CONTRAINT TABLE_COLNAME_NN NOT NULL);

CREATE TABLE TEST_4 (
ID NUMBER NOT NULL ,
NAME VARCHAR2(40) CONSTRAINT NAME_NN NOT NULL);

唯一键UNIQUE约束

CREATE TABLE NAME (
FIELD_NAME TYPE UNIQUE);

或者

CREATE TABLE NAME(
FIELD_NAME TYPE CONTRAINT NAME_UK UNIQUE);

CREATE TABLE TEST_5(
ID NUMBER UNIQUE,
NAME VARCHAR2(50) CONSTRAINT TABLE_COLNAME_UK UNIQUE);

主键PRIMARY KEY约束

CREATE TABLE NAME (
FIELD_NAME TYPE PRIMARY KEY);

或者

CREATE TABLE NAME(
FIELD_NAME TYPE CONTRAINT TABLE_COLNAME_PK PRIMARY KEY);

CREATE TABLE TEST_6 (
ID NUMBER CONSTRAINT TEST_6_ID_PK PRIMARY KEY);

外键FOREIGN KEY约束

外键将定义一个列或者列的组合作为外键，并建立与同一个表或不同表的主键之间的关系。
被引用、提供主键的表称为父表。参考父表的主键来定义外键的表称为子表。

CONSTRAINT 外键名 FOREIGN KEY (子表的列) REFERENCES 父表名(父表的主键列));

这种如果子表有对应数据，删除主表的纪录会把子表中对应的记录级联删除。
CONSTRAINT 外键名 FOREIGN KEY (子表的列) REFERENCES 父表名(父表的主键列) ON DELETE CASCADE);

这种如果子表有对应数据，删除主表的纪录会把子表中对应的记录设置为NULL。
CONSTRAINT 外键名 FOREIGN KEY (子表的列) REFERENCES 父表名(父表的主键列) ON DELETE SET NULL);

CREATE TABLE TEST_DEPT(
ID NUMBER PRIMARY KEY,
DEPT_NAME VARCHAR2(50) NOT NULL
);

CREATE TABLE TEST_EMP(
ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(50) NOT NULL,
DEPT_ID NUMBER, 
CONSTRAINT TEST_EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES TEST_DEPT(ID));

外键的值可以为空，也可以重复。

INSERT INTO TEST_DEPT 
VALUES (1, 'IT');

INSERT INTO TEST_DEPT 
VALUES (2, 'EO');

INSERT INTO TEST_EMP
VALUES (3, 'winston', NULL);

INSERT INTO TEST_EMP
VALUES (4, 'GRACE' , 2);

父表的主键的记录在子表中已经有对应的记录时，无法删除父表的记录。
父表的主键的记录在子表中没有对应的记录时，可以删除父表的记录。

DELETE FROM TEST_DEPT
WHERE ID = 1;

TRUNCATE TABLE TEST_DEPT;

SQL Error: ORA-02292: integrity constraint (HR.TEST_EMP_DEPT_FK) violated
 - child record found


创建外键的时候，如果加上ON DELETE CASCADE，子表的数据直接被删除。
CREATE TABLE TEST_EMP1(
ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(50) CONSTRAINT TEST_EMP1_EMP_NAME_NN NOT NULL,
DEPT_ID NUMBER,
CONSTRAINT TEST_DEPT_EMP1_DEPT_ID_FK FOREIGN KEY (DEPT_ID) REFERENCES TEST_DEPT(ID) ON DELETE CASCADE);

创建外键的时候，如果加上ON DELETE SET NULL，子表的数据直接被赋值NULL。
CREATE TABLE TEST_EMP1(
ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(50) CONSTRAINT TEST_EMP1_EMP_NAME_NN NOT NULL,
DEPT_ID NUMBER,
CONSTRAINT TEST_DEPT_EMP1_ID_FK FOREIGN KEY (DEPT_ID) REFERENCES TEST_DEPT(ID) ON DELETE SET NULL);

CHECK约束

用于定义每行都必须满足的条件
对一下表达式不允许：
1. 对CURRVAL NEXTBAL LEVEL 和ROWNUM伪列调用不允许。
2. 对SYSDATE UID USER和USERENV函数调用也是不允许。
3. 涉及其他行中的其他值的查询也不允许。

CONSTRAINT 约束名 CHECK (表达式)

CREATE TABLE TEST_CHECK1(
ID NUMBER,
CONSTRAINT TEST_CHECK1_ID_MAX CHECK (ID <100) );

INSERT INTO TEST_CHECK1 VALUES (111);
SQL Error: ORA-02290: check constraint (HR.TEST_CHECK1_ID_MAX) violated

添加约束

ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型;

给一个表添加外键
ALTER TABLE TEST_EMP 
ADD CONSTRAINT TEST_DEPT_EMP_DEPT_NO_FK FOREIGN KEY (dept) 
    REFERENCES TEST_DEPT(ID) ON DELETE SET NULL;

给一个表添加主键
ALTER TABLE TEST_EMP 
ADD CONSTRAINT TEST_EMP_EMP_NO_PK PRIMARY KEY (emp_no);

删除约束

ALTER TABLE 表名
DROP CONSTRAINT 约束名;

删除主键约束
ALTER TABLE TEST_EMP
DROP CONSTRAINT TEST_EMP_EMP_NO_PK;

当删除一个表的主键的时候，如果存在外键引用，那么会报错不让删除，这时需要再删除的语句后面加上CASCADE，来级联删除子表的外键。

ALTER TABLE TEST_DEPT
DROP CONSTRAINT SYS_C007664 ;
ORA-02273: this unique/primary key is referenced by some foreign keys

ALTER TABLE TEST_DEPT
DROP CONSTRAINT SYS_C007664 CASCADE;

当一个表的主键被子表引用的时候，DROP TABLE PURGE也不行，必须DROP PRIMARY KEY CASCADE以后再删除表。

禁用和启用约束

ALTER TABLE 表名
DISABLE / ENABLE CONTRAINT 约束名;

比如主键禁用以后，可以添加重复数据，在启用主键的时候，就会报有重复项，无法启用。

对于主表的主键进行禁用，如果有外键参考，那么无法禁用。
需要在禁用的语句加上CASCADE来级联禁用子表的外键约束。

ALTER TABLE TEST_DEPT
DISABLE CONSTRAINT TEST_DEPT_ID_PK CASCADE;

TEST_DEPT的主键禁用了，并且EMP表相应的外键也被禁用了

即使父表的主键恢复了，子表的外键还是禁用状态。
alter table test_dept
enable constraint test_dept_id_pk;

需要再手工启用子表的外键。
ALTER TABLE TEST_EMP
ENABLE CONSTRAINT TEST_DEPT_EMP_ID_FK;

删除列的时候级联删除约束

ALTER TABLE 表名
DROP COLUMN 列名 CASCADE CONSTRAINTS;
删除列的时候，将自己的约束和引用他的外键约束一起删除。

ALTER TABLE TEST_DEPT
DROP COLUMN ID CASCADE CONSTRAINTS;

ID列被删除，这个列的主键约束也被删除，引用他的子表的外键约束也被删除。

查看约束

USER_CONSTRAINTS表记录了约束。

查看表结构
DESC USER_CONSTRAINTS;

获取约束名和对应的表
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME 
FROM USER_CONSTRAINTS
where table_name = 'EMPLOYEES'

EMP_DEPT_FK	       R	EMPLOYEES
EMP_EMAIL_UK	       U	EMPLOYEES
EMP_SALARY_MIN	       C	EMPLOYEES