Contraintes multiples et au niveau de la table

Il est fondamental de garantir que les données de la base de données sont correctes au niveau le plus élémentaire (« intégrité des données »). Si vous écrivez uniquement la sécurité dans l'application Node.js, des bogues pourraient provoquer des données incohérentes dans la base de données. Les contraintes au niveau DDL constituent la dernière ligne de défense impénétrable.

Les principales contraintes

En plus du PRIMARY KEY (qui rend une colonne unique et NOT NULL) et des classiques NOT NULL et UNIQUE, il existe deux catégories fondamentales pour la cohérence logique des données :

1. CLÉ ÉTRANGÈRE (intégrité référentielle)

Une contrainte REFERENCES garantit qu'un ID de liaison existe réellement dans la table "parent" vers laquelle il pointe. Par exemple, une commande doit être liée à un client existant.

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

À quoi sert la clé étrangère ?

• Cela vous évite d'insérer un customer_id qui n'existe pas dans customers.
• Il vous empêche de DELETE (supprimer) un client de customers s'il y a des commandes pointant vers lui (cela protège des enregistrements "orphelins").

Nous pouvons également décider de ce qui doit se passer lorsque le « parent » est supprimé :

customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE

Avec ON DELETE CASCADE, si nous supprimons le client, Postgres supprimera automatiquement tous les orders liés ! (À utiliser avec beaucoup de prudence ; normalement, le paramètre par défaut qui bloque l’opération est préférable.)

2. VÉRIFIER les contraintes

Une contrainte CHECK valide la ligne en testant une expression booléenne avant de procéder à la sauvegarde.

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)
);

Le premier CHECK est une contrainte de colonne (elle fait uniquement référence à stipendio). Le deuxième CHECK en bas est une contrainte de table (elle peut croiser les valeurs de plusieurs colonnes et doit être déclarée en bas).

Si un INSERT ou UPDATE tente de sauvegarder la fin avant le début, Postgres le rejettera avec une erreur !

Contraintes de nommage

Il est souvent recommandé de donner un nom explicite aux contraintes (en utilisant le mot-clé CONSTRAINT). De cette façon, lorsque Postgres bloque un enregistrement, l'erreur vous indiquera par exemple "contrainte 'stipendio_positivo' violée", ce qui est beaucoup plus clair pour le débogage !

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

Essayez-le

Contraintes avec logique personnalisée