维护数据完整性（一）-优快云博客

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/144674992

数据完整性约束

以下是关于 Oracle 数据完整性约束的表格：

约束类型	描述	示例语法（创建表时）	示例语法（修改表时）
实体完整性约束 - 主键约束（PRIMARY KEY）	表中一列或一组列值唯一标识每行记录，不允许重复和空值	CREATE TABLE employees (employee_id NUMBER(10) PRIMARY KEY, employee_name VARCHAR2(50));	ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);
实体完整性约束 - 唯一约束（UNIQUE）	确保列或列组值唯一，允许空值（表可多个唯一约束）	CREATE TABLE employees (employee_email VARCHAR2(100) UNIQUE);	ALTER TABLE employees ADD CONSTRAINT uk_employees_email UNIQUE (employee_email);
域完整性约束 - 非空约束（NOT NULL）	指定列不允许空值	CREATE TABLE employees (employee_name VARCHAR2(50) NOT NULL);	ALTER TABLE employees MODIFY employee_name VARCHAR2(50) NOT NULL;
域完整性约束 - 检查约束（CHECK）	定义列取值范围或条件	CREATE TABLE orders (order_quantity NUMBER CHECK (order_quantity > 0));	ALTER TABLE orders ADD CONSTRAINT ck_order_quantity CHECK (order_quantity > 0);
参照完整性约束 - 外键约束（FOREIGN KEY）	建立两表关联，外键引用另一表主键或唯一约束列	CREATE TABLE orders (order_id NUMBER, employee_id NUMBER, CONSTRAINT fk_orders_employees FOREIGN KEY (employee_id) REFERENCES employees(employee_id));	ALTER TABLE orders ADD CONSTRAINT fk_orders_employees FOREIGN KEY (employee_id) REFERENCES employees(employee_id);

操作	描述	示例语法
查看约束信息	通过数据字典视图查看约束，USER_CONSTRAINTS 查当前用户表约束，DBA_CONSTRAINTS 查所有表约束（需权限）	SELECT constraint_name, constraint_type, table_name FROM USER_CONSTRAINTS WHERE table_name = ‘EMPLOYEES’;
禁用约束	暂时禁用约束，如数据导入时提高性能	ALTER TABLE employees DISABLE CONSTRAINT pk_employees;
启用约束	恢复约束作用，数据需符合条件	ALTER TABLE employees ENABLE CONSTRAINT pk_employees;

完整性约束例子


-- 创建departments表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL
);

-- 创建employees表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE NOT NULL,
    -- 外键约束，确保department_id存在于departments表中
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 添加一个唯一性约束，确保不会有相同的姓名组合
ALTER TABLE employees
ADD CONSTRAINT uc_employee_name UNIQUE (first_name, last_name);

-- 添加一个检查约束，确保email字段包含'@'符号
ALTER TABLE employees
ADD CONSTRAINT chk_email_format CHECK (email LIKE '%@%');


# employee_id是主键，确保每条记录都有一个唯一的标识符。
# email字段有一个唯一性约束，确保不会有重复的电子邮件地址。
# department_id是外键，它引用departments表中的department_id，确保员工只能分配到存在的部门。
# salary字段有一个检查约束，确保工资必须大于0。
# first_name和last_name字段都有非空约束，确保这些字段在插入记录时不能为空。
# 通过ALTER TABLE语句添加了一个唯一性约束uc_employee_name，确保first_name和# last_name的组合是唯一的。
# 通过ALTER TABLE语句添加了一个检查约束chk_email_format，确保电子邮件地址符合基本格式（包含’@'符号）。

CHECK约束的特点和使用规则

CHECK约束是一种用于限制列中值的条件，确保插入或更新的数据满足特定的逻辑条件。以下是描述的详细解释：

可以在列级别或表级别定义：
- 列级别：CHECK约束可以直接在列定义时指定，只针对该列的值进行检查。
- 表级别：CHECK约束也可以在表定义之后，使用单独的语句添加，可以涉及多个列的值。
CHECK子句中指定的条件应评估为布尔结果：
- CHECK约束的条件必须是一个表达式，其结果为布尔值（TRUE或FALSE）。如果条件为TRUE，则允许插入或更新操作；如果为FALSE，则操作将被拒绝。
条件可以引用同一行中的其他列的值：
- 在CHECK约束的条件表达式中，可以引用同一行中的其他列的值来进行比较或计算。
条件不能使用查询：
- CHECK约束的条件不能包含子查询，即不能使用SELECT语句来获取条件值。
不能使用环境函数和伪列：
- 在CHECK约束的条件中，不能使用如SYSDATE（当前日期和时间）、USER（当前用户名）、USERENV（返回当前会话的环境信息）、UID（用户标识符）等环境函数，以及如ROWNUM（行号）、CURRVAL（序列当前值）、NEXTVAL（序列下一个值）或LEVEL（层次查询中的级别）等伪列。
一个列可以有多个CHECK约束：
- 一个列可以定义多个CHECK约束，每个约束都可以检查不同的条件。
列可以具有NULL值：
- 即使列上有CHECK约束，该列仍然可以接受NULL值，除非CHECK约束明确禁止了NULL值。
  总的来说，这段描述是在说明CHECK约束的语法规则和使用限制，以及它在数据库中的作用。

演示例子

CREATE TABLE tt(
    id int CONSTRAINT my_ct1 NOT NULL,
    name char(10) NOT NULL,
    sex char(1) NOT NULL,
    age int
);
ALTER TABLE tt ADD CONSTRAINT age_ct CHECK(age >= 0);
INSERT INTO tt VALUES(0, 'boobooke', 'M', 10);
-- 下面的插入语句会因为违反CHECK约束而失败
-- INSERT INTO tt VALUES(0, 'boobooke', 'M', -10);
commit;
SELECT * FROM tt;
-- 在添加新的CHECK约束之前，应该先删除旧的约束
-- ALTER TABLE tt DROP CONSTRAINT age_ct;
-- 然后添加新的约束
-- ALTER TABLE tt ADD CONSTRAINT age_ct CHECK(age > id);

在执行最后一个ALTER TABLE语句之前，需要先删除原有的age_ct约束，否则会因为约束名重复而失败。

唯一性约束（Unique Constraints）

列级别的唯一性约束：
对于单个列的唯一键（即该列的每个值都必须是唯一的），可以在列级别定义唯一性约束。
表级别的唯一性约束：
对于多列唯一键（也称为复合键），唯一性约束应该在表级别定义。在Oracle中，一个复合唯一键最多可以包含32个列。
唯一性索引的创建：
Oracle会在唯一键列上创建一个唯一性索引来强制执行唯一性。如果表中已经存在具有相同列的唯一索引或非唯一索引，Oracle将使用现有索引。
使用现有索引：
如果要使用现有的非唯一索引来强制唯一性约束，表不能包含任何重复的键值。
唯一性约束允许NULL值：
唯一性约束允许在约束列中存在NULL值。这意味着，尽管列中的每个非NULL值必须是唯一的，但可以有多个NULL值，因为NULL值在数据库中被视为不确定的，不等于任何其他NULL值。
以下是这些描述的示例SQL语句：

-- 创建表并定义单个列的唯一性约束
CREATE TABLE employees (
    employee_id INT CONSTRAINT emp_id_unique UNIQUE,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL
);
-- 创建表并定义多列的唯一性约束
CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    CONSTRAINT emp_name_unique UNIQUE (first_name, last_name)
);
-- 使用现有的非唯一索引作为唯一性约束
-- 假设有一个现有的非唯一索引 idx_emp_email
-- 首先确保没有重复的键值，然后添加唯一性约束
ALTER TABLE employees ADD CONSTRAINT emp_email_unique UNIQUE (email) USING INDEX idx_emp_email;

在最后一条语句中，USING INDEX子句指定了要使用的现有索引。这要求该索引覆盖了唯一性约束中指定的所有列，并且表中不存在违反唯一性的重复键值。

ALTER TABLE BONUS ADD CONSTRAINT UQ_EMP_ID UNIQUE (DEPT, EMP_ID)
USING INDEX TABLESPACE INDX STORAGE (INITIAL 32K NEXT 32K PCTINCREASE 0);
ALTER TABLE EMP ADD SSN VARCHAR2 (11) CONSTRAINT UQ_SSN UNIQUE;

可以为创建键时隐式创建的索引指定存储。如果没有指定存储，索引将在默认表空间的默认存储参数上创建。您可以像创建索引时那样指定LOGGING和NOSORT子句。创建的索引可以是本地分区索引或全局分区索引。索引将与唯一约束同名。
以下是两个示例。第一个示例定义了一个包含两列的唯一约束，并为索引指定了存储参数。第二个示例向EMP表添加了一个新列，并在列级别创建了唯一键。

-- 修改BONUS表，添加一个名为UQ_EMP_ID的唯一约束，该约束作用于DEPT和EMP_ID两列
-- 使用INDX表空间，并为索引指定存储参数：初始大小为32K，后续大小为32K，不增加百分比
ALTER TABLE BONUS ADD CONSTRAINT UQ_EMP_ID UNIQUE (DEPT, EMP_ID)
USING INDEX TABLESPACE INDX STORAGE (INITIAL 32K NEXT 32K PCTINCREASE 0);
-- 修改EMP表，添加一个名为SSN的列，数据类型为VARCHAR2(11)
-- 并在该列上创建一个名为UQ_SSN的唯一约束
ALTER TABLE EMP ADD SSN VARCHAR2 (11) CONSTRAINT UQ_SSN UNIQUE;

详细解释和介绍相关知识：

存储参数：在Oracle中，可以为索引指定存储参数，这些参数定义了索引的物理存储特性。INITIAL参数指定了初始分配给索引的存储空间大小，NEXT参数指定了当初始空间被填满后，为索引分配的额外空间的块大小，PCTINCREASE参数指定了每次分配额外空间时增加的百分比。
LOGGING和NOSORT子句：LOGGING子句指定在创建索引时，操作将被记录到数据库的重做日志中，这是默认行为。NOSORT子句指定在创建索引时，不需要对数据进行排序，这通常适用于数据已经按照索引列的顺序排列的情况。
分区索引：Oracle支持分区索引，这允许将索引数据分布在多个物理段上，可以是本地分区索引（与表分区相同的方式分区）或全局分区索引（可以与表分区不同方式分区）。
索引名称：在Oracle中，当创建唯一约束时，如果没有明确指定索引名称，则默认情况下，索引的名称将与唯一约束的名称相同。
在第一个示例中，USING INDEX子句用于指定索引的存储参数，包括表空间和存储参数。在第二个示例中，ADD子句用于向现有表添加新列，并通过CONSTRAINT子句在同一语句中为新列创建唯一约束。

CREATE TABLE t(id int, name char(10));
ALTER TABLE t ADD CONSTRAINT unique_id UNIQUE(id);
SELECT object_name, object_type FROM user_objects;
DROP INDEX unique_id;  # 无法删除该索引