I asked this question here awhile ago. It's a fairly common question, and it's known as the surrogate vs natural key debate. Using a natural key has the advantage of performance. With a surrogate key, most RDBMS systems will have to maintain two indexes. Natural keys can also make your database more readable, and can eliminate the need to do joins for foreign keys in some cases. Surrogate keys are useful because you can very easily change your data structure with a bit less SQL magick. A lot of Object Relational Mappers always create surrogate keys, too, although I suspect that's mostly a way to make the ORM more portable by guaranteeing that a primary key will exist. The only other time surrogate keys are very useful is when all your candidate keys have values that change fairly often, since the primary key ought to be as stable as possible. Some developers also feel more comfortable with an id field. Having that metadata feels like a safety net for some reason. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Allison Sent: Friday, November 24, 2006 9:54 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: [GENERAL] indexes I notice a lot of places where people use the approach of creating an index and a unique key like: CREATE TABLE foo ( idx SERIAL PRIMARY KEY, name varchar(32) UNIQUE NOT NULL ) instead of CREATE TABLE foo ( name varchar(32) PRIMARY KEY ) If the name is NEVER going to change, is there any advantage to doing this? If there are many-to-many reference tables (like name-to-friends) is this any different? I've seen this a lot, but I've always assumed that with the condition that 'name' would NEVER change, there was no advantage. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
