Re: 1 to many relationships

You cannot do that with 'standard' sql.

The problem is that when you are deleting a child, there is no simple
way tell whether it was the last entry remaining that still references
the parent...

You can create a custom trigger on the child table, that would do what
you want...
I don't know the syntax of plpgslq (normally write stuff like that in
C), so I cannot juts give you the sample code... But the idea is
something like

create function cascade_on_parent () returns opaque as '
begin
delete from parent where prikey=old.parent and
not exists (select 1 from child where parent=old.parent limit 1);
return null;
end;
' language 'plpgsql';


And then you do

create constraint trigger cascade_parent after delete or update on child
initially deferred for each row execute procedure cascade_on_parent ();

I hope, it helps...

Dima



Dennis Gearon wrote:
> If I have a child table and a parent table like so:
>
> ---BEGIN TABLE DEFS---------------------------------------------
> create table parents(
>     PriKey serial
> );
> add primary constraint to PriKey;
>
> create table child(
>     parent INT4 NOT NULL
> );
> add foreign key constraint parent refs parent.PriKey ON DELETE CASCADE;
>
>
>
> ---BEGIN RELATIONSHIP DEF----------------------------------------
> They are supposed to have the following relationship:
>
>     parent(1T1)<------->(1TM)children
>
> textually explained as 'One parent has many children and must have at
> least one in order to exist, while a child must have one and only one
> parent in order to exist')
>
>
>
> ---BEGIN PROBLEM DEF / QUESTION --------=------------------------
> For the 1T1 side of the relationship:
>     the table defs will automatically cause the children to be caught or
> killed in the event that their parents disappear, no prob.
>
> For the 1TM side of the relationship:
>     I don't see anything in the docs that says the PARENT will be
> sacrificed if their children don't survive or disappear, and I don't
> even know if there exists in any database the table definition option to
> enforce this.
>
>
> How is this done on different DB's? On Postgres?
>
>
> ---BEGIN GUESS---------------------------------------------------
> Triggers?
>
>
>