A foreign key is a column that references a primary key of another table. The foreign key value either is NULL or exists as the primary key value. The table that contains the foreign key is called the referencing table. The table that contains the primary key is called the referenced table.
During INSERT or UPDATE operations on a table containing a foreign key, the database checks to determine if the foreign key value matches a corresponding primary key value. If it does not match, the operation returns an error.
During UPDATE or DELETE operations on a table containing a primary or candidate key, if the values to be deleted or updated match the foreign key of the referencing table, the operation returns an error. A value corresponding to a primary or candidate key cannot be updated or deleted if there are references to it.
When you want to drop a table containing a primary or candidate key, the database checks to see if the table has any references to it. If there are tables containing foreign keys that reference the primary or candidate keys of the table you want to drop, the operation returns an error.
In the following example, item_no is the foreign key referencing the item table, and the foreign key is specified at the column level.
CREATE TABLE supplier_item
(
supp_no INTEGER NOT NULL PRIMARY KEY,
item_no INTEGER NOT NULL REFERENCES item,
qty INTEGER
) ;
If a foreign key references a candidate key, you must name the referenced column in a column list. If a foreign key references a primary key, the column list is optional.
The following example illustrates both conditions. In the example, invoice.item_no references the primary key of the item table. The invoice.partnum column references parts.part_no. Since parts.part_no is a primary key, the parts (part_no) column list reference in invoice.part_no is optional.
CREATE TABLE invoice (
inv_no INTEGER NOT NULL PRIMARY KEY,
item_no INTEGER REFERENCES item,
part_no CHAR(3) NOT NULL
REFERENCES parts (part_no),
qty INTEGER NOT NULL
) ;
