Thread: Primary vs Unique Index
Why would one need a Primary Key... which can only be declared at table creation if one can create a Unique Index post tablecreation? ie: I deleted my primary key... is that a big deal? What's the purpose of the Primary Key if it's function can be duplicatedwith another Unique Index? thanks,
Thus spake Gonzo Rock > Why would one need a Primary Key... which can only be declared at table creation if one can create a Unique Index posttable creation? > > ie: I deleted my primary key... is that a big deal? What's the purpose of the Primary Key if it's function can be duplicatedwith another Unique Index? It has to do somewhat with database theory. There is a basic distinction between a unique index and a primary key. Ideally, the primary key should never change but a unique key can as long as the new value is also unique. PostgreSQL doesn't enforce this (I think it should) but a good database design will include primary keys that can remain constant. One thing that PostgreSQL allows is for a program to query a database and determine which key in a table is the primary key. You can use this in user programs to make decisions like that suggested above. I use it in PyGreSQL (Classic) to find records based on a dictionary of values. For example, let's say I have just loaded an address record into a dictionary and I would like that dictionary to include the full province name. I have a province table that allows that lookup. db.get('province', dict) The get method looks at province and determines that the primary key is 'prov' so it looks up that key in the dictionary and uses it to load in (and merge) the province record. If you are familiar with a 4GL such as Progress you may recognize this soul brother of the above statement. FIND province OF address. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Darcy, > It has to do somewhat with database theory. There is a basic > distinction > between a unique index and a primary key. Ideally, the primary key > should > never change but a unique key can as long as the new value is also > unique. > PostgreSQL doesn't enforce this (I think it should) but a good > database > design will include primary keys that can remain constant. To add my .02 here ... I'd prefer it if PostgreSQL required a Primary Key at table creation, and forced drop-and-recreate if you attempt to modify the primary key. Frankly, I regard the failure to enforce Primary Key requirements to be a PostgreSQL bug, even if Tom & Peter don't. A strong Primary Key feature is absolutely required if we want to later build more advanced ANSI SQL 92 features into PostgreSQL. For example, if we wanted to improve Postgres so that simple views would be updatable without writing a lot of custom triggers, then all tables must have a Primary Key as a prerequisite. Or, some more common problems: If you created a Key-less table, with a seperate unique index, you'd run into two problems: 1. Any RDBMS or interface features meant to grab the Primary Key would fail. An example of this is MS ODBC, which requires a Primary Key to make a linked table updatable. 2. If someone later modified your unique index to make it not-unique, you might find yourself with a table with no unique indexes. In this circumstance, the database engine has a hard time identifying individual rows, and will tend to UPDATE/DELETE entire sets of rows based on common characteristics. Fabian Pascal, in "Practical Issues in Database Management" talks further about the importance of primary keys. ______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