面试题：MySQL外键约束下的复杂操作

Question

假设有两个表，一个是`orders`表，包含`order_id`（主键）和`customer_id`；另一个是`customers`表，包含`customer_id`（主键）和`customer_name`。现在要更新`customers`表中的`customer_id`，同时确保`orders`表中的参照完整性不受破坏，应该如何操作？请详细说明步骤并给出可能用到的SQL语句。

Answer 1

1. 添加临时列：
   • 在orders表中添加一个临时列来存储更新前的customer_id。假设临时列名为temp_customer_id。

   ALTER TABLE orders ADD COLUMN temp_customer_id INT;
   

   • 将当前customer_id的值复制到临时列中。

   UPDATE orders SET temp_customer_id = customer_id;
   

2. 更新customers表：
   • 假设要将customers表中customer_id为old_customer_id的值更新为new_customer_id。

   UPDATE customers SET customer_id = new_customer_id WHERE customer_id = old_customer_id;
   

3. 更新orders表：
   • 根据临时列的值，更新orders表中的customer_id。

   UPDATE orders SET customer_id = new_customer_id WHERE temp_customer_id = old_customer_id;
   

4. 删除临时列：
   • 操作完成后，删除orders表中的临时列。

   ALTER TABLE orders DROP COLUMN temp_customer_id;
   

另一种方法（使用事务和外键约束）：

1. 确保外键约束：
   • 确保orders表的customer_id列与customers表的customer_id列之间存在外键约束。如果没有，可以添加外键约束。

   ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
   

2. 开始事务：

   START TRANSACTION;
   

3. 更新customers表：
   • 假设要将customers表中customer_id为old_customer_id的值更新为new_customer_id。

   UPDATE customers SET customer_id = new_customer_id WHERE customer_id = old_customer_id;
   

4. 更新orders表：
   • 根据customers表的更新，更新orders表中的customer_id。

   UPDATE orders SET customer_id = new_customer_id WHERE customer_id = old_customer_id;
   

5. 提交事务：

   COMMIT;
   

注意：在实际操作中，根据数据库管理系统的不同，外键约束的级联更新功能可能也可以实现此需求，例如在MySQL中，可以在创建外键时指定ON UPDATE CASCADE，这样当customers表中的customer_id更新时，orders表中的customer_id会自动更新。

-- 创建表时设置外键并开启级联更新
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON UPDATE CASCADE
);

然后直接更新customers表中的customer_id，orders表中的customer_id会自动同步更新。

UPDATE customers SET customer_id = new_customer_id WHERE customer_id = old_customer_id;