Oracle约束管理 #

约束的作用
├── 保证数据完整性
│   ├── 实体完整性（主键）
│   ├── 参照完整性（外键）
│   └── 域完整性（检查、非空）
├── 防止无效数据
│   └── 在数据插入/更新时验证
└── 维护数据一致性
    └── 确保关联数据正确

Oracle约束类型
├── PRIMARY KEY（主键约束）
│   └── 唯一标识每一行，不允许NULL
├── UNIQUE（唯一约束）
│   └── 列值唯一，允许NULL
├── NOT NULL（非空约束）
│   └── 列值不能为空
├── FOREIGN KEY（外键约束）
│   └── 引用其他表的主键或唯一键
├── CHECK（检查约束）
│   └── 自定义条件验证
└── REF（引用约束）
    └── 引用对象类型（较少使用）

-- 列级主键约束
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- 表级主键约束（命名）
CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

-- 复合主键
CREATE TABLE order_items (
    order_id NUMBER(10),
    item_id NUMBER(5),
    product_id NUMBER(6),
    quantity NUMBER(5),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_id)
);

-- 使用序列创建自增主键
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50)
);

CREATE SEQUENCE seq_employees START WITH 1 INCREMENT BY 1;

INSERT INTO employees VALUES (seq_employees.NEXTVAL, 'John');

-- 12c+ 使用IDENTITY列
CREATE TABLE employees (
    employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- 添加主键
ALTER TABLE employees ADD PRIMARY KEY (employee_id);
ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

-- 删除主键
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees DROP CONSTRAINT pk_employees;

-- 删除主键（级联删除外键）
ALTER TABLE employees DROP PRIMARY KEY CASCADE;

-- 禁用/启用主键
ALTER TABLE employees DISABLE PRIMARY KEY;
ALTER TABLE employees ENABLE PRIMARY KEY;

-- 查看主键信息
SELECT 
    c.CONSTRAINT_NAME,
    cc.COLUMN_NAME,
    c.STATUS
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'EMPLOYEES' AND c.CONSTRAINT_TYPE = 'P';

主键约束特点：
├── 唯一性：每行主键值必须唯一
├── 非空性：主键列不允许NULL值
├── 每表只能有一个主键
├── 自动创建唯一索引
└── 可由多列组成（复合主键）

-- 列级外键约束
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6) REFERENCES customers(customer_id),
    order_date DATE
);

-- 表级外键约束（命名）
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    order_date DATE,
    CONSTRAINT fk_orders_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

-- 自引用外键（父子关系在同一表）
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    employee_name VARCHAR2(50),
    manager_id NUMBER(6),
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
);

-- 复合外键
CREATE TABLE order_items (
    order_id NUMBER(10),
    item_id NUMBER(5),
    product_id NUMBER(6),
    CONSTRAINT fk_order_items_orders 
        FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
);

-- ON DELETE CASCADE：删除父记录时自动删除子记录
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- ON DELETE SET NULL：删除父记录时设置子记录为NULL
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

-- ON DELETE NO ACTION：默认行为，不允许删除有子记录的父记录
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE NO ACTION
);

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_customer;

-- 禁用/启用外键
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;

-- 启用外键（不验证现有数据）
ALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT fk_customer;

-- 查看外键信息
SELECT 
    c.CONSTRAINT_NAME,
    cc.COLUMN_NAME,
    c.R_CONSTRAINT_NAME AS REFERENCED_CONSTRAINT,
    c.DELETE_RULE
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'ORDERS' AND c.CONSTRAINT_TYPE = 'R';

外键约束注意事项：
├── 父表必须有主键或唯一约束
├── 子表外键列数据类型必须与父表匹配
├── 外键列允许NULL值（除非另有非空约束）
├── 删除父表需要先删除外键或使用CASCADE
└── 大量数据操作时考虑禁用外键提升性能

-- 列级唯一约束
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    email VARCHAR2(100) UNIQUE,
    phone VARCHAR2(20)
);

-- 表级唯一约束（命名）
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    email VARCHAR2(100),
    phone VARCHAR2(20),
    CONSTRAINT uk_email UNIQUE (email)
);

-- 复合唯一约束
CREATE TABLE user_roles (
    user_id NUMBER(6),
    role_id NUMBER(6),
    CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
);

-- 多个唯一约束
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    email VARCHAR2(100),
    phone VARCHAR2(20),
    CONSTRAINT uk_email UNIQUE (email),
    CONSTRAINT uk_phone UNIQUE (phone)
);

-- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email);
ALTER TABLE employees ADD UNIQUE (email);

-- 删除唯一约束
ALTER TABLE employees DROP CONSTRAINT uk_email;
ALTER TABLE employees DROP UNIQUE (email);

-- 禁用/启用唯一约束
ALTER TABLE employees DISABLE CONSTRAINT uk_email;
ALTER TABLE employees ENABLE CONSTRAINT uk_email;

-- 查看唯一约束
SELECT 
    c.CONSTRAINT_NAME,
    cc.COLUMN_NAME,
    c.STATUS
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'EMPLOYEES' AND c.CONSTRAINT_TYPE = 'U';

-- 列级检查约束
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    salary NUMBER(10,2) CHECK (salary > 0),
    age NUMBER(3) CHECK (age >= 18 AND age <= 65)
);

-- 表级检查约束（命名）
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    salary NUMBER(10,2),
    CONSTRAINT chk_salary CHECK (salary > 0)
);

-- 复杂检查约束
CREATE TABLE products (
    product_id NUMBER(6) PRIMARY KEY,
    product_name VARCHAR2(100),
    price NUMBER(10,2),
    discount NUMBER(5,2),
    stock_quantity NUMBER(5),
    status VARCHAR2(20),
    
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount >= 0 AND discount <= 100),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0),
    CONSTRAINT chk_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'DISCONTINUED'))
);

-- 多列检查约束
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

-- 条件检查约束
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    salary NUMBER(10,2),
    commission_pct NUMBER(5,2),
    CONSTRAINT chk_commission CHECK (
        commission_pct IS NULL OR 
        (commission_pct >= 0 AND commission_pct <= 100)
    )
);

-- 添加检查约束
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 删除检查约束
ALTER TABLE employees DROP CONSTRAINT chk_salary;

-- 禁用/启用检查约束
ALTER TABLE employees DISABLE CONSTRAINT chk_salary;
ALTER TABLE employees ENABLE CONSTRAINT chk_salary;

-- 查看检查约束
SELECT 
    c.CONSTRAINT_NAME,
    c.SEARCH_CONDITION,
    c.STATUS
FROM USER_CONSTRAINTS c
WHERE c.TABLE_NAME = 'EMPLOYEES' AND c.CONSTRAINT_TYPE = 'C';

-- 检查约束的限制：
-- 1. 不能包含子查询
-- 2. 不能包含SYSDATE、USER等函数
-- 3. 不能引用其他行的值
-- 4. 不能调用用户定义函数

-- 无效的检查约束示例
-- 错误：包含子查询
ALTER TABLE employees ADD CONSTRAINT chk_dept 
CHECK (department_id IN (SELECT department_id FROM departments));

-- 错误：包含SYSDATE
ALTER TABLE employees ADD CONSTRAINT chk_hire_date 
CHECK (hire_date <= SYSDATE);

-- 替代方案：使用触发器
CREATE OR REPLACE TRIGGER trg_check_dept
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.department_id NOT IN (SELECT department_id FROM departments) THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid department');
    END IF;
END;
/

-- 列级非空约束
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100)
);

-- 非空约束与默认值
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    status VARCHAR2(20) DEFAULT 'ACTIVE' NOT NULL,
    create_time TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

-- 添加非空约束
ALTER TABLE employees MODIFY last_name NOT NULL;

-- 删除非空约束
ALTER TABLE employees MODIFY last_name NULL;

-- 查看非空约束
SELECT 
    COLUMN_NAME,
    NULLABLE,
    DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES' AND NULLABLE = 'N';

非空约束特点：
├── 只能定义在列级别
├── 不自动创建索引
├── 与CHECK约束类似但语法不同
├── NULL表示未知值，不等于空字符串
└── 主键列自动具有非空约束

-- 约束状态组合
-- ENABLE VALIDATE：启用并验证所有数据（默认）
-- ENABLE NOVALIDATE：启用但不验证现有数据
-- DISABLE VALIDATE：禁用但验证新数据
-- DISABLE NOVALIDATE：禁用且不验证

-- 启用约束（验证所有数据）
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary;

-- 启用约束（不验证现有数据）
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT chk_salary;

-- 禁用约束（保留索引）
ALTER TABLE employees DISABLE VALIDATE CONSTRAINT chk_salary;

-- 完全禁用约束
ALTER TABLE employees DISABLE NOVALIDATE CONSTRAINT chk_salary;

