SQL by Design: Choosing a Primary Key

Choosing a primary key is one of the most important steps in good database design. A primary key is a table column that serves a special purpose. Each database table needs a primary key because it ensures row-level accessibility. If you choose an appropriate primary key, you can specify a primary key value, which lets you query each table row individually and modify each row without altering other rows in the same table. The values that compose a primary key column are unique; no two values are the same.

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table.

Screen 1 shows seven tables, each with one or two far-left columns underlined. Listing the primary key column(s) first in the table is not necessary, but this is a design and programming standard. The CUSTOMER table has a single-column primary key, CustID. The ADDRESS table has a concatenated primary key, CustID plus AddrType. As you can see from the example data, the set of values is unique for each primary key. Each customer in the CUSTOMER table has a unique CustID that is different from the CustID of any other customer. The ADDRESS table primary key values are also unique, but to see the uniqueness you have to append or concatenate the values from the CustID and AddrType columns into one string. The ADDRESS table has only one billing value and one shipping value.

You can promote a candidate key to primary key when you create the table. In Transact-SQL (T-SQL), the create table command is

CREATE TABLE dbo.SalesPerson (   EmpNo  smallint  NOT NULL,    EmpName  varchar  (25)  NOT NULL  CONSTRAINT  pkeySalesPerson  PRIMARY KEY    CLUSTERED (EmpNo) )GO

You use the CONSTRAINT clause at the end of the CREATE TABLE statement to promote the EmpNo column to primary key. SQL Server creates a unique index on column EmpNo and clusters the table, in accord with the specification PRIMARY KEY CLUSTERED. Similar to a book index, a database table index is a copy of data values arranged in ascending order, and SQL Server uses it for quick look-up and direct access when users query the database. The clustering option ensures that the data in the table is part of the index, which enables quick search and retrieval.

You can also create tables without constraints, then add constraints in a second step:

ALTER TABLE SalesPersonADD CONSTRAINT pkeySalesPerson PRIMARY KEYCLUSTERED (EmpNo)GO

Each candidate key has a certain set of characteristics that recommends it for the title of primary key. These characteristics are never null, brevity, simplicity, preferred data type, nonidentifying value, and never change.

Never Null

No primary key value can be null, nor can you do anything to render the primary key null. This is an inviolate rule of the relational model as supported by ANSI, of relational database management system (RDBMS) design, and of SQL Server. When you designate a primary key, SQL Server flags as NOT NULL all columns that make up the pkey. Null is an unknown condition. When a table value is null, it means one of several things. It can mean that the value is unknown, that the value isn't relevant, or that you don't know whether the value is relevant.

Note that in the previous CREATE TABLE statement, I created the primary key (the EmpNo column) with a NOT NULL property. A unique index—such as the one SQL Server creates as a result of the primary key CONSTRAINT clause—allows only one instance of null, unless you create the column the index is built on as NOT NULL. If a primary key value were allowed a null value, then you couldn't easily retrieve the row associated with the primary key.

Brevity

Because the SQL Server query processor uses the primary key index for lookups and comparisons, choose a brief primary key—one column, if possible. You use primary key columns for joins (combining data from two or more tables based on common values in join columns), for query retrieval, and for grouping or sorting a query result set. The briefer the index entries are, the faster SQL Server can perform the lookups and comparisons. For example, you can use the primary key column for query retrieval (SELECT * FROM SalesPerson WHERE EmpNo = 101) and for data modification (UPDATE SalesPerson SET EmpName = "Joe Buchanan" WHERE empno = 101). In addition, you can use the primary key column to group or sort a query result set (SELECT * FROM Customer ORDER BY CustID).

Simplicity

When choosing a primary key, look for candidates with no embedded spaces, special characters, or differential capitalization. If you installed SQL Server to recognize the difference between uppercase and lowercase letters (whether in dictionary order, case-sensitive order, or binary sort order) in a query, avoid primary key candidates that contain mixed-case values because these entries are hard to work with in SQL queries and join statements. Writing "Big Al's Sports Emporium" in dozens of queries each day is more difficult and error-prone than writing "2".

Data Type

