Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

The relational model

The relational model behind SQL: relations, tuples, attributes, domains, primary and foreign keys, relational algebra operators and how SQL differs from the theory.

The relational model is the theory SQL is built on. Edgar F. Codd proposed it in 1970 at IBM: store data as relations (tables) of facts, and describe what you want with a small set of operations rather than by following pointers. The database decides how to find the data.

SQL borrows the model's ideas but bends them in places. Knowing the original vocabulary makes SQL's behavior easier to predict, especially for keys, joins and duplicates.

The vocabulary

Theory SQL Meaning
Relation Table A set of facts of the same shape
Tuple Row One fact
Attribute Column A named property with a domain
Domain Data type (plus constraints) The allowed values of an attribute
Degree, cardinality Column count, row count The size of the relation
Candidate key UNIQUE / PRIMARY KEY A minimal set of attributes that identifies a tuple
Foreign key FOREIGN KEY ... REFERENCES Attributes that point to a key of another relation
Null NULL A marker for a missing or unknown value (not in the original theory)

In a relation, tuples are unordered and have no duplicates, and each attribute holds one value per tuple. A SQL table may contain duplicate rows unless a key forbids them, and a query result is only ordered if you say ORDER BY.

Keys and integrity

  • Primary key: the chosen identifier of each row. It must be unique and not null.
  • Candidate keys: every column set that could serve; the rest are enforced with UNIQUE.
  • Foreign key: a column whose values must exist in the key column of another table. This links tables and prevents orphans.
  • Entity integrity: no part of a primary key is null. Referential integrity: every foreign key value matches a key or is null.
  • Domain constraints: types, NOT NULL, CHECK, defaults.

Try it

SQL Tables, keys and a foreign key in the bookstore ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Referential integrity in action (fails on purpose) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Relational algebra and SQL

Codd's algebra has a handful of operators that take relations and return relations. Because the result is again a relation, they combine freely, which is what makes subqueries and views possible.

Operator Purpose SQL
Selection (sigma) Keep tuples that satisfy a condition WHERE
Projection (pi) Keep chosen attributes the select list (and DISTINCT)
Union Tuples in either relation UNION
Difference Tuples in the first but not the second EXCEPT (MINUS)
Intersection Tuples in both INTERSECT
Cartesian product All pairs of tuples CROSS JOIN
Join Product followed by selection JOIN ... ON
Rename Change relation or attribute names AS
Division Tuples related to all of another relation no keyword; NOT EXISTS twice
SQL Selection, projection and join as SQL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Where SQL departs from the model

  • Duplicates. SQL tables and results are bags, not sets. UNION ALL and plain SELECT keep duplicates; DISTINCT and UNION remove them.
  • Order. Rows have no inherent order. Only ORDER BY guarantees one.
  • Nulls and three-valued logic. A comparison with NULL is neither true nor false but unknown, which makes NOT IN and joins behave in ways pure relations would not.
  • Column order and duplicate column names exist in SQL results; attributes in the model are unordered and unique.
  • Pointers are not part of the model. You relate rows by matching values, not by following links; keys and joins do that job.
  • Updatable views and triggers go beyond the algebra.

Dialect differences

The model is the same everywhere; how strictly products honor it differs.

  • SQLite allows any value in any column unless the table is STRICT, and ignores foreign keys unless enabled. The other four systems enforce types and foreign keys by default.
  • Oracle treats the empty string as NULL, which no other product does.
  • MySQL's older storage engines (MyISAM) accepted foreign key syntax but did not enforce it; InnoDB does.
  • INTERSECT/EXCEPT are missing from very old MySQL, and Oracle called difference MINUS until 21c.

Usage notes

  • Give every table a primary key. Without one, you cannot reliably identify or update a single row.
  • Model one thing per table, and relate tables with foreign keys. See normalization.
  • Think in sets. Instead of looping over rows, describe the whole result; the optimizer chooses the plan.
  • Use constraints to make the database refuse bad data, instead of trusting every application to do the checking.
  • Expect to design with ER diagrams: entities become tables, relationships become foreign keys or join tables.