Good database design starts with the right 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, page 64, 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 SalesPerson
ADD CONSTRAINT pkeySalesPerson PRIMARY KEY
CLUSTERED (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).
Prev. page
[1]
2
next page 
