Thread: FOREIGN KEY revisited

After seeing the work done to implement SERIAL types (automatic creation
of sequences) would it not be possible to do the same thing with FOREIGN
KEYs by automatically creating 2 triggers for each FOREIGN KEY statement?

Can someone with more intimate knowledge of the parser/grammar sections of
the code comment on this?

--
| Matthew N. Dodd  |This space | '78 Datsun 280Z | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |is for rent| '84 Volvo 245DL | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?  |

> After seeing the work done to implement SERIAL types (automatic
> creation of sequences) would it not be possible to do the same thing
> with FOREIGN KEYs by automatically creating 2 triggers for each
> FOREIGN KEY statement?

It's possible, I suppose, if the code which the triggers execute can be
completely generic (so it is only the equivalent of some "CREATE
TRIGGER" SQL code which must be executed for a new foreign key).

The SERIAL type built on the work I had done to implement PRIMARY KEY
and was pretty trivial to do. However, primary keys are cleaner than the
serial type because the underlying implementation for keys just created
a unique index, which Postgres guarantees to remove if the table is
destroyed. For the serial type, a sequence is created which is _not_
tied directly to the table, and which does _not_ get automatically
destroyed if the table is destroyed.

Hmm, speaking of the serial type, I wonder if I could declare a trigger
to clean up sequences when I destroy a table...

Back to your question: Postgres probably does not remove trigger
functions if a table is destroyed, but that isn't a problem if the
trigger function is generic code which will be reused anyway.

Vadim has been thinking about how to do foreign keys, but I can't
remember if it was via triggers or some other means.

                     - Tom

On Fri, 28 Aug 1998, Thomas G. Lockhart wrote:
> Back to your question: Postgres probably does not remove trigger
> functions if a table is destroyed, but that isn't a problem if the
> trigger function is generic code which will be reused anyway.

Even if you could create a trigger to remove the triggers used to
implement FOREIGN KEYs, how would you remove the trigger that removes the
triggers? :)

What is needed is some way of identifying a particular trigger with a
table and dropping it if the table goes away.

This could be sufficiently genericized to allow any dependant object to be
removed when its dependancy is droped.

> Vadim has been thinking about how to do foreign keys, but I can't
> remember if it was via triggers or some other means.

I'm the lone PostgreSQL advocate at a rather large backbone provider.  I'd
love to keep using postgres, but lack of FOREIGN KEYs is probably the
biggest nail in the coffin.  Oracle would be overkill for this project but
it would make DB maintainence much easier as I wouldn't have to keep track
of all these triggers I'm using to implement FOREIGN KEYs.  The
applications that use the DB are considered 'hostile' and I need to do all
the constraint checking I can to make sure users don't do stupid things so
not using FOREIGN KEYs isn't a solution.

--
| Matthew N. Dodd  |This space | '78 Datsun 280Z | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |is for rent| '84 Volvo 245DL | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?  |

Thomas G. Lockhart wrote:
>
> Back to your question: Postgres probably does not remove trigger
> functions if a table is destroyed, but that isn't a problem if the
> trigger function is generic code which will be reused anyway.

Both functions in spi/refint.c are generic ones. They should be
part of core system, not of contrib, to be used for FOREIGN KEY
in CREATE TABLE...
No problems with using them.

>
> Vadim has been thinking about how to do foreign keys, but I can't
> remember if it was via triggers or some other means.
                     ^^^^^^^^^^^^
No. Via indices.

Vadim