Postgres Pro
Downloads
Home   >   mailing lists

Thread: 1 to many relationships

1 to many relationships

From
Dennis Gearon
Date:
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?



--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

Re: 1 to many relationships

From
Dima Tkach
Date:
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?
>
>
>

Re: 1 to many relationships

From
Dennis Gearon
Date:
??????? ???????, ????!
Bolshoi Spaciba, Dima,
thank you very  much Dima!

I will reply to your other letter later today.

2/23/2003 9:30:40 AM, Dima Tkach <dmitry@openratings.com> wrote:

>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?
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>