数据完整性：构建可靠数据库系统的基石

引言：数据完整性的重要性

在当今数据驱动的商业环境中，数据完整性已成为数据库设计的核心考量。根据IBM的研究，低质量数据每年给美国企业造成约3.1万亿美元的损失。本文将深入探讨数据完整性的四大类型及其实现机制，帮助您构建更加健壮可靠的数据库系统。

什么是数据完整性约束？
数据完整性：存储在数据库中的所有数据值均正确的状态。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性分为四类：
1. 实体完整性（实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.。）
2. 域完整性（域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。）
3. 参照完整性（参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性）

一、数据完整性概述

1.1 数据完整性的定义

数据完整性是指数据库中数据的准确性、一致性和可靠性，确保数据在整个生命周期中不被意外或恶意修改、破坏。

1.2 数据完整性的四种类型

二、实体完整性实现

2.1 主键约束

create table std3 (
     id number(6) constraint std3_id_pk primary key ,
     name varchar(200) constraint std3_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std3_email_uk unique(email)
)

--或者

create table std3 (
     id number(6) ,
     name varchar(200) constraint std3_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std3_email_uk unique(email) ,
     constraint std3_id_pk primary key(id)
)

最佳实践：

• 优先使用无业务意义的代理键（如自增ID）

• 避免使用可变更字段作为主键

• 复合主键字段数建议不超过3个

2.2 唯一约束

create table std2 (
     id number(6) constraint std2_id_uk unique ,
     name varchar(200) constraint std2_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std2_email_uk unique(email)
)

三、参照完整性实现

3.1 外键约束

-- 基本外键语法
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

-- 带级联操作的外键
ALTER TABLE order_items
ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id)
    REFERENCES orders(order_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT;

有效化无效化约束

可以将已有表中的约束有效化或无效化（disabled）。

将表std1中score字段的检查约束std1_score_ck 无效化

alter table std1
disable constraint std1_score_ck ;

此时检查约束将不再有效，可以添加不在check范围的数据。

将表std1中score字段的检查约束std1_score_ck 有效化

alter table std1
enable constraint std1_score_ck ;

std5表结构（外键级联删除）：

create table std5 (
     id number(6) ,
     name varchar(200) constraint std4_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
     friend_id number(6) ,
--表级约束
     constraint std5_email_uk unique(email) ,
     constraint std5_id_pk primary key(id) ,
     constraint std5_friend_id_fk foreign key(friend_id) references std3(id) on delete cascade
)

级联操作选项：

• NO ACTION：默认，阻止破坏完整性的操作

• CASCADE：级联删除/更新

• SET NULL：将外键设为NULL

• SET DEFAULT：设为默认值

四、域完整性实现

4.1 数据类型约束

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    in_stock BOOLEAN DEFAULT TRUE
);

4.2 检查约束

create table std7 (
     id number(6) ,
     name varchar(200) constraint std7_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std7_email_uk unique(email) ,
     constraint std7_id_pk primary key(id) ,
     constraint std7_score_ck check (score >= 0 and score <= 100)
)

4.3外键约束（foreign key）

新建表std4，字段：id（主键），name（非空），score（可为空），friend_id（外键关联std3.id）

create table std4 (
     id number(6) ,
     name varchar(200) constraint std4_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
     friend_id number(6) ,
--表级约束
     constraint std4_email_uk unique(email) ,
     constraint std4_id_pk primary key(id) ,
     constraint std4_friend_id_fk foreign key(friend_id) references std3(id)
)

4.4添加删除约束

可以往已知表中添加新的约束，使用ALTER TABLE - ADD(MODIFY)语句可以添加约束，

not null约束为列级约束必须使用modify

往表std1的score字段加入非空约束

alter table std1
drop constraint std1_id_nn ;

删除表std1中id非空的约束

alter table std1
drop constraint std1_id_nn ;

给表std1的score加入检查约束，要求范围为1-100
 

alter table std1
add(constraint std1_score_ck check(score>=0 and score<=100)) ;

非空约束（not null）

not null为列级约束，只能作用在列上
- 新建表std1，字段：id（非空），name（非空），score（可为空）

create table std1 (
     id number(6) constraint std1_id_nn not null ,
     name varchar(200) not null,
     score number(10,2)
)

级联删除与级联制空祥例

级联删除（ON DELETE CASCADE）：当父表中的列被删除时，字表中相对应的列也被删除。
级联制空（ON DELETE SET NULL）：当父表中的列被删除时，字表中相对应的列被制空。
表结构及初始数据
std3表结构：

create table std3 (
     id number(6) ,
     name varchar(200) constraint std3_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,

--表级约束
     constraint std3_email_uk unique(email) ,
     constraint std3_id_pk primary key(id)

create table std6 (
     id number(6) ,
     name varchar(200) constraint std6_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
     friend_id number(6) ,
--表级约束
     constraint std6_email_uk unique(email) ,
     constraint std6_id_pk primary key(id) ,
     constraint std6_friend_id_fk foreign key(friend_id) references std3(id) on delete set null
     )
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/u013634252/article/details/80696882

级联操作

• 此时删除std3中‘300001’号数据以后：

std5表：

std6表：

)

五、用户定义完整性实现

5.1 触发器示例

CREATE TRIGGER trg_audit_employee
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    VALUES (OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

5.2 存储过程验证

CREATE PROCEDURE sp_transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    -- 验证账户余额
    DECLARE balance DECIMAL(10,2);
    SELECT account_balance INTO balance 
    FROM accounts WHERE account_id = from_account;
    
    IF balance < amount THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- 执行转账
    UPDATE accounts SET account_balance = account_balance - amount
    WHERE account_id = from_account;
    
    UPDATE accounts SET account_balance = account_balance + amount
    WHERE account_id = to_account;
END;

六、高级完整性保护技术

6.1 事务隔离级别

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

6.2 乐观并发控制

-- 使用版本号实现乐观锁
UPDATE products 
SET stock = stock - 1, 
    version = version + 1
WHERE product_id = 1001 
AND version = 5;  -- 客户端读取的版本号

七、数据完整性监控与维护

7.1 完整性检查脚本

-- 查找违反参照完整性的记录
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- 检查约束违规
SELECT emp_id, salary 
FROM employees 
WHERE salary < 2500;

7.2 定期维护计划

-- MySQL表校验
ANALYZE TABLE employees;

-- SQL Server完整性检查
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;

结语：构建完整的数据治理体系

数据完整性不仅是技术实现，更是一种数据治理理念。建议企业：

1. 建立数据质量标准和验证流程

2. 实施变更管理控制结构修改

3. 定期进行数据质量审计

4. 为关键业务数据设置备份验证机制

延伸阅读方向：

• 数据仓库的ETL质量控制

• GDPR等合规要求下的数据完整性

• 区块链技术对数据完整性的创新应用

• 分布式系统中的最终一致性实现

欢迎在评论区分享您在维护数据完整性方面的实战经验！