databasepostgresqltriggers

How to setup a constraint on this child table?


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:

  1. my_table_b.my_field_b should always be greater than zero.
  2. A record in 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?


Solution

    1. 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)
    );
    

    1. A record in my_table_b is only allowed to be inserted/update if the parent record's my_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)
    );