如何确保Room表插入时building_id有效并规避并发问题？

1. 外键约束（最推荐的原生方案） #

直接给room表的building_id字段添加外键关联到building表的主键，这是数据库层面的强约束，从根源上杜绝无效数据：

ALTER TABLE room
ADD CONSTRAINT fk_room_building
FOREIGN KEY (building_id) REFERENCES building(id)
ON DELETE RESTRICT; -- 可选：根据业务需求，也可使用ON DELETE CASCADE等规则

添加后，任何插入无效building_id的操作都会被数据库直接拒绝并返回错误。而且数据库会自动处理并发场景——哪怕你插入前building存在，但提交时被其他事务删除，数据库会检测到不一致并回滚插入操作，绝对保证数据一致性。

2. 原子化INSERT ... SELECT语句（替代拆分的事务操作） #

如果因特殊原因不能用外键，把检查和插入合并成单条原子SQL，彻底避免并发间隙问题：

INSERT INTO room (building_id, room_name)
SELECT :target_building_id, 'Room 1'
FROM building
WHERE id = :target_building_id
LIMIT 1;

这条语句的逻辑是：仅当building表中存在对应ID的记录时，才会插入room数据。因为是单条SQL，数据库会将其作为原子操作执行，不会出现“SELECT完building还在、INSERT前被删”的中间状态。执行后通过受影响行数判断结果：若受影响行数为0，说明building_id无效，插入失败。

3. 带悲观锁的事务方案 #

如果必须采用事务+SELECT+INSERT的模式，在SELECT时给目标building记录加排他锁，阻止其他事务删除它：

BEGIN TRANSACTION;

-- 锁定目标building记录，无结果则直接终止事务
SELECT id FROM building WHERE id = :target_building_id FOR UPDATE;

-- 仅当上述查询有结果时，执行插入
INSERT INTO room (building_id, room_name) VALUES (:target_building_id, 'Room 1');

COMMIT;

注意：如果SELECT返回空，就不要执行INSERT。加FOR UPDATE后，其他事务要删除该building记录会被阻塞，直到当前事务提交或回滚，彻底避免并发删除导致的无效插入。

方案对比 #

• 外键约束：首选方案，数据库原生支持，维护成本极低，一致性保障最强，无需额外业务代码。
• INSERT ... SELECT：适合禁用外键的场景，原子操作性能优，代码简洁。
• 悲观锁事务：适配复杂业务逻辑，但会增加锁开销，可能影响并发性能，需额外处理SELECT结果判断。

内容的提问来源于stack exchange，提问作者Jan Vladimir Mostert