Re: [HACKERS] Finding primary keys in a table

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> wrote

> > > Because we just create a unique index on a PRIMARY specification, I
> > > think any unique index on a field shows it as primary.
> > Hmm.  Any chance we can somehow flag it as well?  Perhaps a new bool
> > field in pg_index the next time we do a dump & reload release?  I
> > assume we will need it eventually anyway.
>
> I'm not sure I understand all the issues, but if we can avoid
> distinctions between different indices that would be A Good Thing. Since
> multiple unique indices are allowed, what would be the extra
> functionality of having one designated "primary"? Is it an arbitrary
> SQL92-ism which fits with older databases, or something which enables
> new and interesting stuff?

Currently the 'primary key' is distinguished by being named
<table name>_pkey (at least this is what the warning sais ;),
I'think this should be quite enough for most purposes.

BTW, are there any operational differences (like not being able to
drop the index) in SQL92 that set primary key apart from other
unique indexes ?

For example, can a foreign key constraint reference any key in the
foreign table ?

Hannu