Re: Primary Key, Unique Key

On Sat, 7 Mar 1998 01:59:01 GMT, mickm@netcom.com (Mickey Mestel) wrote: >Lee Wan Ling (eno.enolwl@memo.ericsson.se) wrote: >: Can anyone enlighten me on the differences between a primary >: key and a unique key? When should I use a primary key and when should I >: use the unique key constraint? > > the two are basically the same thing: a field (or fields) that has been >chosen to act as a key to a table, and so must be unique. Not quite the same thing. Just to make it clear: A primary key is the unique identifier for a row consisting of one or more fields of the table.. There is no unique key, but a unique index. I assume that's what Lee is refering to. Spesifying unique when creating an index is a way of enforcing the fields of the index to be unique. This is a physical implementation issue although it is used and usefull for enforcing the logical requirement of uniqueness as well. There is *no* requirement for an index on a primary key in a relational database as such. It's all too common to confuse keys in a relational database with indexes although there is no relationship. The fact that Informix implements part of the uniqueness constraint via an index is solely an implementation issue. It's actually sad they do so as it makes it necessary to define such indexes on all small tables that would otherwise not need them and leads only to somewhat worse performance. On larger tables it's of course a good way of implementing primary keys. Now an important issue you forgot to mention which makes these two concepts even more different is that any field that is part of a primary key can not be null while that's not the case with a unique index. (If a field is included in the primary key definition in a table there is thus no need to specify "not null" on that field. The primary key spesification takes care of that all by itself as it should.) If you only create a unique index on one or more fields in an Informix database null is seen as just another value. If you have only one field in the unique index one row can have a null in this field. If there are more fields more rows can have null in one or more fields as long as the fields are unique including those with null seen as a value. This is the only place you can properly view null as a value and not as an indicator of value unknown as it is in all other places. > the purpose of this >is that you can uniquely identify the row using only the key value. the term >primary key just means that in the relational model, it is the primary key for >this table, which can be joined to other tables via a foriegn key. when you >have a primary key of one table in another table, it is a foriegn key in the >second table, and the referential constraints in the database make sure that >the two are always the same. so if you have a primary key in one table, say >'123', and a foriegn key in another table, you can't delete that row from the >first table, (the parent table), without first deleteing the row from the >second table, (the child), or that would leave the child without a parent, >which violates the rules of referential integrity. > > a column can be unique, and can have a unique index on it, without >necessarily being a key, certainly not one bound by referentail constraints. >what it comes down to is that they are the same thing, but primary key refers >to the referential constraint that should go along with properly using a key. > > hope that helps. > > mickm Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: Primary with ODBC info: http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html