Re: PRIMARY KEYS - Mailing list pgsql-general

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