Thread: using ID as a key
Hi, I'm new to data base design so please bare with me if my question is so basic.. I'm designing a database, two of the tables are EMPLOYEES and AGENCIES. I need an ID for each of them. I would also need to have the agencyID in the EMPLOYEES table so that I can identify which agency they belong to something like a foreign key. I know postgreSQL does not support it so how do I implement this ? What kind of data type should I use for the ID ? In general I do not know how to implement IDs and use them as keys. I've read the user manual and a bit of the programmer's manual several times but haven't found anything that will apply to this situation. Please Help. Thank You. -Sheila
search for oid and serial or sequence, referential or foreign key in the doc or archive of this list. there are a lot lot very good advices. On Fri, 4 Feb 2000, sheila bel wrote: > Hi, > > I'm new to data base design so please bare with me if my > question is so basic.. > I'm designing a database, two of the tables are EMPLOYEES and > AGENCIES. I need an ID for each of them. I would also need to > have the agencyID in the EMPLOYEES table so that I can identify > which agency they belong to something like a foreign key. I know > postgreSQL does not support it so how do I implement this ? > What kind of data type should I use for the ID ? In general I > do not know how to implement IDs and use them as keys. > I've read the user manual and a bit of the programmer's > manual several times but haven't found anything that will > apply to this situation. > > Please Help. Thank You. > > -Sheila > > > > > ************ >
sheila bel wrote:
>
> Hi,
>
> I'm new to data base design so please bare with me if my
> question is so basic..
> I'm designing a database, two of the tables are EMPLOYEES and
> AGENCIES. I need an ID for each of them. I would also need to
> have the agencyID in the EMPLOYEES table so that I can identify
> which agency they belong to something like a foreign key. I know
> postgreSQL does not support it so how do I implement this ?
> What kind of data type should I use for the ID ?
Sheila: checkout the SERIAL type. It is quite useful for this
purpose, as in...
CREATE TABLE foo (
key_id SERIAL,
...
CREATE TABLE bar (
key_id SERIAL,
foo_key_id INTEGER NOT NULL, -- foreign key to foo
...
Lots of discussion in the archive on how to retrieve a new value for
the purpose of a foreign key (keywords: SERIAL, nextval, sequence).
http://www.postgresql.org/docs/postgres/datatype.htm#AEN842
Cheers,
Ed Loehr
Take a look at Bruce's book at http://www.postgresql.org/docs/awbook.html He explains oid's and sequences extremely well, and that might be what you are looking for. -----Original Message----- From: owner-pgsql-general@postgreSQL.org [mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of sheila bel Sent: Friday, February 04, 2000 11:41 AM To: pgsql-general@postgreSQL.org Subject: [GENERAL] using ID as a key Hi, I'm new to data base design so please bare with me if my question is so basic.. I'm designing a database, two of the tables are EMPLOYEES and AGENCIES. I need an ID for each of them. I would also need to have the agencyID in the EMPLOYEES table so that I can identify which agency they belong to something like a foreign key. I know postgreSQL does not support it so how do I implement this ? What kind of data type should I use for the ID ? In general I do not know how to implement IDs and use them as keys. I've read the user manual and a bit of the programmer's manual several times but haven't found anything that will apply to this situation. Please Help. Thank You. -Sheila ************
I recommend you read Momjian's online book, very informative and provides all the necessary information about using various kinds of id's: http://www.postgresql.org/docs/aw_pgsql_book/index.html As for referential integrity using foreign keys, you can add this functionality to postgresql using triggers. An example of this is available in the contrib/spi directory, look for refint.* Marc On Fri, 4 Feb 2000, sheila bel wrote: > Hi, > > I'm new to data base design so please bare with me if my > question is so basic.. > I'm designing a database, two of the tables are EMPLOYEES and > AGENCIES. I need an ID for each of them. I would also need to > have the agencyID in the EMPLOYEES table so that I can identify > which agency they belong to something like a foreign key. I know > postgreSQL does not support it so how do I implement this ? > What kind of data type should I use for the ID ? In general I > do not know how to implement IDs and use them as keys. > I've read the user manual and a bit of the programmer's > manual several times but haven't found anything that will > apply to this situation. > > Please Help. Thank You. > > -Sheila > > > > > ************ > >
Marc Tardif wrote: > > I recommend you read Momjian's online book, very informative and provides > all the necessary information about using various kinds of id's: > http://www.postgresql.org/docs/aw_pgsql_book/index.html In glancing at Momjian's stuff on this just now, I noticed the book could leave the first-time reader with the impression that both OIDs and sequences (ie, SERIAL) are viable choices for use as primary keys (unintentionally?). Sequences and SERIALs are not mentioned until after a long example of how to do primary keys with OIDs. My recollection is that OIDs have fatal drawbacks in this context, and should NEVER be used as primary keys for serious databases. If that is true, the example of OIDs as primary keys should be removed from the book, or at the very least put after an example using sequences/serials. That section appears bound to lead many astray into the use of OIDs, even though there is a brief section following on OID "limitations"... Can anyone confirm/correct that notion? > As for referential integrity using foreign keys, you can add this > functionality to postgresql using triggers. An example of this is > available in the contrib/spi directory, look for refint.* As Jan Wieck (v7.0 foreign key author) pointed out to me earlier, concurrent transactions prevent 100% reliable referential integrity via triggers, at least with pl/pgsql, though you can get pretty close if you're guarding against the problem scenarios at the application level. I confess ignorance on refint.*... Cheers, Ed Loehr
> Marc Tardif wrote: > > > > I recommend you read Momjian's online book, very informative and provides > > all the necessary information about using various kinds of id's: > > http://www.postgresql.org/docs/aw_pgsql_book/index.html > > In glancing at Momjian's stuff on this just now, I noticed the book > could leave the first-time reader with the impression that both OIDs > and sequences (ie, SERIAL) are viable choices for use as primary keys > (unintentionally?). Sequences and SERIALs are not mentioned until > after a long example of how to do primary keys with OIDs. Don't glance. Read the whole chapter. It will be clear. If people get the wrong impression by reading part of the chapter, well, it's their fault. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
ya, and there is always this listing to ask ;-) On Fri, 4 Feb 2000, Bruce Momjian wrote: > > Marc Tardif wrote: > > > > > > I recommend you read Momjian's online book, very informative and provides > > > all the necessary information about using various kinds of id's: > > > http://www.postgresql.org/docs/aw_pgsql_book/index.html > > > > In glancing at Momjian's stuff on this just now, I noticed the book > > could leave the first-time reader with the impression that both OIDs > > and sequences (ie, SERIAL) are viable choices for use as primary keys > > (unintentionally?). Sequences and SERIALs are not mentioned until > > after a long example of how to do primary keys with OIDs. > > Don't glance. Read the whole chapter. It will be clear. If people get > the wrong impression by reading part of the chapter, well, it's their > fault. > > -- > Bruce Momjian | http://www.op.net/~candle > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ************ >
Bruce Momjian wrote: > > > Marc Tardif wrote: > > > > > > I recommend you read Momjian's online book, very informative and provides > > > all the necessary information about using various kinds of id's: > > > http://www.postgresql.org/docs/aw_pgsql_book/index.html > > > > In glancing at Momjian's stuff on this just now, I noticed the book > > could leave the first-time reader with the impression that both OIDs > > and sequences (ie, SERIAL) are viable choices for use as primary keys > > (unintentionally?). Sequences and SERIALs are not mentioned until > > after a long example of how to do primary keys with OIDs. > > Don't glance. Read the whole chapter. It will be clear. If people get > the wrong impression by reading part of the chapter, well, it's their > fault. What's clear is this is a really lousy answer to a specific question, and that you're not too open to feedback on the book. The answer, as Momjian himself points out in the section right after the one in which he shows you how to use OIDs as primary keys, is that OIDs are NOT DUMPED AT BACKUP TIME by default. That would mean your newly-dumped database would be corrupt upon creation if you used OIDs. Most people wouldn't appreciate that very much. Alas, he does mention that you may twiddle this flag to have OIDs dumped...uh...er...nevermind...that documentation doesn't exist yet. Give me a break. The book leads people astray in this regard, Mr. Momjian. If you'd want to spend time reading posts from people requesting help in salvaging databases corrupted by their book-inspired error, rather than taking a hint on a confusing presentation, that's your choice. Cheers, Ed Loehr
I've been using OID's for a while now, and I haven't had any trouble at all so far. Because this is an active database, it has been important to keep the current data intact. Nevertheless, I have managed to keep everything in order even to the last OID. To dump with oid's, simply add the -o flag to pg_dump (as explained in the pg_dump manpage). In my situation, I particularly appreciate OID's because they have little overhead and they simply do the job. More specifically, they come in handy for creating a fulltextindex, which I recommend you take a look at in the contributions. Although I don't completely agree with Ed Loehr, he does have a point though. Keeping track of OID's can be more complicated than using an id field in a table. The major problem being that you can't change an OID and you can't predict OID's either. As Bruce was telling me, OID's are gathered in batches of 100 by each backend, so you never really know what you'll end up with. Regardless of these obstacles, it is not wrong to use OID's and you should be open to this option. My 2 cents, Marc On Fri, 4 Feb 2000, Ed Loehr wrote: > Bruce Momjian wrote: > > > > > Marc Tardif wrote: > > > > > > > > I recommend you read Momjian's online book, very informative and provides > > > > all the necessary information about using various kinds of id's: > > > > http://www.postgresql.org/docs/aw_pgsql_book/index.html > > > > > > In glancing at Momjian's stuff on this just now, I noticed the book > > > could leave the first-time reader with the impression that both OIDs > > > and sequences (ie, SERIAL) are viable choices for use as primary keys > > > (unintentionally?). Sequences and SERIALs are not mentioned until > > > after a long example of how to do primary keys with OIDs. > > > > Don't glance. Read the whole chapter. It will be clear. If people get > > the wrong impression by reading part of the chapter, well, it's their > > fault. > > What's clear is this is a really lousy answer to a specific question, > and that you're not too open to feedback on the book. > > The answer, as Momjian himself points out in the section right after > the one in which he shows you how to use OIDs as primary keys, is that > OIDs are NOT DUMPED AT BACKUP TIME by default. That would mean your > newly-dumped database would be corrupt upon creation if you used > OIDs. Most people wouldn't appreciate that very much. Alas, he does > mention that you may twiddle this flag to have OIDs > dumped...uh...er...nevermind...that documentation doesn't exist yet. > Give me a break. > > The book leads people astray in this regard, Mr. Momjian. If you'd > want to spend time reading posts from people requesting help in > salvaging databases corrupted by their book-inspired error, rather > than taking a hint on a confusing presentation, that's your choice. > > Cheers, > Ed Loehr >
Marc Tardif wrote: > > I've been using OID's for a while now, and I haven't had any trouble at > all so far. Because this is an active database, it has been important to > keep the current data intact. Nevertheless, I have managed to keep > everything in order even to the last OID. To dump with oid's, simply add > the -o flag to pg_dump (as explained in the pg_dump manpage). > > In my situation, I particularly appreciate OID's because they have little > overhead and they simply do the job. More specifically, they come in handy > for creating a fulltextindex, which I recommend you take a look at in the > contributions. > > Although I don't completely agree with Ed Loehr, he does have a point > though. Keeping track of OID's can be more complicated than using an id > field in a table. The major problem being that you can't change an OID and > you can't predict OID's either. As Bruce was telling me, OID's are > gathered in batches of 100 by each backend, so you never really know what > you'll end up with. Regardless of these obstacles, it is not wrong to use > OID's and you should be open to this option. Now *that* is a helpful answer to my original question. Seems OIDs are viable primary keys, provided you don't get bit by failing to add the -o flag to your pg_dumps and don't mind the unpredictability. I appreciate the info. As for the tradeoffs you mention, I have found it pretty convenient during development to be able to glance at data and tell the order of record creation from the serial id, but I could live without that (I'm also time-stamping record creations). I've never needed to change a serial/sequence-generated *primary* key, so I doubt that read-only constraint would bite you with OIDs. And I can see that saving 4 bytes per record could be pretty significant for some large databases or systems with scarce resources. Cheers, Ed Loehr