Re: How to determine PRIMARY KEYS

On Mon, 11 Jun 2001, Sandro Dentella wrote:

>   I really think that this is more than needed, becouse if you have a table
>   w/ a primary key and unique index NOT NULL on an other attribute both these
>   attributes would be choosen not just the minimum needed (the primary key
>   OR an attribute NOT NULL w/ unique index).
There's an attribute indisprimary in pg_index which you can use to find
what's the primary key on the table. 

I think what you should do is this:
a) if there's a primary key, use that.
b) if there isn't, find a unique/notnull index.

>   1. find all unique indexes on table ($tbl)
> 
>     SELECT i.indexrelid FROM pg_class c, pg_index i \
>           WHERE c.relname = '$tbl'    and c.oid = i.indrelid    \
>           and i.indisunique = 't'
> 
> 
>   2. select in pg_attribute the attributes of these indexes...
>      
>   3. loop over these attrs ($at) and select those that are NOT NULL.
> 
>      SELECT attname,attnotnull from pg_attribute \
>             where attrelid = (SELECT oid from pg_class \
>             where relname = '$tbl') and attname = '$at' \
>             and attnotnull = 't' 
> 
> May I as an alternative just relay on the pattern *_pkey and look at the
> attribute of relation *_pkey to get the PRIMARY KEY?
> 
> Is there a document that explains system tables?
> 
> 
> Thanks in advance
> 
> sandro
> *:-)
> 
> 
> 
> PS. For those who whant to look at tksql or sdsql (the tcl/tk package):
>     http://pcco2.mi.infn.it/sd/soft. If you encounter problems installing or
>     running it I'd really appreciate to be informed (and/or if you like
>     it...;-) . Thanks
> 
> Note: sorry if this message shows up 2 times. I had problems w/ config of
>       sendmail 
> 
> 
> 
> 

-- 
--
Alex Pilosov            | http://www.acecape.com/dsl
CTO - Acecape, Inc.     | AceDSL:The best ADSL in Bell Atlantic area
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018      |