Обсуждение: 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.