Início / dba / Perguntas / 200409
Accepted
Victor
Asked: 2018-03-16 12:44:54 +0800 CST

A restrição de tabela CHECK é executada em exclusões?

  • 772

Acho que não, mas se alguém tiver uma ideia talvez possa me dar uma mão.

Estou tentando restringir a exclusão de uma linha de uma tabela em algumas circunstâncias que exigem consulta em outras tabelas.

Um exemplo

Cada trabalhador (tabela worker) referencia a pessoa física (tabela person). A verificação visa permitir a existência de trabalhadores que irão trabalhar com o seu actual_hapiness_level(da tabela worker) >= base_hapiness_level(da tabela person), para que todas as pessoas subjacentes fiquem satisfeitas .

Há uma exceção importante, é você contratar um trabalhador que faz papel de palhaço , todo mundo fica feliz e, portanto, as restrições anteriores não se aplicam. Tente seguir o exemplo executando uma instrução por vez, lendo os comentários.

E no final você terá inconsistência do ponto de vista "negócio".

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

Como você pode perceber, a verificação não está sendo executada na exclusão do palhaço e isso é um problema neste modelo de domínio de exemplo

postgresql constraint

1 respostas

  1. Best Answer

    Você já tentou ON DELETE RESTRICT? Você pode combinar esta restrição com uma função anexada às tabelas estrangeirasCREATE 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();

    Atualização: verifique uma condição em várias tabelas antes de excluir o registro

    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;
    • 3

relate perguntas