Thread: Where's The Primary Key?

Hi,

I'm new to Postgresql and having trouble finding some familiar ground.  I'm
on Redhat 7.2, and using Postgresql 7.1.3 (I think).  Postgresql was
installed with the operating system with no "help" on my part.

I've created a simple table as shown below and can't find any indication
that the primary key was created.  I would expect the \d meta-command to
show the primary key.  (pgaccess also indicates that there is no primary key
defined.)  I've tried this with a column constraint, a table constraint,
with and without the constraint name, and a few other variants.  All had the
same results.  Is the key really there?  Shouldn't I see it?  Am I doing it
wrong or thinking about it wrong?

I'd appreciate any help to get me kick-started.


db01=> CREATE TABLE junk_t (
db01(> idx int CONSTRAINT junk_idx PRIMARY KEY,
db01(> dat int
db01(> );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'junk_idx' for
table 'junk_t'
CREATE


db01=> \d junk_t
         Table "junk_t"
 Attribute |  Type   | Modifier
-----------+---------+----------
 idx       | integer | not null
 dat       | integer |
Index: junk_idx

db01=>

Am Fre, 2003-07-25 um 09.13 schrieb Jim Louis:
>          Table "junk_t"
>  Attribute |  Type   | Modifier
> -----------+---------+----------
>  idx       | integer | not null
>  dat       | integer |
> Index: junk_idx

\d junk_idx

apart from that, it's much easier just to do:

...
idx int PRIMARY KEY,
...

Just leave the naming of the index to postgresql.

FYI:
You may also want to use SERIAL instead of INT.
It creates INT but with (auto-filled) serial numbers (auto increment).

hth
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

> db01=> \d junk_t
>          Table "junk_t"
>  Attribute |  Type   | Modifier
> -----------+---------+----------
>  idx       | integer | not null
>  dat       | integer |
> Index: junk_idx

Primary key is the combination of unique index + not null. In your case,
both are listed. For further details refer pg_indexes system view.

regards,
bhuvaneswaran

On Mon, Jul 28, 2003 at 09:26:33 +0530,
>
> Primary key is the combination of unique index + not null. In your case,
> both are listed. For further details refer pg_indexes system view.

That isn't the whole story. The primary key of a table is also a default
for foreign keys. There can be several columns that have a unique index
and a not null constraint, but there can be only one primary key.
(The primary key can also be multicolumn.)