Foreign Key Constraints
A foreign key (FK) is a column that is used to join a table to other tables to ensure referential integrity of the data. A foreign key constraint requires that a column contain only values from the primary key column on a specific dimension table.
You can create a foreign key constraint in the CREATE TABLE statement, or you can define a foreign key constraint using ALTER TABLE.
A column with a foreign key constraint can contain NULL values if it does not also have a not NULL constraint, even though the NULL value does not appear in the dimension table's primary key column. This allows rows to be inserted into the table even if the foreign key is not yet known.
You can add a foreign key constraint by referencing the table that contains the primary key. The columns in the referenced table do not need to be specified explicitly.
Examples
Create a table called inventory
to store inventory data:
CREATE TABLE inventory ( date_key INTEGER NOT NULL, product_key INTEGER NOT NULL, warehouse_key INTEGER NOT NULL, ... );
Create a table called warehouse
to store warehouse information:
CREATE TABLE warehouse ( warehouse_key INTEGER NOT NULL PRIMARY KEY, warehouse_name VARCHAR(20), ... );
To ensure referential integrity between the inventory and warehouse tables, define a foreign key constraint called fk_inventory_warehouse
on the inventory
table that references the warehouse
table:
ALTER TABLE inventory ADD CONSTRAINT fk_inventory_warehouse FOREIGN KEY(warehouse_key) REFERENCES warehouse(warehouse_key);
In this example, the inventory
table is the referencing table and the warehouse
table is the referenced table.
You can also create the foreign key constraint in the CREATE TABLE statement that creates the inventory
table, eliminating the need for the ALTER TABLE statement. If you do not specify one or more columns, the PRIMARY KEY of the referenced table is used:
CREATE TABLE inventory (
date_key INTEGER NOT NULL,
product_key INTEGER NOT NULL,
warehouse_key INTEGER NOT NULL REFERENCES warehouse(warehouse_key),
...
);
A foreign key can also constrain and reference multiple columns. The following example uses CREATE TABLE to add a foreign key constraint to a pair of columns:
CREATE TABLE t1 ( c1 INTEGER PRIMARY KEY,
c2 INTEGER,
c3 INTEGER,
FOREIGN KEY (c2, c3) REFERENCES other_table (c1, c2)
);
The following two examples use ALTER TABLE to add a foreign key constraint to a pair of columns. When you use the CONSTRAINT keyword, you must specify a constraint name:
ALTER TABLE t ADD FOREIGN KEY (a, b) REFERENCES other_table(c, d); ALTER TABLE t ADD CONSTRAINT fk_cname FOREIGN KEY (a, b) REFERENCES other_table(c, d);
Note: The FOREIGN KEY keywords are valid only after the column definition, not on the column definition.