Question 273225 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Fail an INSERT if value column does not exist in another table

I’m trying to write a query that would allow you to insert a new Room in a Building, but fail if that building ID does not exist

            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

Currently you can do the insert with any random building id and it would end up as garbage data in the room table and lots of Room 1 entries with non-existing building ids.

Trying something like this (obviously inside a transaction) works, but what happens if the building gets deleted just after the SELECT runs before the INSERT happens?

            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

It’s very unlikely though, but I’m still curious if it can be avoided.
Can this query be improved to completely avoid such concurrency issues or is there a way to use foreign keys to enforce the room insert to always have a valid building_id when the transaction commits or maybe some other novel solution?

Tags:

2

Answers


  1. 0

    This was done on SQL Server 2022. I could not think of a cleaner way to do this, but it works in all of my tests. It actually goes above and beyond your request in that it also checks if that room already exists in the building. So, in short, if the building exists, and that room does not exist, it will do the insert (that is the goal here of this sql).

    I also agree with the folks on referential integrity. However folks, we do not know what this person’s permission is on the database, nor if they already asked to have this done, or maybe they’re a student working on a midterm project. This is EXAMPLE code.

    declare @tbl_room table
(
    building_id int not null,
    room_name varchar(255) not null
)


-- insert some test values
insert into @tbl_room
select  1, 'room 1' union all
select  2, 'room 1' union all
select  2, 'room 2' union all
select  4, 'room 1' union all
select  4, 'room 2' union all
select  5, 'room 1'


-- declare a set of vars to simulate what we want to insert
declare @building_id int = 4
declare @room_name varchar(255) = 'room 2'


-- check if the building and room exist
select  b.building_id
from    @tbl_room b
where   b.building_id = @building_id        
group by    b.building_id

union all

select  rm.building_id
from    @tbl_room rm
where   rm.building_id = @building_id
and     rm.room_name = @room_name
group by    rm.building_id


-- we know we have a building but not the room
if(@@rowcount = 1)  
    begin
        insert into @tbl_room
        select  @building_id,
                @room_name
    end


select  r.building_id,
        r.room_name
from    @tbl_room r
order by    r.building_id

    enter image description here

    Login or Signup to reply.
  2. 0

    A simple constraint would stop a room from being inserted if the building did not exists.

    Try adding a constraint – something like this would give you something to test with.

    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')
;

    then the following code will fail.

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

    which results with the error

    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".

    Here is a SQLFiddle link to play with:
    http://sqlfiddle.com/#!17/babbf7/1

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search