Relational Model and SQL

Tables, rows, and columns

A relational database stores related facts in tables. A table has named columns, and each row holds one value for each column. The column definition gives those values a meaning and a type.

For example, a customers table stores one row per customer:

Read across a row to describe one customer. Read down a column to compare the same fact across customers. This basic picture comes before keys.

Keys that identify rows

A primary key identifies a row with one column or a group of columns. In standard SQL, its values must be unique and not null. A missing value cannot identify a row, and a duplicate would identify more than one row.

Other databases: SQLite is the exception. In an ordinary SQLite table, a primary-key column can accept NULL unless you also declare it NOT NULL.

For customers, customer_number is the primary key. The table already contains customer 10, so another row with customer_number 10 would create two rows with the same identity. The database rejects that insert.

A table may have several unique constraints but only one primary key. A unique constraint can protect another business value, such as an email address.

Foreign keys relate tables

A foreign key stores a reference to a key in another table, or in the same table. A non-null foreign-key value must match a value in a referenced row. This protects referential integrity.

The orders table can store the customer number for each order:

Here, customer_number is a foreign key. The value 10 matches Alice in customers. An order with customer_number 99 fails because the customers table has no row with that key.

Columns covered by a primary key or suitable unique constraint give the reference one row to match. NOT NULL alone is not enough because duplicate values are still possible.

SQL statements and clauses

A SQL statement is a complete command. A clause is a named part of that command. This query is one statement:

SELECT price * quantity AS total
FROM order_items
WHERE quantity > 1;

The select list states the values that appear in the output. It is on the first line. The FROM clause names the input table. The WHERE clause keeps only rows that meet its condition. Together, FROM and its following clauses form the query's table expression.

Expressions and table expressions

An expression calculates a value. A column reference, a literal such as 2, and price * quantity are value expressions. A value expression produces one scalar value. In everyday words, a scalar is one value, such as 5 or 30.

In SELECT 2 + 3;, the expression produces 5, and the query returns that value in one column:

A table expression produces rows and columns that the rest of the query can use. It describes a set of data rather than one calculated value.

Queries and result sets

A query retrieves data, and SQL uses SELECT to specify a query. The select list contains the output expressions. These are the rows in order_items:

Run the same statement:

SELECT price * quantity AS total
FROM order_items
WHERE quantity > 1;

WHERE removes item B because its quantity is 1. The select-list expression multiplies the remaining rows: 15 * 2 gives 30, and 10 * 5 gives 50.

The rows and columns returned by a query form its result set:

In this command, FROM and WHERE are clauses, the multiplication and comparison are expressions, and the returned total values belong to the result set.

Designing a relationship

Suppose customer_number is the stable identity for each customer, email must also reject duplicates, and every order must name an existing customer.

Make customer_number the primary key. Add a separate unique constraint to email. Store customer_number in orders, declare that column NOT NULL, and add a foreign key that references the customer key.

One primary key supplies the declared identity, and a unique constraint protects another business value. Together, NOT NULL and the foreign key do the third job: NOT NULL requires a customer value, and the foreign key checks that this customer exists. A foreign key on its own would still let an order arrive with no customer at all, because a null reference has nothing to check.

Key takeaways

Tables contain rows and named columns. In standard SQL, primary-key values must be unique and not null. In an ordinary SQLite table, add NOT NULL to enforce the second rule. A table can declare at most one primary key, while separate unique constraints can protect other values. A foreign key connects a row to an existing referenced key.

The select list states the output values. A value expression calculates one scalar value. A table expression supplies rows and columns for the query to use. A SELECT query is a statement that contains clauses and expressions and produces a result set.