什么是约束
约束就是在表上强制执行的规则。约束可以防止存在相关性时产生误删除或者误操作。
Oracle包括以下约束：
- NOT NULL 非空约束，此列不允许有空值。
- UNIQUE 唯一约束，不能有重复项。
- PRIMARY KEY 主键约束，一个表具有标识性的列，非空且唯一。
- FOREIGN KEY 外键约束，在列和被引用的表的列之间建立一个外键关系。
- CHECK 检查约束，当条件满足时，才能向改列插入数据或者更新数据。
约束的相关规则：
- 约束的名字描述性好
- 可以在一下任何时刻创建约束：
- 创建表的时候创建
- 创建表后，可以补充约束的定义
- 可以在列或者表级别定义约束。
- 可以在数据字典中查看相关约束的定义。
定义列级约束
引用一个列，并在有用列的范围内定义，可以定义任何类型的约束条件。
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