Re: 1 to many relationships
От | Dennis Gearon |
---|---|
Тема | Re: 1 to many relationships |
Дата | |
Msg-id | QPYXWT853HFJFTRUPQOYT83S9323Z.3e590da6@cal-lab обсуждение исходный текст |
Ответ на | Re: 1 to many relationships (Dima Tkach <dmitry@openratings.com>) |
Список | pgsql-general |
??????? ???????, ????! 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 >
В списке pgsql-general по дате отправления: