4. 数据完整性审计：主键完整性检查、外键完整性检查、非空约束检查、默认值约束检查

数据完整性是数据质量的核心支柱，它确保数据在存储、传输和查询过程中保持准确、一致和可靠。本章聚焦于四种最关键的完整性约束审计：主键完整性、外键完整性、非空约束和默认值约束。通过系统化的SQL审计脚本，你可以快速定位违反约束的数据记录，并制定修复策略。

4.1 主键完整性检查

主键（Primary Key）唯一标识表中的每一行记录，其核心要求是非空且唯一。主键完整性审计的目标是发现重复键值和空值。

4.1.1 检查主键重复

-- 检查主键重复记录
SELECT 
    pk_column,
    COUNT(*) AS duplicate_count
FROM target_table
GROUP BY pk_column
HAVING COUNT(*) > 1;

4.1.2 检查主键空值

-- 检查主键列是否存在 NULL
SELECT 
    COUNT(*) AS null_count
FROM target_table
WHERE pk_column IS NULL;

4.2 外键完整性检查

外键（Foreign Key）维护表与表之间的引用关系，确保子表中的外键值在父表的主键中存在。外键完整性审计主要检测孤立记录（orphan records）。

4.2.1 检查孤立外键

-- 检查子表中不存在于父表的外键值
SELECT DISTINCT
    child.fk_column
FROM child_table child
LEFT JOIN parent_table parent
    ON child.fk_column = parent.pk_column
WHERE parent.pk_column IS NULL;

4.2.2 外键完整性审计清单

4.3 非空约束检查

非空约束（NOT NULL）确保关键业务字段不能缺失。审计时需扫描所有定义为 NOT NULL 的列，找出实际存储为 NULL 的记录。

-- 动态生成非空约束检查 SQL
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    CONCAT('SELECT COUNT(*) AS null_count FROM ', 
           TABLE_SCHEMA, '.', TABLE_NAME, 
           ' WHERE ', COLUMN_NAME, ' IS NULL;') AS audit_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'NO'
  AND TABLE_SCHEMA = 'your_database';

4.4 默认值约束检查

默认值约束（DEFAULT）在插入记录时自动填充指定值。审计重点在于验证默认值是否被正确应用，以及是否存在因显式插入 NULL 导致默认值失效的情况。

4.4.1 检查默认值是否生效

-- 检查某列是否使用了默认值（以创建时间为例）
SELECT 
    COUNT(*) AS missing_default_count
FROM target_table
WHERE created_at IS NULL 
   OR created_at = '1900-01-01';  -- 常见默认值占位

4.4.2 默认值约束审计对比

4.5 完整性审计流程总览

下图展示了数据完整性审计的完整流程，从识别约束定义到生成修复脚本，形成闭环。

4.6 综合审计脚本示例

以下是一个整合了四种完整性检查的审计脚本模板，适用于单表或多表场景：

-- ============================================
-- 数据完整性审计脚本（单表示例）
-- 表名：orders
-- ============================================

-- 1. 主键完整性：检查 order_id 重复和空值
SELECT '主键重复' AS check_type, order_id, COUNT(*) AS cnt
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1
UNION ALL
SELECT '主键空值' AS check_type, NULL, COUNT(*)
FROM orders
WHERE order_id IS NULL;

-- 2. 外键完整性：检查 customer_id 是否在 customers 表存在
SELECT '孤立外键' AS check_type, o.customer_id, COUNT(*) AS cnt
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
GROUP BY o.customer_id;

-- 3. 非空约束检查：检查必填字段
SELECT '非空违规' AS check_type, 'customer_name' AS column_name, COUNT(*) AS cnt
FROM orders
WHERE customer_name IS NULL
UNION ALL
SELECT '非空违规', 'order_date', COUNT(*)
FROM orders
WHERE order_date IS NULL;

-- 4. 默认值约束检查：检查 status 是否使用了默认值
SELECT '默认值缺失' AS check_type, COUNT(*) AS cnt
FROM orders
WHERE status IS NULL OR status = '';

4.7 常见问题与避坑指南

• 主键重复误判： 联合主键需检查所有列的组合唯一性，单列检查可能遗漏。
• 外键性能问题： 大表 LEFT JOIN 检查孤立记录时，务必在外键列上建立索引。
• 非空约束与默认值冲突： 同时定义 NOT NULL 和 DEFAULT 时，显式插入 NULL 会导致违反非空约束，而非使用默认值。
• 默认值类型匹配： 默认值的数据类型必须与列定义一致，否则可能导致隐式转换错误。