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