Обсуждение: 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 acbell@iastate.edu
Andrew Bell <acbell@iastate.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
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 josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco