Using the CREATE TABLE Statement to Assign the Primary Key

A key is a column or combination of columns that uniquely identifies a row in a table. As such, a key gives you way to distinguish one particular row in a table from all of the others. Because a key must be unique, you should not include NULL values in any of the columns that make up a key. Remember, the DBMS cannot make any assumptions about the actual value of NULL in a column. Thus, a row with NULL in a key column will be indistinguishable from any other row in the table because the NULL value could, in fact, be equal to the value in any other row.

Each table can have one (and only one) PRIMARY KEY. Because the PRIMARY KEY must uniquely identify each row in a table, the DBMS automatically applies the NOT NULL constraint to each of the columns that make up the PRIMARY KEY. When creating a new table, you can create a single-column PRIMARY KEY by including the key words "PRIMARY KEY" in the column definition.

The syntax of a PRIMARY KEY definition in a CREATE TABLE statement comes in one of two forms

[CONSTRAINT <constraint name>] PRIMARY KEY

if the PRIMARY KEY is an unnamed constraint defined as part of the PRIMARY KEY column's definition, or

CONSTRAINT <constraint name>

PRIMARY KEY (<column name>

[,<column_name...[,<last column name>]])

for a multiple-column (or composite) PRIMARY KEY or a single-column named PRIMARY KEY.

For example, the CREATE TABLE statement

CREATE TABLE employee

(employee_id INTEGER PRIMARY KEY,

first_name VARCHAR(20),

last_name VARCHAR(30))

identifies the EMPLOYEE_ID column as the PRIMARY KEY in the EMPLOYEE record. As such, every row in the EMPLOYEE table must have a unique, non-NULL value in the EMPLOYEE_ID column.

The system stores the PRIMARY KEY as a constraint in the system tables. As such, if you don't give the PRIMARY KEY a name, the DBMS will generate one for you. The name the system assigns becomes important to you if you ever want to drop a table's PRIMARY KEY so that you can change it. (Since a table can have only one PRIMARY KEY, you will need to drop the existing PRIMARY KEY and create a new one when if you want to change its column[s].)

Note If you are using MS-SQL Server, you can call the stored procedure sp_help to display the name that the DBMS assigned to the PRIMARY KEY by executing the command

EXEC sp_help <table name>

(where <table name> is the name of the table with the PRIMARY KEY whose name you want to know).

MS-SQL Server will respond with a description of the table identified by <table name>. Look in the index_name column of the section of the report titled PRIMARY to see the name the DBMS assigned to the PRIMARY KEY.

If you don't want the DBMS to generate its own name for the PRIMARY KEY, you can name it yourself by specifying the name when you identify the column that makes up the PRIMARY KEY. For example, to give the name pk_employee_table to the PRIMARY KEY in the current example, use the CREATE TABLE statement:

CREATE TABLE employee

(employee_id INTEGER,

CONSTRAINT pk_employee_table PRIMARY KEY,

first_name VARCHAR(20),

last_name VARCHAR(30))

Sometimes no single column in a table has a unique value in every row. Suppose, for example, that each division in your company issues its own employee numbers. Division #1 has employees #123, #124, and #126; division #2 has employees #121, #122, and #123. If you identify EMPLOYEE_ID as the PRIMARY KEY, you will be able to insert all division #1 employees into the EMPLOYEE table. However, when you try to insert division #2 employee #123, the DBMS will not allow you to do so. Because the EMPLOYEE table already has a row with an EMPLOYEE_ID of 123, the DBMS rejects your attempt to add a second row with 123 in the EMPLOYEE_ID column because EMPLOYEE_ID, the PRIMARY KEY, must be unique in each row of the table.

You can still create a PRIMARY KEY for the table where no single column is unique by identifying a set of multiple columns that, when taken together, is different in every row of the table. In the current example, you know that employee numbers are unique by division. Thus, you can use a two-column PRIMARY KEY consisting of EMPLOYEE_ID and DIVISION, such as

CREATE TABLE employee

(employee_id INTEGER,

division SMALLINT,

first_name VARCHAR(20),

last_name VARCHAR(30)

CONSTRAINT pk_employee_table

PRIMARY KEY (employee_id, division))

to create a PRIMARY KEY for the EMPLOYEE table.

Note The Placement of the PRIMARY KEY definition within the CREATE TABLE statement is not important. As such, the CREATE TABLE statement

CREATE TABLE employee

(employee_id INTEGER

CONSTRAINT pk_employee_table

PRIMARY KEY (employee_id, division),

division SMALLINT,

first_name VARCHAR(20),

last_name VARCHAR(30))

is equivalent to the one in the example just prior to this note.