Using the ALTER TABLE Statement to Change Primary and Foreign Keys

In addition to changing the width and data type of a column, you can use the ALTER TABLE statement to add table constraints such as a PRIMARY KEY and FOREIGN KEY. Tip 61, "Using the CREATE TABLE Statement to Assign the Primary Key," and Tip 62, "Using the CREATE TABLE Statement to Assign Foreign Key Constraints," will show you how to define keys as part of the CREATE TABLE statement when creating a new database table. For existing tables, use the ALTER TABLE statement to add named and unnamed PRIMARY KEY and FOREIGN KEY constraints.

Both PRIMARY KEY and FOREIGN KEY constraints are database "keys," which means that each is a column or a combination of columns that uniquely identifies a row in a table. While a PRIMARY key uniquely identifies a row in the table in which it is defined, a FOREIGN KEY uniquely identifies a row in another table. (A FOREIGN KEY in one table always references the PRIMARY key in another table.)

A table can have only one PRIMARY KEY, but it can have several FOREIGN KEYS. While the value of the column or combination of columns that makes up a PRIMARY key must be unique for each row in the table, the value of the column or combination of columns that makes up a FOREIGN KEY need not be (and most likely are not) unique within the table in which the FOREIGN KEY is defined.

You will learn more about the PRIMARY KEY constraint in Tip 172, "Using the PRIMARY KEY Column Constraint to Uniquely Identify Rows in a Table," and the FOREIGN KEY constraints in Tip 173, "Understanding Foreign Keys."

The syntax for using the ALTER TABLE statement to add a PRIMARY KEY constraint to a table is:

ALTER TABLE <table name> ADD CONSTRAINT

<constraint name> PRIMARY KEY (<column name>

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

Therefore if you have a table created by

CREATE TABLE employee

(employee_id INTEGER NOT NULL,

badge_number SMALLINT NOT NULL,

first_name VARCHAR(20),

last_name VARCHAR(30))

you can add a single column PRIMARY KEY based on the EMPLOYEE_ID using the statement:

ALTER TABLE employee

ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id)

If the values in a single column are not unique in each row of the table, but the values in a combination of columns are, you must use a composite, or multi-column PRIMARY KEY. Suppose, for example, that you had an EMPLOYEE table where two employees could have the same employee number, but no two employees with the same employee number would have the same badge number. To add the PRIMARY KEY to the table, use the ALTER TABLE statement

ALTER TABLE employee

ADD CONSTRAINT pk_employee

PRIMARY KEY (employee_id,badge_number)

to combine EMPLOYEE_ID and BADGE_NUMBER into a single, unique key value for each row of the table.

Note Since a table can have only one PRIMARY KEY, you just first use the ALTER TABLE statement

ALTER TABLE <table name> DROP CONSTRAINT <constraint name>

if you want to change a table's PRIMARY KEY. In other words, you must remove a table's existing PRIMARY KEY before you can use the ALTER TABLE statement to add a new PRIMARY KEY constraint to the table.

Adding FOREIGN KEY constraints to a table is similar to defining the PRIMARY KEY. However, when working with a FOREIGN KEY, you must identity not only the columns in the current table that make up the FOREIGN KEY but also the PRIMARY KEY columns in the table referenced by the FOREIGN KEY.

A FOREIGN KEY constraint is normally used to represent a parent/child relationship between two tables. When you place a FOREIGN KEY constraint on a column or combination of columns, you are saying that the value in the column (or combination of columns) in the child record (row in the child table) can be found in the column (or combination of columns) that makes up the PRIMARY KEY in the parent record (row in the parent table).

The syntax for using the ALTER TABLE statement to add a FOREIGN KEY constraint to a table is:

ALTER TABLE <table name>

[WITH NOCHECK]

ADD [CONSTRAINT <constraint name] FOREIGN KEY

(<column name>[,<column name>...,<last column name>]

REFERENCES <foreign table name>

(<foreign column name>

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

As such, to create a parent/child relationship between a (parent) CUSTOMER table created by

CREATE TABLE customer

(customer_number INTEGER PRIMARY KEY,

first_name VARCHAR(20),

last_name VARCHAR(30),

address VARCHAR(35))

and a (child) ORDER table created by

CREATE TABLE order

(placed_by_customer_num INTEGER,

order_date DATETIME,

item_number INTEGER,

quantity SMALLINT)

you might use the ALTER TABLE statement

ALTER TABLE order ADD

CONSTRAINT fk_order_column

FOREIGN KEY (placed_by_customer_num)

REFERENCES customer (customer_number)

which links each row in the ORDER table to one (and only one) of the rows in the CUSTOMER table. In other words, each and every value in the PLACED BY_CUSTOMER_NUM column in the ORDER table can be found in the CUSTOMER_NUMBER column of the customer table. Thus, every order (child) must have an associated customer (parent) that placed the order.

Note When you use the ALTER TABLE statement to add a FOREIGN KEY to a table, the DBMS will check existing data to make sure it does not violate the constraint. In the current example, the DBMS will ensure that every value in the PLACED_BY_CUSTOMER_NUM of the ORDER table exists in the CUSTOMER_NUMBER table of the CUSTOMER table. If the check fails, the DBMS will not create the FOREIGN KEY, thereby maintaining referential data integrity.

If you are sure that existing data will not violate the FOREIGN KEY constraint, you can speed up the execution of the ALTER TABLE statement by adding the WITH NOCHECK clause. If you do so, the DBMS will not apply the FOREIGN KEY constraint to existing rows of the table. Only rows subsequently updated or inserted will be checked to make sure that the FOREIGN KEY value exists in the PRIMARY KEY of the referenced (parent) table.