CODEWITHSUNDEEP
sqlpostgresqlconstraints
bca
bca

Reputation: 464

Using queries in check constraints

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

Upvotes: 0

Views: 81

Answers (2)

The Impaler
The Impaler

Reputation: 48780

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.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246523

If a glass truly can be on multiple floors at the same time, add building_id to floors_glasses and create compound foreign keys from that table to floors and glasses that include the building_id column. That will guarantee your integrity constraint.

You cannot define a check constraint that references other rows or tables, because it would become invalid as soon as you modify those other objects. For example, restoring a dump might fail.

Upvotes: 1

Related Questions