Integer (number) data types are the best choice for primary key, followed by fixed-length character data types. SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.

Fixed-length character data types are better than variable-length character data types because SQL Server must decompress variable-length character data before processing the data. This extra step consumes valuable processing power.

Nonidentifying Value

A common mistake among database designers is trying to build intelligence, or meaning, in to the primary key. The most compelling reason not to create a primary key with meaning is that the primary key column you create to be descriptive might become obsolete. For example, you can build an intelligent primary key in the CUSTOMER table in Screen 1.

YYMMSSCCCnnn, whereYY = the last two digits of the year this customer placed its first order,MM = the two-number month designator which indicates in which month the customer placed its first order,SS = the two-character code for state where the customer resides,CCC = the three-character code for city, based on airport codes,nnn = numbers, from 0001 to 999, allowing for a maximum of 999 customers for each YYSSCCC combination.

A customer from Denver who first ordered from your company in June 1990 initially would have a customer number 9006CODEN010, assuming that this was the tenth customer from Denver. You can then use this intelligent primary key to scan and parse the key value so you can tell when customers started doing business with your company and where the customers are located. You could sort and filter by year or year and month, by state, or by city. But if the customer opens a second office location in a different city or state, should you treat the second office as a separate customer? Or should you continue to use the original customer number for both offices, although this approach will prevent access to the embedded information about the second office? And what happens when you get the 1000th customer in Los Angeles? How much database redesigning and rewriting will you need to expand the nnn portion of the customer number to nnnn? To avoid these difficulties, choose a primary key with a nonidentifying value.

Never Change

After you assign a primary key value, never change it. Returning to the previous example, what happens when a customer moves its operation from New Jersey to Colorado? Do you change the primary key value? How do you handle the primary key when a city builds a new airport, such as happened in Denver? Now, to be accurate, the old customer 9006CODEN010 should be 9006CODIA010. Do you create a new customer number, thus disrupting the history established by this customer, or do you try to change all instances of 9006CODEN010 to 9006CODIA010 in the database? Note that the primary key values repeat from table to table in Screen 1. The CustID column appears in the CUSTOMER table and in the ORDER table. In the CUSTOMER table, CustID is the primary key column. In the ORDER table, CustID is not the primary key but a foreign key. The foreign key sets up an implied link from one table to another. The foreign key CustID of the ORDER table implies a one-to-many (1:M) relationship between the CUSTOMER and ORDER tables. Primary key values in a relational database of any size cascade from one table to another, propelled by the 1:M relationships that are a necessary part of the database. If you change a primary key value, you'll find that you have to change the associated foreign key values in many tables, or you'll lose the informational links built into the database.

Surrogate Keys

Here's one method to ensure that the primary key value is never null, brief, a simple data type, and a nonidentifying value: Create the primary key column as a surrogate key. Each surrogate value represents, or stands in place of, its associated row in a table. The surrogate value is meaningless. The numbering scheme usually starts with 1 for the first row of the table, and increments with each row added to that table. SQL Server has an identity property that you can specify for number data types. When you create a primary key column with the identity property, the primary key column is a surrogate primary key. The surrogate column's values are system-generated, and each value is unique within a table. You can use this code to create a surrogate primary key in T-SQL:

CREATE TABLE dbo.Customer( CustID  integerIDENTITY(1,1)PRIMARY KEY NONCLUSTERED,   CompanyName  varchar (25)  iNOT NULL )GO

In this statement, you don't have to specify not null when you create the primary key column. The identity property ensures that each row inserted into the table has a unique, system-generated number for CustID. The numbers start at 1 and increment by 1, as specified. For large-volume, high-transaction databases, you may want to assign a different range of numbers for each table to avoid a potential bottleneck on insert row operations to the database. And, certainly, if you have a horizontally partitioned table in your database, you might want to assign a different, non-overlapping range of numbers for each portion of the table.

May the Best Primary Key Win

You can use the criteria in Table 1 to evaluate each primary key candidate. The candidate that matches all the best answers is the best choice as the table's primary key. If no candidate meets all the best answer criteria, consider creating a surrogate primary key for the table.