sql - Using queries in check constraints

Question

I'm trying to use check constraint with a row while I only have the private key. I don't know if there is another way to check this kind of stuff, I'm fairly new to SQL, so I'm also open to suggestions.

Here is a minimal example:

I have following tables:

Buildings:
building_id: int GENERATED PRIMARY KEY

Floors:
floor_id: int GENERATED PRIMARY KEY
floor_nr: int
building_id: int FOREIGN KEY REFERENCES Buildings (building_id)

Glasses:
glass_id: int GENERATED PRIMARY KEY
building_id: int FOREIGN KEY REFERENCES Buildings (building_id)

Floors_Glasses:
floor_id: int FOREIGN KEY REFERENCES Floors (floor_id)
glass_id: int FOREIGN KEY REFERENCES Glasses (building_id)

When I want to move a glass to the floor, I need to check if

(SELECT building_id FROM Floors WHERE floor_id = floor.floor_id) == glass.building_id

So I don't have to repeat the query above for each query I write.

Edit:

I end up using constraint triggers as following:

CREATE CONSTRAINT TRIGGER "trigger name"
    AFTER UPDATE OF "column name" ON "table name"
    FOR EACH ROW EXECUTE PROCEDURE "procedure name"

And raise exceptions as

raise exception 'message' using errcode = 'restrict_violation';

question from:https://stackoverflow.com/questions/65847089/using-queries-in-check-constraints

Answer 1

You can always enforce this rule at the app level. However, I'm always distrusful of the apps (since they are full of bugs all the time), so I personally prefer to enforce the rules at the database level, whenever possible.

It's a bit more startup work but saves you a lot of time later on, since it prevents data corruption issues from the start.

You can do:

create table buildings (
  building_id int primary key not null
);

create table floors (
  building_id int not null references buildings (building_id),
  floor_id int not null,
  floor_nr int,
  primary key (building_id, floor_id)
);

create table glasses (
  building_id int not null references buildings (building_id),
  glass_id int not null,
  glass_price int,
  primary key (building_id, glass_id)
);

create table floor_glasses (
  building_id int not null,
  floor_id int not null,
  glass_id int not null,
  primary key (building_id, floor_id, glass_id),
  foreign key (building_id, floor_id) references floors (building_id, floor_id),
  foreign key (building_id, glass_id) references glasses (building_id, glass_id)
);

The key concept is in the last table. There's only one column building_id that is used in two foreign keys references. This reference sharing ensures the floors and glasses referenced in the last table always belong to the same building.