Multiple and table-level constraints

Ensuring that the data in the database is correct at the most basic layer ("data integrity") is fundamental. If you only write safety into the Node.js application, bugs could cause inconsistent data in the DB. Constraints at the DDL level are the last, impenetrable line of defense.

The main constraints

In addition to PRIMARY KEY (which makes a column unique and NOT NULL) and the classic NOT NULL and UNIQUE, there are two fundamental categories for the logical consistency of the data:

1. FOREIGN KEY (Referential Integrity)

A REFERENCES constraint ensures that a linking ID really exists in the "parent" table it points to. For example, an order must be linked to an existing customer.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id)
);

What does the Foreign Key do?

• It prevents you from inserting a customer_id that does not exist in customers.
• It prevents you from DELETE (deleting) a customer from customers if there are orders pointing to them (it protects against "orphan" records).

We can also decide what should happen when the "parent" is deleted:

customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE

With ON DELETE CASCADE, if we delete the customer, Postgres will automatically remove all linked orders! (Use with great caution; normally the default setting that blocks the operation is preferable.)

2. CHECK Constraints

A CHECK constraint validates the row by testing a boolean expression before proceeding to save.

CREATE TABLE contratti (
  id SERIAL PRIMARY KEY,
  stipendio NUMERIC(10,2) CHECK (stipendio > 0),
  data_inizio DATE,
  data_fine DATE,
  CHECK (data_fine > data_inizio)
);

The first CHECK is a column constraint (it only refers to stipendio). The second CHECK at the bottom is a table constraint (it can cross values from multiple columns, and must be declared at the bottom).

If an INSERT or UPDATE tries to save the end before the start, Postgres will reject it with an error!

Naming Constraints

It is often good practice to give constraints an explicit name (using the CONSTRAINT keyword). This way, when Postgres blocks a record, the error will tell you for example "constraint 'stipendio_positivo' violated", which is much clearer for debugging!

CREATE TABLE prodotti (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(50),
  CONSTRAINT nome_univoco UNIQUE(nome)
);

Try it

Constraints with custom logic