webdevpro.net

SQL Intégrité des données via des clés étrangères

SQL Intégrité des données via des clés étrangères | webdevpro.net

Relation entre deux tables à réaliser

Nous souhaitons créer deux tables, adresse et user, qui sont liés l’une à l’autre :
Dans la table user, la colonne ID_Adresse doit contenir uniquement des clé primaires présentent dans la table adresse

Le schéma suivant présente la relation à créer :

relation-entre-table-user-table-adresse

Attention, il ne faut pas confondre intégrité des tables avec le concept de jointure sql

Création de la table adresse – la table père

Tout d’abord, il faut créer la table père : la table qui va nourrir l’autre table (ça éviter de revenir dessus par la suite) :

structure-table-adresse

Remarques :

  • pour réaliser la relation entre cette table et la table user, il faut que la colonne ID soit indexée
  • De plus cette colonne sert d’identifiant unique pour cette table, il faut donc qu’elle soit enregistrée comme clé primaire de la table
-- Table structure for table `adresse`
CREATE TABLE IF NOT EXISTS `adresse` (
  `id_address` int(10) unsigned NOT NULL,
  `num` int(11) NOT NULL,
  `ville` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Indexes for table `adresse`
ALTER TABLE `adresse`
  ADD PRIMARY KEY (`id_address`);

-- AUTO_INCREMENT for table `adresse`
ALTER TABLE `adresse`
  MODIFY `id_address` int(10) unsigned NOT NULL AUTO_INCREMENT;

Création de la table user – la table enfant

Puis, il faut créer la table enfant (qui récupère une information d’une autre information) :

structure-table-user

Remarques :

  • Lors de la création de la table, il faut indexé la colonne id_address
  • Etant donné que deux utilisateurs peuvent avoir la même adresse, il faut choisir INDEX (ou KEY)
  • Si l’on ne veut pas ce cas de figure, il aurait fallu choisir UNIQUE
-- Table structure for table `user`
CREATE TABLE IF NOT EXISTS `user` (
  `id_user` int(10) unsigned NOT NULL,
  `nom` varchar(255) NOT NULL,
  `id_address` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Indexes for table `user`
ALTER TABLE `user`
  ADD PRIMARY KEY (`id_user`),
  ADD KEY `id_address` (`id_address`);

Comparaison entre INDEX, UNIQUE et PRIMARY KEY

Accepte la valeur KEY ou INDEX UNIQUE PRIMARY KEY
NULL OUI +rs Oui 1 seul Aucun
DOUBLON OUI +rs Aucun Aucun
ROLE relation 1-n relation 1-1 identifie une ligne
dans une table

Ajout d’une contrainte

Les deux tables sont créées et les colonnes à relier sont indexées correctement.
Nous allons maintenant mettre en place une contrainte dans la table enfant :

ALTER TABLE `user`
  ADD CONSTRAINT `toto` FOREIGN KEY (`id_address`) REFERENCES `adresse` (`id_address`) ON DELETE CASCADE ON UPDATE CASCADE;

Remarques :

  • Pour que la contrainte soit réalisée, il faut que les colonnes liées aient le même type
  • J’ai donnée le nom toto à la contrainte (ce nom est complètement arbitaire). Il permet de récupérer plus facilement en cas de modification ou de suppression et n’a aucun impacte sur la relation
  • la contrainte est de type ON DELETE CASCADE ON UPDATE CASCADE

Les différents types de contrainte CASCADE / SET NULL / RESTRICT

type de contrainte En cas de suppression de la ligne dans la table père En cas mis à jour de l’ID dans la table père
CASCADE la table enfant est impactée : la ou les lignes sont supprimées la table enfant est impactée : l’ID dans la ou les lignes sont modifiées
SET NULL la table enfant est impactée : l’ID dans la ou les lignes sont modifiées à NULL la table enfant est impactée : l’ID dans la ou les lignes sont modifiées à NULL
NO ACTION / RESTRICT l’opération est refusée l’opération est refusée

Insérer un nouvel utilisateur

Désormais nos deux tables sont liées :

Remarques :

  • il n’est plus possible d’ajouter de valeur autre que celles présentent dans la colonne ID de adresse
  • si je veux ajouter un nouvel utilisateur avec une nouvelle adresse, je dois au préalable créer l’adresse puis créer l’utilisateur (ou créer un utilisateur avec un ID adresse null, puis créer l’adresse, puis mettre à jour de l’utilisateur ….)
  • En cas de suppression de l’utilisateur, l’adresse associée n’est pas supprimée
  • En cas de suppression de l’adresse, le ou les utilisateurs associés seront supprimés grâce (ou à cause) de CASCADE

Ailleurs sur le web : le concept d’INDEX et la vitesse de recherche

La mise en place d’INDEX + une CONSTRAINT sur la colonne ID_adresse de la table enfant permet de garantir l’intégrité des données insérer dans cette colonne, mais les INDEXE sont aussi utilisés dans un autre cas, l’optimisation de la recherche dans les base de données :