Does the table constraint CHECK gets executed on deletions?

Question

I guess not but if someone has an idea perhaps could give me a hand.

I'm trying to restrict the deletion of a row of a table under some circumstances that requires to query against other tables.

An example

Each worker (table worker) references the physical person (table person). The check aims to allows the existence of workers that will work with his actual_hapiness_level (from table worker) >= base_hapiness_level (from table person), so all the underlying persons are happy.

There is an important exception, is you hire a worker that acts a clown, everybody is happy and therefore the former restrictions doesn't apply. Try to follow the example running one statement at the time, reading the comments.

And the end you will get inconsesty from a "business" point of view.

create table person (
    id serial primary key,
    base_hapiness_level int,
    name text
);
create table worker (
    id serial primary key,
    person_id int,
    actual_hapiness_level int,
    acts_as_clown bool default false,
    CONSTRAINT person_id_fkey FOREIGN KEY (person_id)
          REFERENCES person (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE OR REPLACE FUNCTION is_worker_happy_according_to_his_personal_values (bigint,bigint) RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing things_are_okey(person_id= %,base_hapiness_level= %)', $1, $2;
    RETURN ($2 >= (select p.base_hapiness_level from person p where p.id = $1));
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION is_there_a_clown_in_there_to_handle_the_unhappiness () RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing is_there_a_clown_in_there_to_handle_the_unhappiness';
    RETURN EXISTS (select * from worker where acts_as_clown is true);
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE worker ADD CONSTRAINT person_can_work check (
    is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) OR
    (NOT is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) AND is_there_a_clown_in_there_to_handle_the_unhappiness())
);

-- create 3 persons
insert into person (base_hapiness_level,name) values (50,'robert');
insert into person (base_hapiness_level,name) values (10,'cris');
insert into person (base_hapiness_level,name) values (0,'the_clown');

-- insertion 1 is okey, as there is no clow required to the hapiness level of robert
insert into worker (actual_hapiness_level,person_id) values (50,(select id from person where name = 'robert'));

-- insertion 2 goes wrong, as there is no clown to handle the unhapiness of crish
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));

-- let's hire a clown.....
insert into worker (actual_hapiness_level,person_id,acts_as_clown) values (100,(select id from person where name = 'the_clown'),true);

-- try to insert cris again (with the clown is alright)
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));
-- and works ;)

-- but if we kick the clown, now there is no one to handle the unhapinness what a mess!! what about the check :(!!!
delete from worker where person_id = (select id from person where name = 'the_clown')

As you can realize, the check is not beign performing on the deletion of the clown and that is a problem in this example domain model

Answer 1

Have you tried ON DELETE RESTRICT ? You can combine this constraint with a function attached to the foreign tables CREATE TRIGGER ON DELETE

CREATE TABLE my_table (
  foreign_key_id uuid REFERENCES my_foreign_table ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM my_table 
    WHERE foreign_key_id IS NULL 
    AND foreign_key_id = OLD.foreign_key_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foreign_row_trigger BEFORE DELETE ON my_foreign_table 
    FOR EACH ROW EXECUTE PROCEDURE foreign_object_delete();

Update: Check a condition in several tables before deleting the record

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
   IF SELECT exists(SELECT 1 FROM table_x WHERE column_x = 'SAMPLE') THEN
      DELETE FROM my_table 
      WHERE foreign_key_id IS NULL 
      AND foreign_key_id = OLD.foreign_key_id;
   END IF;
      RETURN OLD;
END;
$$ LANGUAGE plpgsql;