Re: 1 to many relationships

From: Dima Tkach <dmitry(at)openratings(dot)com>
To: Dennis Gearon <gearond(at)cvc(dot)net>
Subject: Re: 1 to many relationships
Date: 2003-02-23 17:30:40
Message-ID: 3E590540.4040501@openratings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?
>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2003-02-23 17:35:49 Re: Strange error (Socket command option unknown)
Previous Message Tom Lane 2003-02-23 16:52:37 Re: Strange error (Socket command option unknown)