Foreign Key - Example

Example

As a first example to illustrate foreign keys, suppose an accounts database has a table with invoices and each invoice is associated with a particular supplier. Supplier details (such as name and address) are kept in a separate table; each supplier is given a 'supplier number' to identify it. Each invoice record has an attribute containing the supplier number for that invoice. Then, the 'supplier number' is the primary key in the Supplier table. The foreign key in the Invoices table points to that primary key. The relational schema is the following. Primary keys are marked in bold, and foreign keys are marked in italics.

Supplier ( SupplierNumber, Name, Address, Type ) Invoices ( InvoiceNumber, SupplierNumber, Text )

The corresponding Data Definition Language statement is as follows.

CREATE TABLE Supplier ( SupplierNumber INTEGER NOT NULL, Name VARCHAR(20) NOT NULL, Address VARCHAR(50) NOT NULL, TYPE VARCHAR(10), CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber), CONSTRAINT number_value CHECK (SupplierNumber > 0) ) CREATE TABLE Invoices ( InvoiceNumber INTEGER NOT NULL, SupplierNumber INTEGER NOT NULL, Text VARCHAR(4096), CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber), CONSTRAINT inumber_value CHECK (InvoiceNumber > 0), CONSTRAINT supplier_fk FOREIGN KEY(SupplierNumber) REFERENCES Supplier(SupplierNumber) ON UPDATE CASCADE ON DELETE RESTRICT )