Обсуждение: Foreign Key Implementation
Hi All, I had my first go at Postgres today. So far so good. But I need to know how to implement foreign keys using postgres. I did not find anything in the documentation and several searches on "foreign" key produced nothing. Does Postgres have foreign key functionality. And if not why and when??? thanks for your kind consideration to a naive, novice. Catharine Catharine Drozdowski Emery WorldWide Oracle Database Administrator PHONE: (503) 450 5712
On Tue, Apr 04, 2000 at 11:19:56PM -0000, Drozdowski, Catharine I allegedly wrote: > Hi All, > I had my first go at Postgres today. So far so good. But I need to know how > to implement foreign keys using postgres. I did not find anything in the > documentation and several searches on "foreign" key produced nothing. > > Does Postgres have foreign key functionality. And if not why and when??? I'm no expert on this, but PostgreSQL does have a foreign key implementation, albeit a somewhat cumbersome IMO. You should look the refint (referential integrity) stuff in the contrib directory of the sourcetree. Since I don't have a sourcetree lying around at the moment, I can't verify the exact name of the directory. The goodies may be located in the SDI (or something similar) directory. What it boils down to is that you use triggers to enforce referential integrity. It works just fine, but is a bit labourious to setup. My 0.02 EU, Mathijs
Does anyone know how much longer till 7.0 is released? Thanks, -- Daniel Spratlen spratlen@arches.uga.edu
On Wed, 5 Apr 2000, Daniel Spratlen wrote: > Does anyone know how much longer till 7.0 is released? We're looking for an April 15th release *cross fingers* We're just getting ready to release a beta5 (beta4 is available, but not announced, as beta5 will most likely be out Friday) ... this one will require an initdb, and *should* be the last one that requires it before the release ... after that, it will mostly be doc updates before the official release, unless someone pops up with an unknown bug ...
Hi, I! Wednesday, April 05, 2000, 3:19:56 AM, you wrote: DCI>But I need to know how to implement foreign keys using postgres. I did not find anything in the DCI> documentation and several searches on "foreign" key produced nothing. There is no way to implement foreign keys. Data will always be copy when you'll try to pass it as FK. DCI> Does Postgres have foreign key functionality. And if not why and when??? postgresql 7.0 coming So, with posgres 6.* you can implement only relational integrity. I Use Erwin and it's macroses to implement automatic RI creation. Regards, Alexey V. Meledin InterForge Developers Group, Saint-Petersburg look_to: <www.etcompany.ru><www.businessweb.ru> <www.inplan.spb.ru><www.pia.ru>>>>>>>>>>>>>>>>> mail_to: <avm@webclub.ru><nick_as: <cureman>>>>
> I'm no expert on this, but PostgreSQL does have a foreign key implementation, > albeit a somewhat cumbersome IMO. You should look the refint (referential > integrity) stuff in the contrib directory of the sourcetree. Since I don't > have a sourcetree lying around at the moment, I can't verify the exact name > of the directory. The goodies may be located in the SDI (or something similar) > directory. What it boils down to is that you use triggers to enforce referential > integrity. It works just fine, but is a bit labourious to setup. The old contrib/spi/refint.c is obsolete now. You should use the new implementation. Go to www.postgresql.org and search for Jan Wieck's TODO page about reference integrity, you will find the exact syntax there. Examples can be found in the mailing list, also on www.postgresql.org. Regards, Zoltan
But don't forget that in 7.0, which we see is hopefully coming in ten days' time, foreign keys are almost entirely supported. Look at the mailing list of pgsql-general for last month, and see several people replying to a question I posted with the syntax... Yrs, Moray ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk ----- Original Message ----- From: "Alexey V. Meledin" <avm@webclub.ru> To: "Drozdowski, Catharine I" <Drozdowski.Catharine@emeryworld.com>; <pgsql-sql@postgresql.org> Sent: Wednesday, April 05, 2000 7:27 AM Subject: Re: [SQL] Foreign Key Implementation > Hi, > I! > > Wednesday, April 05, 2000, 3:19:56 AM, you wrote: > > > DCI>But I need to know how to implement foreign keys using postgres. I did not find anything in the > DCI> documentation and several searches on "foreign" key produced nothing. > There is no way to implement foreign keys. Data will always be copy > when you'll try to pass it as FK. > DCI> Does Postgres have foreign key functionality. And if not why and when??? > postgresql 7.0 coming > > So, with posgres 6.* you can implement only relational integrity. I > Use Erwin and it's macroses to implement automatic RI creation. > > > Regards, Alexey V. Meledin > InterForge Developers Group, Saint-Petersburg > look_to: <www.etcompany.ru><www.businessweb.ru> > <www.inplan.spb.ru><www.pia.ru>>>>>>>>>>>>>>>>> > mail_to: <avm@webclub.ru><nick_as: <cureman>>>> > > >
The documentation in 7.0 is rather obsolete, sorry for inconvenience. The release will hopefully contain all information. Here is an example for basic foreign key definition: create table group(id serial, name varchar(20)); create table member(id serial, name varchar(30), group int4references group(id)); The following has the same result: create table group(id serial, name varchar(20)); create table member(id serial, name varchar(30), group int4,foreign key id references group(id)on update restrict on deleterestrict); I hope this helps. Regards, Zoltan