CONTRAINTE : intégrité de la base de données

Contrôle de l'intégrité de la base de données

Une autre chose importante à savoir sur les bases de données est les CONTRAINTES. À l'aide de contraintes, vous pouvez contrôler les modifications de données dans vos tables et maintenir leur intégrité et leur cohérence.

Qu'est-ce que la cohérence des données lorsque l'on parle de base de données ?

Prenons notre boutique en ligne avec des tableaux d'employés, de produits et de tâches . Nous savons déjà qu'il peut y avoir des tâches dans la table des tâches qui ne sont assignées à personne : l'employee_id de ces lignes est NULL.

Mais que se passe-t-il s'il y a une entrée dans la table des tâches avec un employee_id égal à, disons, 115 ? Après tout, nous n'avons pas un tel employé. Nous n'avons pas d'employé avec id = 115 dans la table des employés. En même temps, un lien vers un employé avec cet identifiant se trouve dans la table des tâches. Il s'agit d'un exemple d' incohérence des données .

Alors comment réconcilier ces données ? Idéalement, ce serait pour que le serveur SQL, à chaque changement de données, contrôle toutes ces nuances. Et il y a une telle opportunité, elle s'appelle FOREIGN_KEY.

Si une colonne de votre table contient non seulement des nombres, mais des lignes d'ID d'une autre table, cela peut être spécifié explicitement.

Ajout d'une CLÉ ÉTRANGÈRE

Une telle clé peut être ajoutée à la table à la fois au stade de sa création et après, en utilisant ALTER TABLE. Le format n'est pas fondamentalement différent. Nous présenterons les deux options.

La forme générale d'une telle clé/règle est :

FOREIGN KEY (column)
  	REFERENCES table(column)

Ajoutons cette clé/règle à la table des tâches pour nous assurer que tous les employee_ids de la table font référence à une entrée existante dans la table des employés. Ce script ressemblera à ceci :

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Et si nous décidions d'ajouter cette règle au moment de créer la table des tâches, alors le code ressemblerait à ceci :

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Soit dit en passant, il existe des situations où la chaîne à laquelle nous nous référons a une clé composite unique : par exemple, "Nom et année de naissance" ou "productCatogoryId et productId". Alors FOREIGN KEY peut être écrit comme ceci :

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

FOREIGN KEY et modification des données

Imaginez maintenant une situation où nous avons décidé de mettre à jour certaines données dans la table des employés et où notre identifiant d'employé a changé. Qu'adviendra-t-il des données de la table des tâches ? C'est vrai, ils deviendront inutiles et l'intégrité de notre base de données sera violée.

Pour éviter que cela ne se produise, vous pouvez demander à SQL Server de modifier l'id_employé de toutes les lignes de toutes les tables faisant référence à cet identifiant modifié particulier lorsque l'identifiant de la table des employés change.

Ces scripts sont appelés OnUpdate et OnDelete . Que faire si l'identifiant de l'enregistrement change et que faire si l'enregistrement est supprimé ?

Avec la suppression, tout n'est pas si simple. Si vous avez des objets dépendants représentés par des chaînes dans la base de données qui se réfèrent les uns aux autres, une grande variété de scénarios de comportement sont possibles lors de la suppression d'un objet.

Disons que nous supprimons un utilisateur du site, ce qui signifie que nous devons supprimer toute sa correspondance personnelle. Mais il est peu probable que nous supprimions tous ses commentaires publics.

Ou un employé démissionne. Ce serait étrange s'il démissionnait et qu'en même temps toutes les tâches qui lui étaient assignées disparaissaient de la base de données. Mais s'ils étaient restés nommés non par lui, cela aurait aussi mal tourné. Il est plus correct de faire en sorte que l'employé puisse démissionner après avoir réaffecté toutes ses tâches à d'autres personnes.

Voici comment nous pouvons décrire ces scénarios en utilisant FOREIGN KEY. La forme générale d'une telle clé/règle est :

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Que faire en cas de suppression (ON DELETE) ou de modification (ON UPDATE) d'enregistrements ? Au total, il peut y avoir 5 options pour que le serveur SQL agisse dans chacune de ces situations :

Voici comment nous pourrions modifier notre tableau des tâches :

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Ce qui est écrit ici :

ON UPDATE CASCADE : Si la clé id dans la table des employés change, alors changez également l'employee_id dans la table des tâches qui y fait référence.

ON DELETE RESTRICT : Si une ligne est en cours de suppression de la table des employés et est référencée à partir de la table des tâches, empêcher la suppression de la ligne de la table des employés.