Creating views with foreign keys

We utilise foreign and Primary_key to create relationships between tables in SQL. Foreign keys link data in one table to the data in another table and are how we cross-reference data in SQL.

In essence you use the primary key of one table to access data in another table. When one table A contains the primary key of another table B as a field, that key is “foreign” to A hence the name.

Let’s say we have a sales table:

In SQL this would be set up as follows:

CREATE TABLE sales (
  saleId integer PRIMARY KEY,
  modelId integer,
  saleDate date,
  employeeId text,
  price float
)

For every model that is sold it is possible for the customer to return it. This data will be kept in another table returns. Every model sold will have an entry in sales but may or may not have an entry in returns (not every customer will return an item).

We want to establish a relationship between the two tables so that if an item is returned we can trace it back to its original sale.

As the saleId is the primary key in sales this means it is a unique identifier for each model sold. We will therefore use this in our returns table to track the sale data.

Here’s our returns table:

In this table saleId is identical to saleId in sales. It is the primary key in sales but a foreign key in returns. If a device has been returned it must have an entry in returns and the salesId of the entry in returns must match the salesId in sales.

This is the primary benefit of utilising foreign keys: they add a restriction. Entries to the table with a foreign key must have a value that corresponds with the foreign key column.

We call this a foreign key contraint. More explicitly, our contraint is as follows:

Any value entered into returns.saleId must already exist in sales.salesId

A secondary benefit is that they save us the trouble of repeating data. Without foreign keys we would have to input saleId twice in two different tables.

We establish the foreign key reference with ther following SQL:

CREATE TABLE returns (
	returnId integer PRIMARY KEY,
	saleId integer NOT NULL,
	returnDate date,
	reason text,
	FOREIGN KEY (sale_id) REFERENCES sales(sale_id)
	);

A table can have more than one foreign key.

If you delete the source of the foreign key, you also delete its references in tables for which it is a foreign key. This is important to remember. So if a row was deleted from sales the row in returns with the corresponding saleId would also be deleted.