Data Definition Language (SQL DDL)

A foreign key is a constraint that enforces the consistency of two tables, thus ensuring their so-called referential integrity. This kind of integrity means it’s always possible to get full information about an object even if this information is distributed among several tables. The reasons for such a distribution lie in the relational model design principles, and will be examined later.

The relationship between tables isn’t equivalent. There’s always a parent and a child table. There are two types of relationship: one-to-one and one-to-many. A one-to-one relationship means that a row in the parent table has no more than one (that is, one or none) corresponding row in the child table. For a one-to-many relationship, a row in the parent table may refer to any number (including zero) of rows in the child table.

The relationship is established by having equal values of certain columns in the parent and child tables. The column (or a set of columns in the case of a compound key) in the child table lined to a column (or a set of columns) in the parent table is called a foreign key.

Since in any relationship, the parent table is on the “one” side, its column involved in the foreign key relationship should have a PRIMARY KEY or UNIQUE constraint applied. And the foreign key is created by means of the FOREIGN KEY specification:

FOREIGN KEY(< list of COLUMNS 1 >
 REFERENCES < name of the parent TABLE >(< list of COLUMNS  2 >)

The number of columns in the lists 1 and 2 must be equal, and their data types must be compatible for each pair of columns.

That’s how a foreign key for the PC table can be created:

ALTER TABLE PC
ADD CONSTRAINT fk_pc_product
FOREIGN KEY(model) REFERENCES Product(model);

Note: for the parent table, the column in parentheses needn’t to be specified if it is a primary key, since there can be only one primary key in a table. That’s exactly our case; therefore, the last line can be rewritten as follows: 

FOREIGN KEY(model) REFERENCES Product;

Foreign keys for the Printer and Laptop tables are created in a similar manner.