 |
 |
 |
Module Reference |  |
 |
 |
|
|
|
Module Reference | |
|
|
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.
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:
LastName column is the primary key of the People table
House column is the primary key of the Houses table
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' | +--------------+------------------+--------------+
|
|
|
Module Reference | |
|
|