About Primary Keys and auto increment columns

Ideally, every table should have a Primary Key. It's very important that a table has a Primary Key. There are two good reasons why a table should have a Primary Key. First, a Primary Key uniquely identifies each record in a table so it helps to ensure against redundant data in that table. This also means that we can "point" to a specific record in the table. Second, it is also the instrument used to establish a relationship between tables. This will be of importance when you want to retrieve data from multiple tables in a query.

Some guidelines for establishing a Primary Key:

1. It’s value must be unique.
2. It can never be null.
3. It must directly identify each value of the remaining fields in a given record of a table.
4. It should comprise the minimum number of fields to guarantee uniqueness.

A Primary Key may consist of multiple fields.

A lot of database developers add an identity or auto column to each of their tables to ensure uniqueness. This may sound like a good idea but, if the rows in a table are naturally unique, then the use of an identity column is not recommended and not needed. Using an (unnecessairy) auto increment or identity column can reduce the performance of your database because it may require the use of extra joins, which requires more I/O to read your table. Advantages of using identity columns are:

1. Uniqueness is always guaranteed.
2. This is a simple and flexible implementation.

You can make an attribute (or column) part of the Primary Key by setting the Primary Key checkbox in the Attribute dialog of DeZign for Databases. You can make a multi-column Primary Key by setting the Primary Key option in multiple attributes. It is recommended to place the attributes that are part of the primary at the top of the entity/table. Most DBMS's require this.


See also: Autoincrement Primary Key for Oracle, How to implement an auto increment column in MS Access?, Auto increment Primary Key for InterBase