Thread: assertion and company
oh, i 'm sorry, i've given you a blank mail :P i begin to say: Sorry for my bad english ^^ My problem is: i have 2 tables,"editoriale" and "ColaboratoreFisso", in the first i have the foreign key of the second and in this lasttable i have an attribute named MembroCR that can assume only boolean values... Now, i have to be sure that every row in "ColaboratoreFisso", that has the key also inside "editoriale", has the attributeMembroCR=TRUE. So i thought to resolve this with an Assertion, but postgresql doesn't implement it... in what way i can solve my matter? This is my code (my first database, so don't blame me if i have writed too much mistakes :P) CREATE TABLE editoriale ( CodiceE serial PRIMARY KEY, Titolo varchar(100) NOT NULL, Testo text NOT NULL, MembroComitatoRedazione char(16) NOT NULL, FOREIGN KEY (MembroComitatoRedazione) REFERENCES collaboratoreFisso (CodiceFiscaleF) ON DELETE CASCADE ON UPDATE CASCADE); CREATE ASSERTION CONTROLLO_COMITATO CHECK (NOT EXISTS ( SELECT MembroComitatoRedazione FROM editoriale WHERE MembroComitatoRedazione IN (SELECT CodiceFiscaleF FROM collaboratoreFisso WHERE MembroCR = FALSE))); if you can write me an alternative soulution^^ ANOTHER QUESTION: I have some problem with my redundancies: In what way i can implement them? With triggers? I have tried but my code doesn't work, i don't know how to use them :( Thanks for your help! ^^
On Sat, 23 Sep 2006, snake84@inwind.it wrote: > My problem is: > i have 2 tables,"editoriale" and "ColaboratoreFisso", in the first i > have the foreign key of the second and in this last table i have an > attribute named MembroCR that can assume only boolean values... > > Now, i have to be sure that every row in "ColaboratoreFisso", that has > the key also inside "editoriale", has the attribute MembroCR=TRUE. > > So i thought to resolve this with an Assertion, but postgresql doesn't > implement it... in what way i can solve my matter? You can probably check the condition in a set of after triggers. I think you'd need an after insert and update trigger on editoriale to make sure the new value doesn't line up with a MembroCR = false collaboratoreFisso and I think an after update trigger on collaboratoreFisso to prevent MembroCR from being set to false on a row that is referenced. I think something in the general vein of the following untested, might work as a starting point. You'll probably need to do some more work on them. create or replace function cfe1() returns trigger as $$ begin perform 1 from CollaboratoreFisso where CollaboratoreFisso.CodiceFiscaleF = NEW.MembroComitatoRedazione and CollaboratoreFisso.MembroCR = false for read only; if found then raise exception '...'; end if; return NEW; end;$$ language 'plpgsql'; create trigger cfet1 after insert or update on editoriale for each row execute procedure cfe1(); create or replace function cfe2() returns trigger as $$ begin if (NEW.MembroCR = false) then perform 1 from editoriale where editoriale.MembroComitatoRedazione = NEW.CodiceFiscaleF for read only; if found then raise exception '...'; end if; end if; return NEW; end;$$ language 'plpgsql'; create trigger cfet2 after update on CollaboratoreFisso for each row execute procedure cfe2(); > ANOTHER QUESTION: > > I have some problem with my redundancies: > > In what way i can implement them? With triggers? I have tried but my > code doesn't work, i don't know how to use them :( I'm not sure what you mean by redundancies here.