Tim Uckun wrote: > If I have a primary key constraint defined in the database do I also > need to create an index on that field for fast lookup? No. Declaring field(s) as the primary key automatically adds a UNIQUE constraint on those fields. PostgreSQL implements unique constraints using a unique-constrained index. PostgreSQL tells you about this when you create a table. craig=> CREATE TABLE j ( y INTEGER PRIMARY KEY ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "j_pkey" for table "j" > The documentation on the web seems to imply that the contraint is not > an index. Is that right? There are many types of constraint. Unique constraints. Foreign key constraints. CHECK constraints. Probably more. Of those, unique constraints are the only ones that will automatically create an index. Foreign key constraints benefit from an index on the referring field, by the way, so you should generally create an index on the referring field. PostgreSQL doesn't do this for you since it's not strictly necessary and the index does have a space cost and a time cost for updates, inserts and deletes. As for CHECK constraints - I strongly recommend reading up on them, as they're really important for producing schema that properly ensure that the data stored is valid at all times. > What the difference between creating a unique, not null index and > setting a primary key? As far as I know, a huge amount in purely technical terms. There may only be one primary key, where there may be several NOT NULL UNIQUE constrained columns or column sets. Also, some clients rely on the primary key as table metadata. DBMS front-ends (think MS Access), reporting tools, etc tend to use this information, as do some ORM tools. -- Craig Ringer > Thanks. >
