数据库外键：深度分析与最佳实践

-- 创建表时定义外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 或者先创建表，后添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- MySQL 中，InnoDB 引擎会自动为外键创建索引
-- 但显式创建索引仍然是好习惯
CREATE INDEX idx_customer_id ON orders(customer_id);

-- PostgreSQL 需要手动创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 假设 customers 表中有 customer_id = 1 的记录
-- 以下操作会被外键约束阻止：
INSERT INTO orders (customer_id, order_date)
VALUES (999, '2024-01-01');  -- 错误：customer_id 999 不存在

-- 以下操作会被允许：
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2024-01-01');  -- 成功：customer_id 1 存在

-- 通过查询系统表，可以快速了解表之间的关系
-- MySQL
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 场景：删除用户时，自动删除其所有订单
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE  -- 级联删除
);

-- 删除用户时，其所有订单会自动删除
DELETE FROM customers WHERE customer_id = 1;
-- orders 表中所有 customer_id = 1 的记录会被自动删除

-- 优化器可能知道 orders.customer_id 必然在 customers 中存在
-- 因此可以优化连接顺序和连接算法
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2024-01-01';

场景：向 orders 表插入 10,000 条记录

有外键约束：
- 执行时间：~2.5 秒
- 需要检查 customers 表 10,000 次

无外键约束：
- 执行时间：~0.8 秒
- 无额外检查开销

性能差异：约 3 倍

-- 删除一个用户，该用户有 100,000 条订单记录
DELETE FROM customers WHERE customer_id = 1;
-- 由于 ON DELETE CASCADE，需要：
-- 1. 锁定 customers 表
-- 2. 锁定 orders 表
-- 3. 删除 100,000 条订单记录
-- 4. 可能还需要锁定其他关联表（如 order_items）
-- 整个过程可能需要数秒甚至数分钟

❌ 错误示例：
服务A（用户服务）的数据库：customers 表
服务B（订单服务）的数据库：orders 表
如果 orders.customer_id 引用 customers.customer_id
→ 两个服务无法独立部署和扩展
→ 违反了微服务的数据库隔离原则

✅ 正确示例：
服务A（用户服务）的数据库：customers 表
服务B（订单服务）的数据库：orders 表（包含 customer_id，但不设置外键）
→ 通过服务接口保证数据一致性
→ 两个服务可以独立部署和扩展

-- 假设 orders 表按 order_id 分片到不同的数据库
-- Database 1: orders_0 (order_id % 4 = 0)
-- Database 2: orders_1 (order_id % 4 = 1)
-- Database 3: orders_2 (order_id % 4 = 2)
-- Database 4: orders_3 (order_id % 4 = 3)

-- 如果 orders.customer_id 有外键约束指向 customers 表
-- 问题：customers 表在哪个数据库？如何跨数据库检查外键？
-- 答案：无法实现，必须去除外键约束

-- 有外键约束时，必须按顺序导入：
-- 1. 先导入 customers 表
-- 2. 再导入 orders 表
-- 3. 最后导入 order_items 表

-- 如果顺序错误，导入会失败
-- 如果数据量很大，整个过程会非常慢

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- 执行数据导入
SET FOREIGN_KEY_CHECKS = 1;

-- PostgreSQL
ALTER TABLE orders DISABLE TRIGGER ALL;
-- 执行数据导入
ALTER TABLE orders ENABLE TRIGGER ALL;

-- 修改 customers 表的主键类型
ALTER TABLE customers
MODIFY COLUMN customer_id BIGINT;

-- 问题：
-- 1. 需要检查所有引用 customers.customer_id 的表
-- 2. 可能需要锁定多张表
-- 3. 如果数据量很大，可能需要数小时
-- 4. 期间可能影响业务

-- InnoDB 会自动创建索引，但显式创建仍是好习惯
-- 外键列必须是索引列
CREATE TABLE orders (
    customer_id INT,
    INDEX idx_customer_id (customer_id),  -- 必须创建索引
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 允许在事务结束前暂时违反约束
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
DEFERRABLE INITIALLY DEFERRED;

-- 这样可以在事务中先插入 orders，再插入 customers
BEGIN;
INSERT INTO orders (customer_id) VALUES (1);  -- 暂时允许
INSERT INTO customers (customer_id) VALUES (1);  -- 最后插入
COMMIT;  -- 此时才检查约束

@Service
@Transactional
public class OrderService {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private OrderRepository orderRepository;

    public void createOrder(Long customerId, Order order) {
        // 1. 验证客户是否存在
        Customer customer = customerRepository.findById(customerId)
            .orElseThrow(() -> new CustomerNotFoundException());

        // 2. 创建订单（在事务中）
        order.setCustomerId(customerId);
        orderRepository.save(order);

        // 如果后续操作失败，整个事务回滚
    }
}

public void createOrder(Long customerId, Order order) {
    // 应用层校验
    if (!customerRepository.existsById(customerId)) {
        throw new IllegalArgumentException("Customer not found");
    }

    order.setCustomerId(customerId);
    orderRepository.save(order);
}

用户服务 → 发布事件（用户创建） → 消息队列
                                    ↓
订单服务 ← 订阅事件 ← 消息队列

@Scheduled(cron = "0 0 2 * * ?")  // 每天凌晨2点执行
public void checkDataConsistency() {
    // 查找所有无效的 customer_id
    List<Order> invalidOrders = orderRepository
        .findOrdersWithInvalidCustomerId();

    // 处理无效订单（记录日志、发送告警等）
    invalidOrders.forEach(order -> {
        log.warn("Invalid order found: {}", order.getId());
        // 可以设置为待处理状态，等待人工处理
    });
}

@Entity
public class Order {
    @ManyToOne
    @JoinColumn(name = "customer_id")
    private Customer customer;

    // 使用 @ManyToOne 注解，ORM 会在应用层管理关联关系
}

SET FOREIGN_KEY_CHECKS = 0;
-- 执行操作
SET FOREIGN_KEY_CHECKS = 1;

ALTER TABLE orders DISABLE TRIGGER ALL;
-- 执行操作
ALTER TABLE orders ENABLE TRIGGER ALL;

DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE customer_id = 1;

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

-- 然后可以直接删除
DELETE FROM customers WHERE customer_id = 1;

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL;

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';