Обсуждение: 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