From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | javier garcia - CEBAS <rn001(at)cebas(dot)csic(dot)es>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: PRIMARY KEYS |
Date: | 2003-05-20 11:40:54 |
Message-ID: | 200305201240.54784.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Nigel J. Andrews | 2003-05-20 11:45:20 | Re: How to get required space between field names |
Previous Message | Nigel J. Andrews | 2003-05-20 11:22:07 | Re: How to get required space between field names |