Re: 1 to many relationships

??????? ???????, ????!
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(at)openratings(dot)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
>