Re: Primary Keys

Andrew Bell <acbell(at)iastate(dot)edu> writes:
> Can someone tell me what advantage there is to creating a table with a
> primary key rather than simply adding a unique index to the table at a
> later time.

AFAIK, the only functional difference is that a primary key declaration
establishes a default column (or columns) for foreign key references
from other tables. If you did CREATE TABLE foo (..., primary key(f1))
then another table could say just "REFERENCES foo" and f1 would be
understood to be the referenced column. Without a primary key spec
you would always have to mention the referenced column explicitly.

BTW, I believe PRIMARY KEY also causes a NOT NULL constraint to be
attached to the columns, so it's not exactly equivalent to a unique
index. It's equivalent to unique index + NOT NULL, except for the
REFERENCES convenience feature.

regards, tom lane