Thread: Primary Keys

Hi,

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.

Thanks,


-- Andrew Bell
[email protected]

Andrew Bell <[email protected]> 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

Andrew,

> > 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.

If you are a serious DBA, one of the important rules to follow is "Never
create any table without a Primary Key."  One can achieve the same
result by adding a UNIQUE index on a NOT NULL column later, but the
danger is that you'll forget and go into production with an un-keyed
table.  Believe me, I've done it, and didn't realize what was wrong
until I got a "Could not identify unique tuple" error.

For an extensive discussion of why primary keys are important, see
"Practical Issues in Database Management" (F. Pascal).

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      [email protected]
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco