postgresql 如果值列在另一个表中不存在，则使故障排除失败

ecr0jaav 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(167)

我正在尝试编写一个查询，允许您在Building中插入一个新的Room，但如果该Building 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条目。
尝试这样的方法（显然是在事务中）是可行的，但是如果在SELECT运行之后，在SQL发生之前，构建被删除了，会发生什么呢？

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，或者其他一些新颖的解决方案？

postgresql

来源：https://stackoverflow.com/questions/77370217/fail-an-insert-if-value-column-does-not-exist-in-another-table

1条答案

按热度按时间

btxsgosb1#

如果建筑物不存在，则简单的约束将阻止插入房间。
尝试添加一个约束--像这样的东西会给你给予一些东西来测试。

CREATE TABLE building(
    id int UNIQUE, 
    name varchar(15)
);

CREATE TABLE room(
    id int UNIQUE, 
    building_id int, 
    name varchar(10),
    CONSTRAINT foreign_key_room_building
        FOREIGN KEY(building_id) 
        REFERENCES building(id)
        ON DELETE CASCADE,
    CONSTRAINT unique_id_building_id
        UNIQUE (id, building_id)    
);

INSERT INTO building
    (id, name)
VALUES
    (1, 'orange building'),
    (2, 'red building')
;
     
INSERT INTO room
    (id, building_id, name)
VALUES
    (1, 1, 'front room'),
    (2, 1, 'back room')
;

字符串
下面的代码将失败。

INSERT INTO room
    (id, building_id, name)
VALUES
    (3, 3, 'front room')
;

型
这导致了错误

ERROR: insert or update on table "room" violates foreign key constraint "foreign_key_room_building" Detail: Key (building_id)=(3) is not present in table "building".

型
下面是一个SQLFiddle链接：http：//sqlfiddle.com/#！17/babbf 7/1

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 如果值列在另一个表中不存在，则使故障排除失败

1条答案

相关问题

热门标签

最新问答