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 |