As was previously discussed, a database key uniquely identifies a row in a table. In Tip 61, "Using the CREATE TABLE Statement to Assign the Primary Key," you learned that each row in a PRIMARY KEY uniquely identifies single row within the table in which the PRIMARY KEY is declared. A FOREIGN KEY, on the other hand, references the PRIMARY KEY in a table other than the one in which the FOREIGN KEY is declared. As such, each row within a FOREIGN KEY in one table uniquely identifies a single row in another table. While each PRIMARY KEY value must be unique within a table, the values within a FOREIGN KEY need not be (and most likely is not) unique.
A FOREIGN KEY is normally represents 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 a column (or combination of columns) within the row in the child table can be found in the column (or combination of columns) that makes up the PRIMARY KEY value of a specific row within the parent table.
The syntax of the FOREIGN KEY constraint declaration is:
[CONSTRAINT <constraint name>]
FOREIGN KEY (<column name>
[,<column name>...[,<last column name>]])
REFERENCES <foreign table name>
(<foreign table column name>
[,<foreign table column name>...
[,<last foreign table column name>]]}
Suppose, for example, that you want to track customer orders using the CUSTOMER (parent) table and ORDER (child) table created by:
CREATE TABLE customer
(customer_number INTEGER,
first_name VARCHAR(20),
last_name VARCHAR(30),
address VARCHAR(35),
CONSTRAINT pk_customer_table
PRIMARY KEY (customer_number))
CREATE TABLE order
(placed_by_customer_num INTEGER
FOREIGN KEY (placed_by_customer_num) REFERENCES
customer(customer_number),
order_date DATETIME,
item_number INTEGER,
quantity SMALLINT,
CONSTRAINT pk_order_table
PRIMARY KEY
(placed_by_customer_num, order_date, item_number))
The FOREIGN KEY defined in the ORDER table tells you that you can find the value in the PLACED_BY_CUSTOMER_NUM column (of the child table ORDER) in the PRIMARY KEY column CUSTOMER_NUMBER of a row in the CUSTOMER (parent) table.
Because the column PLACED_BY_CUST_NUM is a foreign and not a primary key in the ORDER table, you can have more than one ORDER row with the same value in the PLACED_BY_CUST_NUM column, indicating that an individual customer ordered more than one item or placed more than one order.
The FOREIGN KEY constraint on the PLACED_BY_CUSTOMER_NUM column also tells you that the value in the PLACED_BY_CUST_NUM column will appear once and only once in the CUSTOMER_NUMBER field of the CUSTOMER table (because a FOREIGN KEY in a table always refers to the PRIMARY KEY in another table). As such, you will be able to uniquely identify the customer that placed the order because the FOREIGN KEY value (PLACED_BY_CUSTOMER_NUM) in the current table uniquely identifies a row (a customer) in the foreign (CUSTOMER) table.
When you do not provide a name for a FOREIGN KEY constraint, the system will generate one for you so that it can store the constraint in its system tables. In the current example, the FOREIGN KEY in the ORDER table was not explicitly named. As such, the system will generate a name.
If you are using MS-SQL Server, you can determine the name of the FOREIGN KEY by executing the stored procedure sp_help and supplying the name of the parent table (CUSTOMER, in the current example) for <table name> in the statement:
EXEC sp_help <table name>
MS-SQL Server will respond with a description of the table and will list the FOREIGN KEY names in the "Table Is Referenced By" section of the report.
If you want to select a name for the FOREIGN KEY (instead of having the DBMS generate one), change the CREATE TABLE statement in the current example to:
CREATE TABLE order
(placed_by_customer_num INTEGER
CONSTRAINT fk_customer_table FOREIGN KEY
(placed_by_customer_num) REFERENCES
customer(customer_number),
order_date DATETIME,
item_number INTEGER,
quantity SMALLINT,
CONSTRAINT pk_order_table
PRIMARY KEY
(placed_by_customer_num, order_date, item_number))
As you learned in Tip 61, a PRIMARY KEY may consist of more than one column. When a FOREIGN KEY in one table references a composite (or multi-column) PRIMARY KEY in another table, the FOREIGN key, too, will consist of multiple columns. Suppose, for example, that the ITEM_MASTER table for the orders in the current example was defined by:
CREATE TABLE item_master
(item_number INTEGER,
vendor_id INTEGER,
quantity_on_hand SMALLINT
CONSTRAINT pk_item_master_table
PRIMARY KEY (item_number, vendor_id))
You could reference the composite PRIMARY KEY in the ITEM_MASTER table with the FOREIGN KEY constraint FK_ITEM_MASTER_TABLE using the CREATE TABLE statement:
CREATE TABLE order
(placed_by_customer_num INTEGER
order_date DATETIME,
item_number INTEGER,
vendor_id_number INTEGER,
quantity SMALLINT,
CONSTRAINT fk_item_master_table FOREIGN KEY
(item_number, vendor_id_number) REFERENCES
item_master (item_number, vendor_id),
CONSTRAINT fk_customer_table FOREIGN KEY
(placed_by_customer_num) REFERENCES
customer (customer_number),
CONSTRAINT pk_order_table
PRIMARY KEY
(placed_by_customer_num, order_date, item_number))
The important thing to know about the FOREIGN KEY constraint is that it specifies that the value in the column or combination of columns in one table must be found as the value in the PRIMARY KEY of the table, which it references. As such, if you have a single-column PRIMARY KEY, you will use a single-column FOREIGN KEY. Conversely, if you need to reference a multi-column or composite PRIMARY KEY, you will use a multi-column FOREIGN KEY.
