Re: 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(at)svana(dot)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

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(at)svana(dot)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(at)svana(dot)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(at)kciLink(dot)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(at)cox(dot)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).

On Wed, May 21, 2003 at 12:02:01PM -0400, Vivek Khera wrote:
> >>>>> "MvO" == Martijn van Oosterhout <kleptog(at)svana(dot)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(at)svana(dot)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

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(at)varlena(dot)com Database Consulting www.varlena.com
PostgreSQL General Bits http:/www.varlena.com/GeneralBits/
"Free your mind the rest will follow" -- en vogue

On 5/20/03 2:50 PM, "Mark Wilson" <mwilson13(at)cox(dot)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(at)moyergroup(dot)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(at)svana(dot)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
>>
>>
>
>

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
>

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(at)postgresql(dot)org
>
>

--
=============================================================
elein(at)varlena(dot)com Database Consulting www.varlena.com
PostgreSQL General Bits http:/www.varlena.com/GeneralBits/
"Free your mind the rest will follow" -- en vogue