SQL Database Design » 10. Constraints » 10.1 Primary Keys

10.1 Primary Keys

Table of Contents

Primary key constraints are used to uniquely identify entities within a table. A table may have only one primary key. Primary key columns cannot be NULL. Columns flagged as primary keys are automatically made NOT NULL by the DBMS. You generally have two choices for your primary keys: natural or artificial keys. You can read more in the 13.1 Natural vs. Artificial Keys section.

10.1.1 Unsigned IDs

Using unsigned (artificial) keys is a two-edged sword. Often, IDs are simple auto-increment columns. The general question is, whether it is worth any effort to avoid wasting half of an integer's range of values or not. One argument against unsigned integers is, that you either need a ... CHECK ( col_name >= 0 ) constraint or you have to use a non-portable keyword like UNSIGNED.

Usually signed integers are alright. If you happen to run out of IDs, you already have a pretty big database, probably with a lot of storage space available, so it will not matter much switching to BIGINT instead. If you insist on using the full range of an integer, there is nothing against using negative IDs. Overall, I would recommend not to use unsigned IDs and rather switch to the next larger integer instead. Of course, this is not really necessary on Oracle because of the way NUMBER works.

10.1.2 Strings as Keys

There is no technical problem with using strings as/in primary keys. But, are there any downsides when compared with integer IDs? The thought relates to storage and indexing, as a keyword can basically be n bytes whereas an integer is usually only four bytes. Even though you might think there is a storage hit when tables reference others via strings instead of integers, the hit is minor. Storage space is cheap today and every primary key column is (usually) indexed anyway. Technically, there is no problem.

10.1.3 Mutability

Many developers seem to think that primary keys have to be constant for a lifetime. This is not true. Often, natural keys must be changed, e.g. when the last name of a woman must be changed because she gets married. As long as the database's referential integrity is retained, there is no need to think that way. You can always use an ON UPDATE CASCADE clause where appropriate.

More practical issues are addressed in the section 13.1 Natural vs. Artificial Keys.

Last updated: 2010-08-04