-- 场景1：大批量数据导入
-- 先禁用约束，导入数据，再启用约束
ALTER TABLE employees DISABLE CONSTRAINT fk_dept;
-- 导入数据...
ALTER TABLE employees ENABLE CONSTRAINT fk_dept;

-- 场景2：历史数据清理
-- 启用约束但不验证历史数据
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT chk_salary;

-- 场景3：数据修复
-- 禁用约束修复数据后重新启用
ALTER TABLE employees DISABLE CONSTRAINT chk_salary;
-- 修复数据...
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary;

-- 查看约束状态
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    STATUS,
    VALIDATED,
    DEFERRED
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';

-- STATUS：ENABLED/DISABLED
-- VALIDATED：VALIDATED/NOT VALIDATED
-- DEFERRED：IMMEDIATE/DEFERRED

-- 创建延迟约束
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        DEFERRABLE INITIALLY DEFERRED
);

-- DEFERRABLE：允许延迟检查
-- INITIALLY IMMEDIATE：默认立即检查（每条语句后）
-- INITIALLY DEFERRED：默认延迟检查（事务提交时）

-- 设置约束延迟
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS fk_customer DEFERRED;

-- 设置约束立即
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS fk_customer IMMEDIATE;

-- 延迟约束应用场景
-- 场景：循环引用
-- 表A引用表B，表B引用表A

-- 使用延迟约束解决循环引用
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    department_id NUMBER(4),
    CONSTRAINT fk_dept 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
        DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    manager_id NUMBER(6),
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
        DEFERRABLE INITIALLY DEFERRED
);

-- 插入数据
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO employees VALUES (1, 10);
INSERT INTO departments VALUES (10, 1);
COMMIT;  -- 提交时验证约束

-- 启用约束时创建异常表
-- 创建异常表
BEGIN
    DBMS_IOT.CREATE_EXCEPTION_TABLE('EXCEPTIONS');
END;
/

-- 或手动创建
CREATE TABLE exceptions (
    row_id ROWID,
    owner VARCHAR2(30),
    table_name VARCHAR2(30),
    constraint VARCHAR2(30)
);

-- 启用约束并记录异常
ALTER TABLE employees ENABLE CONSTRAINT chk_salary 
EXCEPTIONS INTO exceptions;

-- 查找违规数据
SELECT e.* 
FROM employees e, exceptions ex
WHERE e.ROWID = ex.row_id
AND ex.table_name = 'EMPLOYEES';

-- 查找并修复违规数据
-- 1. 查看违规记录
SELECT * FROM exceptions WHERE table_name = 'EMPLOYEES';

-- 2. 查看具体数据
SELECT * FROM employees WHERE ROWID IN (
    SELECT row_id FROM exceptions WHERE table_name = 'EMPLOYEES'
);

-- 3. 修复数据
UPDATE employees SET salary = 0 WHERE salary < 0;

-- 4. 清空异常表
DELETE FROM exceptions;

-- 5. 重新启用约束
ALTER TABLE employees ENABLE CONSTRAINT chk_salary;

-- 常见约束错误
-- ORA-00001：违反唯一约束
-- ORA-02290：违反检查约束
-- ORA-02291：违反外键约束（父记录不存在）
-- ORA-02292：违反外键约束（子记录存在）
-- ORA-01400：违反非空约束

-- 查看约束错误详情
SELECT 
    c.CONSTRAINT_NAME,
    c.CONSTRAINT_TYPE,
    c.SEARCH_CONDITION,
    c.R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS c
WHERE c.CONSTRAINT_NAME = '约束名';

-- 查看表的所有约束
SELECT 
    c.CONSTRAINT_NAME,
    c.CONSTRAINT_TYPE,
    c.STATUS,
    c.VALIDATED,
    cc.COLUMN_NAME
FROM USER_CONSTRAINTS c
LEFT JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'EMPLOYEES'
ORDER BY c.CONSTRAINT_TYPE, c.CONSTRAINT_NAME;

-- 查看外键关系
SELECT 
    c.TABLE_NAME AS CHILD_TABLE,
    cc.COLUMN_NAME AS CHILD_COLUMN,
    r.TABLE_NAME AS PARENT_TABLE,
    rc.COLUMN_NAME AS PARENT_COLUMN,
    c.DELETE_RULE
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
JOIN USER_CONSTRAINTS r ON c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
JOIN USER_CONS_COLUMNS rc ON r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'R';

-- 查看禁用的约束
SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    STATUS
FROM USER_CONSTRAINTS
WHERE STATUS = 'DISABLED';