Sql 如果另一个表中不存在值列，则插入失败

我正在try 编写一个允许您在建筑物中插入新房间的查询，但如果该建筑物ID不存在，则会失败

            INSERT INTO room (
                building_id, room_name
            ) VALUES (
                $1, 
                CONCAT('Room ', (SELECT count(1) +1 FROM room WHERE building_id = $1))
            )
            RETURNING building_id, id, room_name, version

目前，您可以使用任何随机的建筑物ID进行插入，结果将是房间表中的垃圾数据和大量带有不存在的建筑物ID的Room 1个条目.

try 这样的操作(显然是在事务中)是可行的，但是如果在插入发生之前，在SELECT运行之后，建筑被删除了，会发生什么呢？

            INSERT INTO room (
                building_id, 
                room_name
            ) 
            SELECT 
                $1, 
                CONCAT(
                    'Room ',
                    (SELECT count(1) + 1
                    FROM room AS r 
                    WHERE r.building_id = $1)
                )
            FROM building AS b
            WHERE b.id = $1
            RETURNING building_id, id, room_name, version

虽然这不太可能，但我仍然好奇它是否可以避免. 是否可以改进此查询以完全避免此类并发问题，或者是否有一种方法可以使用外键强制房间插入在事务提交时始终具有有效的Building_id，或者其他一些新的解决方案？