# Normalisation

La table vehicule créée précédemment comporte une erreur dans sa conception. En effet, elle ne respecte pas la 3FN: la marque dépend du modele et on a donc une redondance dans les noms de marques. En cas de changement de nom, il faudrait donc modifier toutes les entrées de la table vehicule qui correspondent à la marque à renommer. En cas d'oubli, les données ne sont plus fiables.

La résolution de cette erreur de normalisation consiste en la création d'une nouvelle table dédiée aux marques:

modele (1, 1) - (1, n) marque
Un modèle correspond à 1 marque et une marque crée au moins un modèle. Les cardinalités maximales (à droite) constituent une relation OneToMany.

On va alors créer une table marque qui sera référencée dans la table vehicule par sa clé primaire, il s'agira alors d'une clé étrangère:

marque:

vehicule:

# Correction

Plutôt que de supprimer la table et de recommencer, essayons de corriger cette erreur sans perdre les données. C'est un scénario qu'il est important de maîtriser dans le cas d'une application en production.

On crée la nouvelle table des marques avec sa séquence:

CREATE SEQUENCE seq_marque_id INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE marque (
    id INT NOT NULL,
    nom VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
);

Puis on insère les marques déjà existantes en se basant sur les entrées de la table vehicule:

INSERT INTO marque (id, nom)
SELECT nextval('seq_marque_id'), marque
FROM (SELECT DISTINCT marque FROM vehicule) v;

Enfin, on ajoute une nouvelle colonne marque_id dans la table vehicule qui va référencer la clé primaire de la table marque via une contrainte de clé étrangère. Cette contrainte va obliger à ce que les valeurs de vehicule.marque_id existent bien dans marque.id.

-- création de la nouvelle colonne avec NULL en valeur par défaut pour les lignes existantes
ALTER TABLE vehicule ADD COLUMN marque_id INT DEFAULT NULL;

-- remplissage de la nouvelle colonne avec les ID des marques correspondantes
UPDATE vehicule v
SET marque_id = m.id
FROM marque m
WHERE v.marque = m.nom;

-- nettoyage des colonnes et création de la contrainte de clé étrangère
ALTER TABLE vehicule
    ALTER COLUMN marque_id SET NOT NULL
    , DROP COLUMN marque
    , ADD CONSTRAINT fk_vehicule_marque FOREIGN KEY (marque_id) REFERENCES marque (id);

Désormais l'insertion d'une ligne dans la table vehicule échouera si la valeur de marque_id n'existe pas dans la table marque:

INSERT INTO vehicule (id, modele, marque_id) VALUES (7, 'Wrangler', 999);

[23503] ERROR: insert or update on table "vehicule" violates foreign key constraint "fk_vehicule_marque"
Detail: Key (marque_id)=(999) is not present in table "marque".

# Événements

En dehors de l'insertion, les contraintes de clés étrangères peuvent affecter les autres opérations d'écriture: la modification et suppression des entrées référencées (table "parent"). On peut adapter le comportement du SGBD selon nos besoins pour empêcher l'opération ou la répercuter sur les entrées qui référencent (table "enfant").

Essayons de supprimer une ligne de la table marque:

DELETE FROM marque WHERE nom = 'Toyota';

[23503] ERROR: update or delete on table "marque" violates foreign key constraint "fk_vehicule_marque" on table "vehicule"
Detail: Key (id)=(2) is still referenced from table "vehicule".

L'opération est bloquée car des entrées de vehicule référencent actuellement la marque Toyota. Le comportement à la suppression est par défaut NO ACTION, qui est pratiquement identique à RESTRICT. On peut également définir une valeur avec SET NULL et SET DEFAULT, ou encore reproduire l'opération sur les entrées "enfant" avec CASCADE: en supprimant une marque, on peut supprimmer les vehicule associés.

Remplaçons la contrainte de clé étrangère dans vehicule:

ALTER TABLE vehicule
    DROP CONSTRAINT fk_vehicule_marque
    , ADD CONSTRAINT fk_vehicule_marque FOREIGN KEY (marque_id) REFERENCES marque (id) ON DELETE CASCADE;

On peut réessayer de supprimer une marque:

DELETE FROM marque WHERE nom = 'Toyota';

1 row affected in 9 ms