Thread

1. FOREIGN KEY revisited

   Matthew N. Dodd <winter@jurai.net> — 1998-08-27T21:45:21Z

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

2. Re: [HACKERS] FOREIGN KEY revisited

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-08-28T06:53:57Z

   > 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
   
   

3. Re: [HACKERS] FOREIGN KEY revisited

   Matthew N. Dodd <winter@jurai.net> — 1998-08-28T06:58:08Z

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

4. Re: [HACKERS] FOREIGN KEY revisited

   Vadim Mikheev <vadim@krs.ru> — 1998-08-28T07:31:55Z

   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