### 1.4.11.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.11.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:

- The `LastName` column is the primary key of the `People` table
- The `House` column is the primary key of the `Houses` table
- The `Owner` column in the `House` table is used to refer to the people in the `People` table. `Owner` is a foreign key field.

    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'      |
    +--------------+------------------+--------------+