Every record in a table should have a primary key that uniquely identifies the record. The primary key is usually just an integer. In a banking application, the primary key links a customer to their accounts.
The following SQL statements create and populate a customers table with the records for two customers:
CREATE TABLE customers (id INTEGER PRIMARY KEY, name CHAR(20), address CHAR(40)); INSERT INTO customers (id, name, address) VALUES (1, 'Smith', '123 Main Street'); INSERT INTO customers (id, name, address) VALUES (2, 'Jones', '456 Fifth Avenue');
The following SQL statement creates the accounts table:
CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance FLOAT, customer_id INT REFERENCES customers);
In the accounts table, the column customer_id is designated as a foreign key that points to the customers table (as indicated by the REFERENCES keyword). The value of this column is supposed to be exactly the same as the id value (the primary key) in the corresponding customer row in the customers table. In this way account rows are associated with customer rows. The feature of a database that allows such relationships to be maintained in a reliable way is called referential integrity, and the corresponding SQL syntax elements used to define such relationships are called referential integrity constraints. For more on referential integrity, see Referential integrity.
Customer Smith has two accounts, and customer Jones has one account.
INSERT INTO accounts (id, balance, customer_id) VALUES (1001, 200.00, 1); INSERT INTO accounts (id, balance, customer_id) VALUES (1002, 5000.00, 1); INSERT INTO accounts (id, balance, customer_id) VALUES (1003, 222.00, 2);
To list each customer and the accounts that they have, you use a “join” operation. The WHERE clause in the SELECT statement joins those pairs of records where the customer_id number in the accounts table matches the id number in the customers table.
SELECT name, balance FROM customers, accounts WHERE accounts.customer_id = customers.id;
The query might provide the following output:
NAME BALANCE Smith 200.0 Smith 5000.0 Jones 222.0
If a person has multiple accounts, they might want to know the total amount of money that they have in all accounts. This information can be provided by using the following query:
SELECT customers.id, SUM(balance) FROM customers, accounts WHERE accounts.customer_id = customers.id GROUP BY customers.id;
The query might provide the following output:
NAME BALANCE Smith 5200.0 Jones 222.0
This query uses the GROUP BY clause and an aggregate function named SUM() so that each customer is displayed only one time, along with the total amount of money in all their accounts.
Note that join operations are not limited to two tables. It is possible to create joins with an almost arbitrary number of tables. As a realistic extension of the banking example, you might have another table, payments, which holds information about each payment made. Thus, you would have not only a 1-to-many relationship from each customer to their accounts, but also a 1-to-many relationship from each account to all of the payments made from that account. It is quite possible to write a query that will list all the payments that a customer has made, even if that customer has multiple accounts.