Обсуждение: PRIMARY KEYS
Hi; I'm pretty new in Postgres. I've created several tables but I didn't care about PRIMARY KEYS in the begining. Now I woul like to add them to the tables. I've read that PostgreSQL adds an automatic column called OID to each table; but when I do a SELECT, the OID column doesn't not appear. Is this OID column a PRIMARY KEY by default? Can I see it? How can I see what column is the primary key in a table? Can I select, at any moment a different Prymary Key? Can I alter the values of a Prymary Key in a Table? Thanks and forgive me for these basic questions. Best wishes Javier
On Tue, May 20, 2003 at 11:04:02AM +0200, javier garcia - CEBAS wrote: > Hi; > I'm pretty new in Postgres. > I've created several tables but I didn't care about PRIMARY KEYS in the > begining. Now I woul like to add them to the tables. > I've read that PostgreSQL adds an automatic column called OID to each table; > but when I do a SELECT, the OID column doesn't not appear. The OID is hidden from view. You have to ask from it explicitly. > Is this OID column a PRIMARY KEY by default? Can I see it? No, the oid field is not guarenteed to be unique. > How can I see what column is the primary key in a table? \d table mentions it iirc > Can I select, at any moment a different Prymary Key? A primary is just a unique identifier. So any field with a unique index can be a primary key. > Can I alter the values of a Prymary Key in a Table? Yes. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Вложения
On Tuesday 20 May 2003 10:04 am, javier garcia - CEBAS wrote: > Hi; > I'm pretty new in Postgres. > I've created several tables but I didn't care about PRIMARY KEYS in the > begining. Now I woul like to add them to the tables. OK - to begin at the beginning, you need to decide what fields are your primary key based on the meaning of each table. If you'd like to show the schemas for a couple of tables we can suggest what they might be. Once you've identified them you can use: ALTER TABLE my_table ADD PRIMARY KEY (key_col1, key_col2...) A primary key is a unique way to identify one row in a table. It is possible to have several keys, but only one would be your primary key. For example, here in the UK, each adult has a different National Insurance number. If you were writing a payroll application, you might have an internal "payroll_id" column as well as a "ni_num" column. Both are unique but it might make more sense in the application to use "payroll_id" - this is a business decision. > I've read that PostgreSQL adds an automatic column called OID to each > table; but when I do a SELECT, the OID column doesn't not appear. > Is this OID column a PRIMARY KEY by default? Can I see it? The OID column might/might not be present to see it you need to explicitly ask for it: SELECT OID,* FROM my_table; The OID is intended to be unique for system tables only. You can use it as a primary key, but it is not recommended. User tables do not need to have an OID in current versions of PG, so I'd avoid it if possible. > How can I see what column is the primary key in a table? You can see the details of a table, including indexes, primary key etc with the \d command in psql: => \d contract_type Table "promise.contract_type" Column | Type | Modifiers ---------+----------------------+----------- ct_id | character varying(4) | not null ct_name | short_name_type | not null Indexes: contract_type_pkey primary key btree (ct_id) > Can I select, at any moment a different Prymary Key? This doesn't really make sense - the difference between a "primary key" and any other keys (unique row identifiers) depends on the meaning of a table. > Can I alter the values of a Prymary Key in a Table? Yes, but if other tables refer to them you'll need foreign key constraints to make sure the updates propogate to all tables that depend on them. > Thanks and forgive me for these basic questions. Can I recommend getting a book? I started with "An Introduction to Database Systems" by "C.J. Date" - don't know if it's still in print,. Don't know what books others would recommend, but you're looking for something covering theory rather than the fine detail of a particular database. Richard Huxton
> A primary key is a unique way to identify one row in a table. > to have several keys, but only one would be your primary key. For example, > here in the UK, each adult has a different National Insurance number. If you > were writing a payroll application, you might have an internal "payroll_id" > column as well as a "ni_num" column. Both are unique but it might make more > sense in the application to use "payroll_id" - this is a business decision. And - if you agree with database theory - a bad one at that. Supposedly primary keys should be void of any meaning bar their primary key-ness. I got into the habit of starting any but the most simple table like this: create table ( id serial primary key, ... Never had any trouble with that. Good or bad practice ? Gotta decide for yourself. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Richard Huxton advises: > Can I recommend getting a book? I started with "An > Introduction to Database Systems" by "C.J. Date" - don't know > if it's still in print. It is still in print, and it is damn good! Everyone starting out with databases should read it. Date has an ideological axe to grind, but the information is good. Get the latest edition: http://www.amazon.com/exec/obidos/tg/detail/-/0201385902/qid=1053451778/ sr=8-2/ref=sr_8_2/002-1424813-4586401?v=glance&s=books&n=507846
On Tuesday, May 20, 2003, at 08:41 AM, Karsten Hilbert wrote: >> [Earlier post] A primary key is a unique way to identify one row in a >> table. > >> to have several keys, but only one would be your primary key. For >> example, >> here in the UK, each adult has a different National Insurance number. >> If you >> were writing a payroll application, you might have an internal >> "payroll_id" >> column as well as a "ni_num" column. Both are unique but it might >> make more >> sense in the application to use "payroll_id" - this is a business >> decision. [Earlier post] > > And - if you agree with database theory - a bad one at that. > Supposedly primary keys should be void of any meaning bar > their primary key-ness. [snip] I think a database "theory" that says primary keys should be void of any business meaning is a bad theory, and is certainly not required by the relational model. As one example, a primary key with business meaning assures that one does not have duplicate records (if the data model is otherwise correct). If the data being modeled does not have a "natural" candidate key (an attribute value or values that will always be unique), then one should be created (such as a National Insurance number). Such a created primary key usually becomes indistinguishable from a "natural" key as a business matter because the ability to distinguish entities in the real world is also useful for many purposes outside of any particular database. Another reason to create a primary key would be to avoid overly complex composite keys, particularly those that require attributes that are not otherwise important for the data model -- for example, an adult could be uniquely identified by a composite key including name, date of birth, place of birth, parent's names, etc., without using the National Insurance number (or some other formerly created key).
On Tue, May 20, 2003 at 03:50:11PM -0400, Mark Wilson wrote: > Another reason to create a primary key would be to avoid overly complex > composite keys, particularly those that require attributes that are not > otherwise important for the data model -- for example, an adult could > be uniquely identified by a composite key including name, date of > birth, place of birth, parent's names, etc., without using the National > Insurance number (or some other formerly created key). The other problem is that not everyone may have a National Insurance number (maybe they're foreigners or not old enough). Using identifiers that you don't control is a risky business (wasn't there something about US Social Security Numbers not being unique?). At least in Australia, the National Privacy Principles state, among other things, that you are not allowed to use as primary identifier for a customer an identifier assigned by other organisation (except in certain special circumstances). So, using something like your Tax File Number, Medicare Number or Drivers Licence number as primary key is forbidden. If you're permitted to have the information you're allowed to store it but you sure as hell can't index your filing cabinets on it or make it a primary key. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Вложения
>>>>> "MvO" == Martijn van Oosterhout <kleptog@svana.org> writes: MvO> Number or Drivers Licence number as primary key is forbidden. If MvO> you're permitted to have the information you're allowed to store MvO> it but you sure as hell can't index your filing cabinets on it or MvO> make it a primary key. What exactly is the difference between UNIQUE INDEX on a NOT NULL column of driver license numbers with no other primary key on that table and PRIMARY KEY on that same column? The only thing that changed was the name. But then, try explaining that to your lawmakers... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On Tue, May 20, 2003 at 15:50:11 -0400, Mark Wilson <mwilson13@cox.net> wrote: > > As one example, a primary key with business meaning assures that one > does not have duplicate records (if the data model is otherwise > correct). Unique isn't really good enough. You also want it to be defined for all records and you don't want it to change. I don't know about national ID numbers in the UK, but in the US it is possible to change them, not everyone has one, and they aren't unique (due to screwups - not intentionally).
This is unlike any database theory I've heard of. Choosing a natural key over an artificial key is the ideal. I've heard that a lot. Sometimes there are several candidate keys to choose from. And sometimes the primary keys are more than one column. Sometimes I bail out to an artificial key when the primary key is too long, but it depends very much on how the table will be accessed and who knows what and when. --elein On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote: > And - if you agree with database theory - a bad one at that. > Supposedly primary keys should be void of any meaning bar > their primary key-ness. I got into the habit of starting > any but the most simple table like this: > > create table ( > id serial primary key, > ... > > Never had any trouble with that. Good or bad practice ? Gotta > decide for yourself. > > Karsten > -- > GPG key ID E4071346 @ wwwkeys.pgp.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue
On Wed, May 21, 2003 at 12:02:01PM -0400, Vivek Khera wrote: > >>>>> "MvO" == Martijn van Oosterhout <kleptog@svana.org> writes: > > MvO> Number or Drivers Licence number as primary key is forbidden. If > MvO> you're permitted to have the information you're allowed to store > MvO> it but you sure as hell can't index your filing cabinets on it or > MvO> make it a primary key. > > What exactly is the difference between > > UNIQUE INDEX on a NOT NULL column of driver license numbers with no > other primary key on that table > > and > > PRIMARY KEY on that same column? > > The only thing that changed was the name. There's no *technical* difference, but a very large semantic difference. Whatever you use as your primary will become prolific. It will be used in tables that reference this one, it will appear on invoices and receipts, debugging traces. It will yelled across the room by staff in efforts to track down problems. It will be given as a reference number to other organisations who do stuff on a customer's behalf. If I had to give you my driver's licence number, I'd feel a lot better if there was at least some possibility of access control. Primary keys should never change once a record has been created. If they do you need to update a lot of other stuff. In that sense using opaque identifier is good because then it never needs to change. Besides, over here two states could hand out the same licence number, you need to write the name of the state down too. > But then, try explaining that to your lawmakers... They care only about the semantic difference. It's a very simple but effective way to restrict the flow of private information. Note they are only principles (guidelines), not laws (though there are departments to deal with customer complaints about it). The criminal act is the unauthorised distribution of private information. Identity theft is a real problem, why not do the right thing and do your part? If you can guarentee that even though it's your primary key you're not going print it out or send it to people who have no right to know that info, then I guess you're in the clear. If you can't guarentee it, why take the risk? Given the amount of effort going into prevent spam, why can't people spend the same amount of time reducing the amount of private information floating around uncontrolled. Sorry for going off topic, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Вложения
On 5/20/03 2:50 PM, "Mark Wilson" <mwilson13@cox.net> wrote: > > On Tuesday, May 20, 2003, at 08:41 AM, Karsten Hilbert wrote: > >> And - if you agree with database theory - a bad one at that. >> Supposedly primary keys should be void of any meaning bar >> their primary key-ness. [snip] > > I think a database "theory" that says primary keys should be void of > any business meaning is a bad theory, and is certainly not required by > the relational model. True, the relational model doesn't require this. But it remains a very, very good idea. > > As one example, a primary key with business meaning assures that one > does not have duplicate records (if the data model is otherwise > correct). The problem is simply this. A good primary key (good from the brute vantage point of the database, which is what we're designing) should not be subject either to change or to loss of uniqueness. If a PK does change,it's true that you can handle this by some programmatic means such as adding cascading update rules to any foreign key constraints. So change is irritating but not necessarily catastrophic. But loss of uniqueness is catastrophic. So the data modeler's job is to make keys that are least likely to change or lose uniqueness. The best way (IMHO) to guarantee this is to make sure they have no existence in the problem domain (the "real world") AT ALL. I'm afraid it hasn't been my experience that "a primary key with business meaning assures that one does not have duplicate records", because business rules and business reasons are subject to change that the data modeler can't control. And they do change. Or, in the example another poster offered, the Social Security Number, the supposedly unique key is not, because someone messed up when generating it. So my own personal rule is never to subject the necessary characteristics of keys to any of the ill winds that blow outside the database. These keys should be owned, generated and maintained by the application. If the outside world then wants to use them, well, by all means! But, from the vantage of the data modeler, these keys serve one and only one function, which is to uniquely identify rows in certain tables in a certain database implementation. So my own rule, anyway, is that primary keys should be like water -- elemental, nigh-invisible, colorless, flavorless, and completely under the control of the application designer. Just my two bits' worth. -- sgl ======================================================= Steve Lane Vice President The Moyer Group 14 North Peoria St Suite 2H Chicago, IL 60607 Voice: (312) 433-2421 Email: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com =======================================================
Martijn van Oosterhout wrote: > On Wed, May 21, 2003 at 12:02:01PM -0400, Vivek Khera wrote: > >>>>>>>"MvO" == Martijn van Oosterhout <kleptog@svana.org> writes: >> >>MvO> Number or Drivers Licence number as primary key is forbidden. If >>MvO> you're permitted to have the information you're allowed to store >>MvO> it but you sure as hell can't index your filing cabinets on it or >>MvO> make it a primary key. >> >>What exactly is the difference between >> >>UNIQUE INDEX on a NOT NULL column of driver license numbers with no >>other primary key on that table >> >>and >> >>PRIMARY KEY on that same column? >> >>The only thing that changed was the name. > > > There's no *technical* difference, but a very large semantic difference. > Whatever you use as your primary will become prolific. One technical difference is that if you declare a column as a FOREIGN KEY referencing another table without specifying a column within that table, then the PRIMARY KEY of that table will be used as the target of the FOREIGN KEY. I think. Erik
This is none of my beeswax but I was just reading an excerpt from a book introducing relational database concepts and one of the points made was that it is a good practice to use a primary key that is devoid of any significance -- it should only be significant as a primary key. The reason for this is that when there is meaning to a column, then there is the possibility that the column may be altered in some way, so it is best to use a "pure" primary key column dedicated to that purpose. Erik elein wrote: > This is unlike any database theory I've heard of. > Choosing a natural key over an artificial key is > the ideal. I've heard that a lot. > > Sometimes there are several candidate keys to > choose from. And sometimes the primary keys > are more than one column. > > Sometimes I bail out to an artificial key when the > primary key is too long, but it depends very much on how > the table will be accessed and who knows what and > when. > > --elein > > On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote: > >>And - if you agree with database theory - a bad one at that. >>Supposedly primary keys should be void of any meaning bar >>their primary key-ness. I got into the habit of starting >>any but the most simple table like this: >> >>create table ( >> id serial primary key, >> ... >> >>Never had any trouble with that. Good or bad practice ? Gotta >>decide for yourself. >> >>Karsten >>-- >>GPG key ID E4071346 @ wwwkeys.pgp.net >>E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> >> > >
Choosing an artificial key is the ideal, according to everything I've heard. In one of my database classes, I remember I had a classmate who had worked with some very large datasets of U.S. citizens, and found that there were actually duplicate social security numbers assigned to different people. Not many, and I don't recall whether the first person had died before the SSN was reused, but it really goes to show that they only to _guarantee_ a unique primary key is to generate it yourself. Yes, you may want to put a unique index on your SSN field or other candidate key fields that ought to be unique. Integer keys are also faster to compare and sort on, so I would expect joins between tables to execute faster if the join fields are single integers, compared to a PK that is a combination of varchar() fields. Wes Sheldahl elein <elein@varlena.com>@postgresql.org on 05/21/2003 09:03:09 PM Please respond to elein@varlena.com Sent by: pgsql-general-owner@postgresql.org To: Karsten Hilbert <Karsten.Hilbert@gmx.net>, pgsql-general@postgresql.org cc: elein@varlena.com Subject: Re: [GENERAL] PRIMARY KEYS This is unlike any database theory I've heard of. Choosing a natural key over an artificial key is the ideal. I've heard that a lot. Sometimes there are several candidate keys to choose from. And sometimes the primary keys are more than one column. Sometimes I bail out to an artificial key when the primary key is too long, but it depends very much on how the table will be accessed and who knows what and when. --elein On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote: > And - if you agree with database theory - a bad one at that. > Supposedly primary keys should be void of any meaning bar > their primary key-ness. I got into the habit of starting > any but the most simple table like this: > > create table ( > id serial primary key, > ... > > Never had any trouble with that. Good or bad practice ? Gotta > decide for yourself. > > Karsten > -- > GPG key ID E4071346 @ wwwkeys.pgp.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
I think it has a lot to do with performance versus natural design. While it may be natural to key records off of a primary key of first name, last name, address, city, state, cip, it is much faster to key off of a simple integer. So, one school of thought would be to use a unique index on the stuff that should be unique, but to have a serial column act as your foreign key. The other would be to use a primary key as both a unique index AND a foreign key. Performance wise, the single int will usually win, especially if you aren't returning data that is actually in the unique index. I think both schools are valid, one provides a more natural feel to the way the fks are referenced, but is slower, while the other uses an artificial fk is less intuitive but faster. On Thu, 22 May 2003, Erik Price wrote: > This is none of my beeswax but I was just reading an excerpt from a book > introducing relational database concepts and one of the points made was > that it is a good practice to use a primary key that is devoid of any > significance -- it should only be significant as a primary key. The > reason for this is that when there is meaning to a column, then there is > the possibility that the column may be altered in some way, so it is > best to use a "pure" primary key column dedicated to that purpose. > > > > Erik > > > > elein wrote: > > This is unlike any database theory I've heard of. > > Choosing a natural key over an artificial key is > > the ideal. I've heard that a lot. > > > > Sometimes there are several candidate keys to > > choose from. And sometimes the primary keys > > are more than one column. > > > > Sometimes I bail out to an artificial key when the > > primary key is too long, but it depends very much on how > > the table will be accessed and who knows what and > > when. > > > > --elein > > > > On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote: > > > >>And - if you agree with database theory - a bad one at that. > >>Supposedly primary keys should be void of any meaning bar > >>their primary key-ness. I got into the habit of starting > >>any but the most simple table like this: > >> > >>create table ( > >> id serial primary key, > >> ... > >> > >>Never had any trouble with that. Good or bad practice ? Gotta > >>decide for yourself. > >> > >>Karsten > >>-- > >>GPG key ID E4071346 @ wwwkeys.pgp.net > >>E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 4: Don't 'kill -9' the postmaster > >> > >> > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
wsheldah@lexmark.com wrote: > Choosing an artificial key is the ideal, according to everything I've > heard. In one of my database classes, I remember I had a classmate who had > worked with some very large datasets of U.S. citizens, and found that there > were actually duplicate social security numbers assigned to different > people. Not many, and I don't recall whether the first person had died > before the SSN was reused, but it really goes to show that they only to > _guarantee_ a unique primary key is to generate it yourself. Yes, you may > want to put a unique index on your SSN field or other candidate key fields > that ought to be unique. I think the desire to have an artificial numeric key is founded in the manner in which SQL has implemented the relational model. Logically, artificial candidate keys have no business in a relation. But I agree in their convenience in throwing around keys of a consistent size and type in client applications and middleware. So, IMHO, I think the modeler should first design the database to be *logically consistent*: 1) Each relation has a unique, natural candidate key (the x of the relation) - relations are sets, not bags. 2) Each relation's non-key attributes (the f(x), g(x), ... of the relation) should be dependent upon the natural key, the whole key, and nothing but the natural key - that's 3NF at a minimum. Then, once the model complies with the RM wrt constraints on the domains, relations, and a database as a whole, one could go back and add the artificial keys for convenience purposes. It's the modeller's job to design a database that ensures logical consistency *first* in the face of a users, programmers, dbas, etc. that will attempt to break it. The database should be, logically speaking, unbreakable. But the whole point of the RM is that it is *provably* logically consistent if its prescriptions and proscriptions are followed. SQL doesn't force that on you, which is probably a mistake... IMHO, Mike Mascari mascarm@mascari.com
On Thursday, May 22, 2003, at 11:43 AM, wsheldah@lexmark.com wrote: > > Choosing an artificial key is the ideal, according to everything I've > heard. In one of my database classes, I remember I had a classmate who > had > worked with some very large datasets of U.S. citizens, and found that > there > were actually duplicate social security numbers assigned to different > people. Not many, and I don't recall whether the first person had died > before the SSN was reused, but it really goes to show that they only to > _guarantee_ a unique primary key is to generate it yourself. Yes, you > may > want to put a unique index on your SSN field or other candidate key > fields > that ought to be unique. > > [snip] Wouldn't one wish to know, and deal with, a situation where a business rule -- each person has a unique social security number -- has been violated? If you're a business doing tax withholding for employees, isn't this a critical question?
This message of Scotts and Mike Mascari's message clarifies my statement about natural keys better than I could. IME I design databases to reflect the data in them using relational theory with a dab of object modeling. I am as much influenced by Rambaugh (Object-Oriented Modeling & Design, Rumbaugh, Blaha, et al) as Date and intuition. Design by theory, then tune for practicality when doing the implementation. SQL is not the only query language nor is PostgreSQL's implementation of it the only one. But I must say that PostgreSQL's UPDATE CASCADE capabilities are pretty nice when you have natural keys. elein On Thursday 22 May 2003 09:56, scott.marlowe wrote: > I think it has a lot to do with performance versus natural design. > > While it may be natural to key records off of a primary key of first name, > last name, address, city, state, cip, it is much faster to key off of a > simple integer. > > So, one school of thought would be to use a unique index on the stuff that > should be unique, but to have a serial column act as your foreign key. > > The other would be to use a primary key as both a unique index AND a > foreign key. > > Performance wise, the single int will usually win, especially if you > aren't returning data that is actually in the unique index. > > I think both schools are valid, one provides a more natural feel to the > way the fks are referenced, but is slower, while the other uses an > artificial fk is less intuitive but faster. > > On Thu, 22 May 2003, Erik Price wrote: > > > This is none of my beeswax but I was just reading an excerpt from a book > > introducing relational database concepts and one of the points made was > > that it is a good practice to use a primary key that is devoid of any > > significance -- it should only be significant as a primary key. The > > reason for this is that when there is meaning to a column, then there is > > the possibility that the column may be altered in some way, so it is > > best to use a "pure" primary key column dedicated to that purpose. > > > > > > > > Erik > > > > > > > > elein wrote: > > > This is unlike any database theory I've heard of. > > > Choosing a natural key over an artificial key is > > > the ideal. I've heard that a lot. > > > > > > Sometimes there are several candidate keys to > > > choose from. And sometimes the primary keys > > > are more than one column. > > > > > > Sometimes I bail out to an artificial key when the > > > primary key is too long, but it depends very much on how > > > the table will be accessed and who knows what and > > > when. > > > > > > --elein > > > > > > On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote: > > > > > >>And - if you agree with database theory - a bad one at that. > > >>Supposedly primary keys should be void of any meaning bar > > >>their primary key-ness. I got into the habit of starting > > >>any but the most simple table like this: > > >> > > >>create table ( > > >> id serial primary key, > > >> ... > > >> > > >>Never had any trouble with that. Good or bad practice ? Gotta > > >>decide for yourself. > > >> > > >>Karsten > > >>-- > > >>GPG key ID E4071346 @ wwwkeys.pgp.net > > >>E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > >> > > >>---------------------------(end of broadcast)--------------------------- > > >>TIP 4: Don't 'kill -9' the postmaster > > >> > > >> > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- ============================================================= elein@varlena.com Database Consulting www.varlena.com PostgreSQL General Bits http:/www.varlena.com/GeneralBits/ "Free your mind the rest will follow" -- en vogue