I have the following 2 postgresql tables:
CREATE TABLE my_table_a (
id SERIAL PRIMARY KEY,
my_field_a BOOLEAN NOT NULL
);
CREATE TABLE my_table_b (
id SERIAL PRIMARY KEY,
fk_id INTEGER REFERENCES my_table_a(id),
my_field_b INTEGER
);
I want to make sure the following data integrity constraints always hold true:
my_table_b.my_field_b
should always be greater than zero.my_table_b
is only allowed to be inserted/update if the parent record's my_field_a == True.How do I do it? I am not sure, but I think I will need an update and create trigger on table my_table_b
and I will need and update trigger on my_table_a
. What would those triggers look like?
my_table_b.my_field_b
should always be greater than zero.
Use a check
constraint:
CREATE TABLE my_table_b (
id SERIAL PRIMARY KEY,
fk_id INTEGER REFERENCES my_table_a(id),
my_field_b INTEGER CHECK (my_field_b>0)
);
- A record in
my_table_b
is only allowed to be inserted/update if the parent record'smy_field_a == True
.
Regular constraints can't span multiple tables. You can use a constraint trigger
to enforce this: demo at db<>fiddle
create function trgf_parent_only_with_true_my_field_a()returns trigger as
$f$ declare is_violated boolean:=false;
begin
case TG_TABLE_NAME
when 'my_table_a'
then is_violated:=exists(select from my_table_b
where fk_id=NEW.id);
when 'my_table_b' --allows `NEW.fk_id` reference, absent in 'my_table_a'
then is_violated:=not exists(select from my_table_a
where my_field_a is true
and id=NEW.fk_id);
end case;
if is_violated then raise exception using
message = format('%s on table %I violates foreign key constraint %I'
, TG_OP, TG_TABLE_NAME, TG_NAME),
errcode = 'foreign_key_violation', -- sqlstate 23503
schema = TG_TABLE_SCHEMA,
table = TG_TABLE_NAME,
column = 'fk_id',
detail = format('violating row of %I was %L'
, TG_TABLE_NAME, NEW);
end if;
return new;--doesn't matter if you return `null` or `new` in an `after` trigger
end $f$ language plpgsql;
That's not really a regular foreign_key_violation
, but rather a custom constraint violation. What and how to raise
is up to if and how you catch and handle exceptions.
The function belongs in two triggers: one checks new and changing FKs on my_table_b
, the other makes sure updates on my_table_a
don't introduce a violation on parent's end.
create constraint trigger ctrg_my_table_b_parent_only_with_true_my_field_a
after insert or update on my_table_b
for each row when(new.fk_id is not null)--makes no sense to check empty links
execute function trgf_parent_only_with_true_my_field_a();
create constraint trigger ctrg_my_table_a_parent_only_with_true_my_field_a
after update on my_table_a
for each row when(new.my_field_a is FALSE and old.my_field_a is TRUE)
execute function trgf_parent_only_with_true_my_field_a();
These gets validated after your base fk_id INTEGER REFERENCES my_table_a(id)
constraint (and obviously my_field_a BOOLEAN NOT NULL
). If it gets this far, it's safe to assume the corresponding row must exist and my_field_a
may only be true
or false
, not null
.
A somewhat ugly alternative would be to duplicate the boolean
field in the other table and add a two-key unique index, allowing the foreign key to reference both columns. Then, a check
constraint can guard the my_field_a=True
rule on my_table_b
's side.
demo at db<>fiddle
CREATE UNIQUE INDEX ON my_table_a(id,my_field_a);
CREATE TABLE my_table_b (
id SERIAL PRIMARY KEY,
fk_id INTEGER,
fk_my_field_a BOOLEAN CHECK (fk_my_field_a IS TRUE),
my_field_b INTEGER CHECK (my_field_b>0),
FOREIGN KEY (fk_id,fk_my_field_a) REFERENCES my_table_a(id,my_field_a)
);