Foreign key constraints

A foreign key joins a table to another table by referencing its primary key. A foreign key constraint specifies that the key can only contain values that are in the referenced primary key, and thus ensures the referential integrity of data that is joined on the two keys.

You can identify a table's foreign key when you create the table, or in an existing table with ALTER TABLE. For example, the following CREATE TABLE statement defines two foreign key constraints: fk_store_orders_store and fk_store_orders_vendor:

=> CREATE TABLE store.store_orders_fact(
    product_key int NOT NULL,
    product_version int NOT NULL,
    store_key int NOT NULL CONSTRAINT fk_store_orders_store REFERENCES store.store_dimension (store_key),
    vendor_key int NOT NULL CONSTRAINT fk_store_orders_vendor REFERENCES public.vendor_dimension (vendor_key),
    employee_key int NOT NULL,
    order_number int NOT NULL,
    date_ordered date,
    date_shipped date,
    expected_delivery_date date,
    date_delivered date,
    quantity_ordered int,
    quantity_delivered int,
    shipper_name varchar(32),
    unit_price int,
    shipping_cost int,
    total_order_cost int,
    quantity_in_stock int,
    reorder_level int,
    overstock_ceiling int
);

The following ALTER TABLE statement adds foreign key constraint fk_store_orders_employee to the same table:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
       FOREIGN KEY (employee_key) REFERENCES public.employee_dimension (employee_key);

The REFERENCES clause can omit the name of the referenced column if it is the same as the foreign key column name. For example, the following ALTER TABLE statement is equivalent to the one above:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
       FOREIGN KEY (employee_key) REFERENCES public.employee_dimension;

Multi-column foreign keys

If a foreign key refrences a primary key that contains multiple columns, the foreign key must contain the same number of columns. For example, the primary key for table public.product_dimension contains two columns, product_key and product_version. In this case, CREATE TABLE can define a foreign key constraint that references this primary key as follows:

=> CREATE TABLE store.store_orders_fact3(
    product_key int NOT NULL,
    product_version int NOT NULL,
    ...
   CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension (product_key, product_version)
);

CREATE TABLE

CREATE TABLE can specify multi-column foreign keys only after all table columns are defined. You can also specify the table's foreign key with a separate ALTER TABLE...ADD CONSTRAINT statement:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension (product_key, product_version);

In both examples, the constraint specifies the columns in the referenced table. If the referenced column names are the same as the foreign key column names, the REFERENCES clause can omit them. For example, the following ALTER TABLE statement is equivalent to the previous one:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension;

NULL values in foreign key

A foreign key that whose columns omit NOT NULL can contain NULL values, even if the primary key contains no NULL values. Thus, you can insert rows into the table even if their foreign key is not yet known.