Subsections

1.4.12.11 FOREIGN KEY and Joins

Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join. Joins and the use of primary and foreign keys are inter-related.


1.4.12.11.1 FOREIGN KEY

Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

In the People table below, the LastName column is the primary key, meaning that no two rows can have the same LastName. The LastName distinguishes two persons even if they have the same name.

When you look at the example tables below, notice that:

People
+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| Smith    | John      | 10     | 1980-01-01  |
+----------+-----------+--------+-------------+
| Doe      | James     | 3      | 1981-12-25  |
+----------+-----------+--------+-------------+

Houses
+-------+-------+
| House | Owner |
+-------+-------+
| 1     | Smith |
+-------+-------+
| 2     | Smith |
+-------+-------+
| 3     | Doe   |
+-------+-------+

People may own more than one house. In our example John Smith owns both House 1 and 2. In order to keep the database consistent you would not want to remove Smith from the People table or drop the People table because the Houses table would still contain a reference to Smith. Similarly you wouldn't want to insert or update a value in the Owner column of the Houses table which didn't exist as a primary key for the People table.

By specifying the Owner column of the Houses table as a foregin key these constraints are enforced by web.database.

The SQL for the tables is below. Note: The line breaks in the first CREATE statement are for formatting; web.database doesn't support line breaks in SQL.

CREATE TABLE People (
    LastName String PRIMARY KEY, FirstName String, 
    Number Integer, DateOfBirth Date
)
CREATE TABLE Houses (House Integer, Owner String FOREIGN KEY=People)

If a column is specified FOREIGN KEY it cannot have any other options. The table specified as providing the foreign key must have a primary key. It is the primary key value which is used as a foreign key in the other table.

Bool, Float, Text and Binary fields cannot be foreign key fields.

We can select data from two tables by referring to two tables, using the SQL below. Note: The line breaks are just for formatting; web.database doesn't support line breaks in SQL.

SELECT Houses.House, People.FirstName, Houses.Owner 
FROM People, Houses 
WHERE People.LastName=Houses.Owner

Here is the result

+--------------+------------------+--------------+
| Houses.House | People.FirstName | Houses.Owner |
+--------------+------------------+--------------+
| 1            | 'John'           | 'Smith'      |
| 2            | 'John'           | 'Smith'      |
| 3            | 'James'          | 'Doe'        |
+--------------+------------------+--------------+

and another example:

SELECT Houses.House, People.FirstName, Houses.Owner 
FROM People, Houses 
WHERE People.LastName=Houses.Owner  and People.DateOfBirth<'1981-01-01'

Here is the result

+--------------+------------------+--------------+
| Houses.House | People.FirstName | Houses.Owner |
+--------------+------------------+--------------+
| 1            | 'John'           | 'Smith'      |
| 2            | 'John'           | 'Smith'      |
+--------------+------------------+--------------+