We have a CLIENTES relation and can distinguish its schema from its contents. Now we need orders. We could enter any customer identifier and trust people not to make mistakes. But a database should help maintain consistency, not simply hope for it.
Here is a possible extension of PEDIDOS in the fictional shop from part one. The names remain in Spanish to keep one shared example: importe is the amount, estado the status and fecha_envio the shipping date.
| pedido_id | cliente_id | importe | estado | fecha_envio |
|---|---|---|---|---|
| 101 | 1 | 120.00 | pagado | 2026-09-03 |
| 102 | 1 | 180.00 | pagado | NULL |
| 103 | 2 | 40.00 | pagado | 2026-09-04 |
| 104 | 3 | 150.00 | cancelado | NULL |
| 105 | 3 | 110.00 | pagado | NULL |
The relation has degree 5 and cardinality 5. Its schema is PEDIDOS(pedido_id, cliente_id, importe, estado, fecha_envio). We allow orders not yet shipped, but require every order to belong to a registered customer.
A foreign key points to a customer who exists
pedido_id identifies the order. cliente_id does something else: it identifies its buyer. We define it as a foreign key referencing the primary key of CLIENTES.
The value 1 appears in several orders without causing a problem. Ana can buy more than once. A foreign key does not have to be unique in the relation containing it. Here, uniqueness belongs to pedido_id.
The connection uses values, not row positions or a pointer stored in the cell. Order 101 contains cliente_id = 1; looking up that identifier in CLIENTES leads to Ana.
A foreign key can contain several attributes and can reference its own relation. An employee’s responsable_id, for example, could refer to another employee. A composite referenced key needs the corresponding set of foreign-key attributes with compatible domains. In SQL, a foreign key can reference a suitable unique key as well as a primary key.
Four checks with different jobs
Integrity means respecting the model’s conditions and the rules we define for the information represented. It does not prove that a value is true: an incorrectly recorded amount of 120 euros may pass every constraint. It can reject contradictions we know how to identify.
Primary-key uniqueness
Two orders cannot both have pedido_id = 101, even with different amounts. Looking up order 101 should not leave us guessing which one was intended.
For a composite key, it is the combination that must be unique. LINEAS_PEDIDO can repeat an order identifier and a line number individually, but not their complete pair.
Entity integrity
No primary-key attribute can be null. An order with an unknown identifier lacks a complete identity in our model.
Uniqueness and entity integrity address different problems: one prevents two identical identities; the other prevents an incomplete identity. An SQL PRIMARY KEY declaration requires both.
Referential integrity
An order with cliente_id = 99 is invalid if customer 99 does not exist. The foreign key must identify an existing tuple, or allow an absent reference where the design permits nulls. Our shop requires a customer, so that column cannot contain NULL either.
This must hold beyond order creation. Changing an order’s customer, deleting a referenced customer or changing their identifier can also break the connection. A reference valid yesterday should not silently become invalid tomorrow.
Domain integrity
Non-null values must belong to their attribute’s domain. estado = 'azul' falls outside our three allowed statuses. importe = -20 is invalid if we define the domain as nonnegative amounts.
Meaning matters for operations too. A customer identifier and an amount can both be stored as numbers, but comparing them does not tell us who bought more. Database systems catch many incompatible types, but not necessarily every semantic incompatibility between user-defined domains. An expression that runs is not automatically meaningful.
The model’s rules and this shop’s rules
Not every restriction has the same origin. Identity and references belong to the general model. Requiring nonnegative order amounts or forbidding a shipping date on a cancelled order is specific to our example: a user-defined integrity constraint, also called a business rule.
Another system might use a negative amount to represent a refund. It might allow cancellation after shipping. Our example’s decisions are not universal laws.
One requirement can have several representations. Nonnegative amounts could be expressed through a user-defined domain or a column constraint. What matters is having the database enforce the requirement, not merely leaving a code comment about it.
NULL is not an invented date
In our table, fecha_envio = NULL means no shipping date is recorded. The order may not have shipped yet, or shipping may be inapplicable because it was cancelled. One marker cannot distinguish all these situations by itself, which is why status is also recorded.
NULL is not zero or an empty string, and is not an ordinary value in the date domain. It marks absent information. Substituting an invented date asserts something we do not know and may later contaminate a calculation.
SQL tests for it with IS NULL, not = NULL. Ordinary comparisons involving unknown information can yield unknown rather than true or false. WHERE only keeps rows for which its condition is true.
A less obvious consequence: CHECK (importe >= 0) does not make an amount mandatory. PostgreSQL also accepts an unknown result for a CHECK, so we need NOT NULL separately. The PostgreSQL constraint documentation explains this and foreign-key options.
What happens if we delete Ana?
Ana has orders 101 and 102. Deleting her customer record alone would leave both pointing to someone who no longer exists. The design must choose the behavior that fits the meaning of the data.
| Policy | Effect of deleting the customer | When it might fit |
|---|---|---|
| Restrict | Reject the deletion while orders reference the customer. | We want to retain the orders and their reference. |
| Cascade | Delete the orders that reference the customer too. | Dependent records should disappear with their parent. |
| Set null | Retain orders, but change their customer reference to NULL. |
Orders without an associated customer are allowed. |
Setting null does not fit our mandatory column. Cascading historical orders could erase information we need to keep. It is not a convenience option to choose without considering the consequences.
Policies can also apply when the referenced key changes. Restricting deletion while cascading an identifier update is a coherent choice: changing a key and deleting an entity need not have the same treatment.
Turning the decisions into SQL
This small schema uses PostgreSQL syntax. It includes our shop’s rule that cancelled orders cannot have a shipping date; it does not attempt to cover every situation in a real business.
CREATE TABLE clientes (
cliente_id INTEGER PRIMARY KEY CHECK (cliente_id > 0),
nombre TEXT NOT NULL CHECK (trim(nombre) <> ''),
email TEXT NOT NULL UNIQUE,
ciudad TEXT NOT NULL
);
CREATE TABLE pedidos (
pedido_id INTEGER PRIMARY KEY CHECK (pedido_id > 0),
cliente_id INTEGER NOT NULL,
importe NUMERIC(10, 2) NOT NULL CHECK (importe >= 0),
estado TEXT NOT NULL
CHECK (estado IN ('pendiente', 'pagado', 'cancelado')),
fecha_envio DATE,
FOREIGN KEY (cliente_id) REFERENCES clientes(cliente_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
CHECK (estado <> 'cancelado' OR fecha_envio IS NULL)
);The primary key provides uniqueness and excludes nulls. UNIQUE with NOT NULL preserves email’s candidate-key status under our rules, though it does not validate email format. The CHECK constraints narrow allowed values. The foreign key prevents orphan orders. Each declaration has a specific job.
Now we can ask the data questions
The relational model covers more than structure and integrity. It also covers manipulation: inserting, deleting and modifying tuples, and querying information from relations.
Relational algebra describes queries using operations that take relations and return another relation. This property is called relational closure: one result can become the input to the next step.
Selection keeps tuples satisfying a condition. Projection keeps chosen attributes. A join combines tuples satisfying a condition between relations. We can select paid orders over 100 euros, join them to CLIENTES through the identifier, then project customer identifier and name. Our data returns Ana and Carla.
We can also unite compatible sets, find their intersection or calculate a difference. To find customers without orders, subtract customer identifiers present in PEDIDOS from those in CLIENTES: Diego remains. We are comparing compatible relations, not subtracting whole tables with different schemas.
The vocabulary feels less abstract once each word has a referent. We know what a tuple represents, what identifies it and what protects its connections. Before writing a complicated query, I would try explaining the table in these terms. If I still cannot say what a row represents or what its key identifies, another JOIN will not fix the problem.
Found this useful? If you would like to support this space, you can buy me a coffee.
Buy me a coffee Optional support through PayPal. You choose the amount.
