Si vous êtes propriétaire d’une entreprise, vous avez certainement constaté la valeur et la nécessité des données dans votre activité. Le fait d’avoir les moyens de stocker et de manipuler des bases de données ajoute de la valeur à l’entreprise.

Les bases de données sont organisées selon une convention particulière et vous permettent de structurer les données en connexions, ce qui nous amène aux bases de données relationnelles, qui ont été adoptées comme forme de gestion des données depuis les années 1970. Sur le marché actuel, les bases de données relationnelles sont préférées pour leurs capacités de manipulation des données.

Bien qu’il existe de nombreuses bases de données relationnelles, MySQL s’est hissé au premier rang, se classant au deuxième rang mondial, selon Statista, en janvier 2022.

Dans le serveur SQL, les contraintes sont des règles prédéfinies et des limitations appliquées à une ou plusieurs colonnes ; elles sont liées aux valeurs de la colonne et aident à maintenir l’intégrité, la précision et la fiabilité des données des colonnes spécifiées.

En d’autres termes, seules les données qui répondent à la règle de contrainte sont insérées avec succès dans la colonne. L’opération d’insertion est interrompue si les données ne répondent pas aux critères.

Cet article suppose que vous avez déjà rencontré des bases de données relationnelles, en particulier MySQL, et que vous souhaitez renforcer vos connaissances dans ce domaine. Enfin, je vous donnerai quelques conseils pour interagir avec les contraintes de clé étrangère.

Contraintes de clé primaire – Récapitulatif

YouTube video

En SQL, une table comporte une ou plusieurs colonnes contenant des valeurs clés qui identifient précisément chaque ligne du système. La ou les colonnes intitulées clé primaire (PK) de la table ont pour rôle d’assurer l’intégrité de l’entité de la table. Les contraintes de clé primaire garantissent l’unicité des données et sont souvent définies sur une colonne d’identité.

Lorsque vous spécifiez les contraintes de clé primaire pour votre table, le moteur de base de données impose automatiquement l’unicité des données en générant des index uniques pour chacune des colonnes primaires. Les clés primaires offrent un avantage extrême lorsqu’elles sont utilisées dans des requêtes, car elles permettent un accès rapide aux données.

Si des contraintes de clé primaire sont définies sur plusieurs colonnes, on parle de clé primaire composite. Dans ce cas, chaque colonne de clé primaire peut contenir des valeurs en double. Toutefois, les valeurs combinées de toutes les colonnes de la clé primaire doivent être uniques.

Un bon exemple est celui d’une table comportant les colonnes `id`, `names` et `age`. Lorsque vous définissez sa contrainte de clé primaire sur la combinaison de `id` et `names`, vous pouvez avoir des instances dupliquées des valeurs `id` ou `names`. Cependant, chaque combinaison doit être unique pour éviter les lignes dupliquées. Ainsi, vous pouvez avoir des enregistrements avec `id=1` et `name=Walter`, et `age-22“et `id=1`, `name=Henry` et `age=27`, mais vous ne pouvez pas avoir d’autres enregistrements avec `id=1` et `name=Walter` parce que la combinaison n’est pas unique.

Voici quelques aspects essentiels à connaître :

  1. Une table ne contient qu’une seule contrainte de clé primaire.
  2. Les clés primaires ne peuvent pas dépasser 16 colonnes et une longueur maximale de 900 caractères.
  3. Les index générés par les clés primaires peuvent augmenter ceux de la table. Cependant, le nombre d’index en grappe sur une table ne peut pas dépasser 1, et le nombre d’index non en grappe sur une table est limité à 999.
  4. Lorsque les termes clustered et non-clustered ne sont pas spécifiés pour une contrainte de clé, le terme clustered est automatiquement retenu.
  5. Toutes les colonnes déclarées dans une contrainte de clé primaire doivent être définies comme non nulles. Si ce n’est pas le cas, la nullité de toutes les colonnes liées dans la contrainte est définie de manière robotique comme étant non nulle.
  6. Lorsque les clés primaires sont définies sur un type de colonne défini par l’utilisateur dans le Common Language Runtime (CLR), l’implémentation du type doit prendre en charge l’ordre binaire.

Contraintes de clé étrangère – Récapitulatif

Une clé étrangère (FK) est une colonne ou une combinaison de plusieurs colonnes utilisée pour créer et lier un lien entre deux tables et gérer les données à stocker dans une table de clé étrangère.

Une référence de clé étrangère implique la création d’un lien entre deux tables, lorsqu’une ou plusieurs colonnes contenant la clé primaire d’une autre table sont référencées par une ou plusieurs colonnes d’une table différente.

Dans le cas d’une référence à une clé étrangère, une connexion est créée entre deux tables lorsqu’une ou plusieurs colonnes détenant la clé primaire d’une table sont référencées par des colonnes d’une autre table.

Dans un cas d’utilisation pratique, vous pouvez avoir une table, Sales.SalesOrderHeader, avec une clé étrangère reliée à une autre table, Sales. Person, car il existe une relation logique entre les vendeurs et les commandes clients.

Ici, le SalesPersonID de la colonne SalesOrderHeader est associé à la colonne de clé primaire de la table SalesPerson. La clé étrangère de la table Sales Person est la colonne SalesPersonID de la colonne SalesOrderHeader.

Cette relation définit une règle : une valeurSalesPersonID ne peut pas figurer dans votre table SalesOrderHeader si elle est inexistante dans la tableSalesPerson .

Une table peut référencer jusqu’à 253 autres colonnes et tables en tant que clés étrangères, également appelées références sortantes. Depuis 2016, le serveur SQL a augmenté le nombre de tables et de colonnes que vous pouvez référencer dans une seule table, également connu sous le nom de références entrantes, de 253 à 10000. Cette augmentation s’accompagne toutefois de quelques restrictions :

  1. Les références de clés étrangères dépassant 253 ne sont disponibles que pour les opérations DML DELETE. Les opérations MERGE et UPDATE ne sont pas prises en charge.
  2. Les tables ayant des références de clés étrangères vers elles-mêmes ne peuvent pas dépasser 253 références de clés étrangères.
  3. Pour les index en colonnes, les tables à mémoire optimisée et les tables à clés étrangères partitionnées, les références aux clés étrangères sont limitées à 253.

Quels sont les avantages des clés étrangères ?

Comme nous l’avons déjà mentionné, les contraintes de clés étrangères jouent un rôle essentiel dans la sauvegarde de l’intégrité et de la cohérence des données dans les bases de données relationnelles. Voici un aperçu des raisons pour lesquelles les contraintes de clés étrangères sont essentielles.

  1. Intégrité référentielle – Les contraintes de clé étrangère garantissent que chaque enregistrement d’une table enfant correspond à un enregistrement d’une table primaire, ce qui assure la cohérence des données dans les deux tables.
  2. Prévention des enregistrements orphelins – Si vous supprimez une table parent, les contraintes de clé étrangère garantissent que la table enfant associée est également supprimée, ce qui permet d’éviter les enregistrements orphelins susceptibles d’entraîner une incohérence des données.
  3. Amélioration des performances – Les contraintes de clé étrangère améliorent les performances des requêtes en permettant au système de gestion de base de données d’optimiser les requêtes en fonction des relations entre les tables.

Index des contraintes de clé étrangère

Les contraintes de clés étrangères ne créent pas automatiquement les index correspondants comme pour les clés primaires. Vous pouvez créer manuellement des index pour les contraintes de clé étrangère, ce qui est avantageux pour les raisons suivantes.

  • Les colonnes des clés étrangères sont souvent utilisées dans les critères de jointure lors de la combinaison de données provenant de tables liées dans les requêtes en faisant correspondre les colonnes liées à la contrainte. Les index aident la base de données à trouver les données associées dans une table étrangère.
  • Si vous modifiez les contraintes de la clé primaire, elles sont vérifiées avec les contraintes étrangères dans les tables liées.

Il n’est pas obligatoire de créer des index. Vous pouvez toujours combiner les données de deux tables sans spécifier les contraintes de clés primaires et étrangères. Toutefois, l’ajout de contraintes de clés étrangères optimise les tables et les combine dans une requête qui répond à ses critères d’utilisation des clés. Si vous modifiez les contraintes de clé primaire, elles sont vérifiées avec les contraintes de clé étrangère en relation.

Conseils pour créer des contraintes de clé étrangère en SQL

Vous avez déjà consacré beaucoup de temps à la spéculation et répondu à la question du pourquoi. Concentrons-nous maintenant sur les tactiques de création des contraintes de clé étrangère, et répondons au comment.

Un champ “clé étrangère” dans une table fait référence à la “clé primaire” d’une autre table. La table avec la clé primaire est votre table mère. Et la table avec la clé étrangère est appelée la table enfant. Voyons cela de plus près.

Création d’une clé étrangère lors de la création d’une table

Lors de la création d’une table, vous pouvez également créer une contrainte de clé étrangère pour garantir l’intégrité référentielle. Voici comment procéder :

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    date_de_la_commande DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ;

Le code ci-dessus crée une table appelée “orders” avec la clé primaire entière “order_id”, un autre entier “customer_id” et la date “order_date”. Dans ce cas, la contrainte FOREIGN KEY est ajoutée à la colonne “customer_id” et fait référence au “customer_id” de votre table “customers”.

Création d’une clé étrangère après la création d’une table

Supposons que vous ayez déjà créé une table et que vous souhaitiez ajouter une contrainte de clé étrangère ; utilisez l’instruction `ALTERTABLE` dans votre code. Regardez l’extrait de code ci-dessous.

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ;

Dans ce cas, vous avez ajouté une contrainte de clé étrangère à la colonne “customer_id” de la table “orders” pour référencer la colonne “customer_id” de la table “customers”.

Création d’une clé étrangère sans vérification des données existantes

Lorsque vous ajoutez une contrainte de clé étrangère à une table, la base de données vérifie automatiquement les données existantes pour s’assurer de leur cohérence avec la contrainte. Toutefois, si vous savez que les données sont cohérentes et que vous souhaitez ajouter une contrainte sans vérifier la cohérence, voici comment procéder.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALIDATE ;

La commande NOT VALIDATE indique à la base de données de ne pas vérifier les données existantes. Ce cas particulier est utile dans certains cas. Par exemple, lorsque vous avez des données massives et que vous souhaitez terminer le processus de validation.

Création d’une clé étrangère via DELETE/UPDATE

Lorsque vous créez des contraintes de clé étrangère, vous pouvez indiquer l’action à entreprendre lorsque la ligne référencée est mise à jour ou supprimée. Dans ce cas, vous utilisez des contraintes d’intégrité référentielle en cascade pour dicter les actions à entreprendre. Ces contraintes sont les suivantes

#1. PAS D’ACTION

Comme dans de nombreuses autres bases de données, la règle “PAS D’ACTION” est le comportement par défaut lorsque vous créez une contrainte de clé étrangère. Cela signifie qu’aucune action n’est entreprise lorsque la ligne référencée est supprimée ou mise à jour.

Le moteur de base de données génère une erreur si la contrainte de clé étrangère n’est pas respectée. Cette méthode n’est toutefois pas recommandée, car elle peut entraîner des problèmes d’intégrité référentielle, étant donné que la contrainte de clé étrangère doit être appliquée. Voici un exemple de la procédure à suivre :

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE NO ACTION
AUCUNE ACTION LORS DE LA MISE À JOUR ;

#2. CASCADE

La règle “CASCADE” est une autre option pour les actions “ON DELETE” et “ON UPDATE” lors de la création de contraintes de clés étrangères. Lorsqu’elle est mise en place, elle signifie que chaque fois qu’une ligne est mise à jour ou supprimée dans les tables parentes, les lignes référencées sont mises à jour ou supprimées en conséquence. Cette technique est très utile pour maintenir l’intégrité référentielle. Voici un exemple :

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
SUR LA CASCADE DE MISE À JOUR ;

Vous devez faire preuve de prudence lorsque vous utilisez cette règle, car elle peut avoir des conséquences indésirables si elle n’est pas utilisée avec précaution. Vous voulez éviter de supprimer accidentellement trop de données ou de créer des références circulaires. Par conséquent, n’utilisez cette option qu’en cas de nécessité et avec prudence.

L’utilisation de CASCADE est soumise à certaines règles :

  • Vous ne pouvez pas spécifier CASCADE si une colonne d’horodatage fait partie de la clé étrangère ou de la clé référencée.
  • Si votre table possède un déclencheur INSTEAD OF DELETE, vous ne pouvez pas spécifier ON DELETED CASCADE.
  • Vous ne pouvez pas spécifier ON UPDATE CASCADE si votre table a un déclencheur INSTEAD OF UPDATE.

#3. SET NULL

Lorsque vous supprimez ou mettez à jour une ligne correspondante dans la table parent, toutes les valeurs composant la clé étrangère sont mises à zéro. Cette règle de contrainte exige que les colonnes de la clé étrangère soient nullables pour être exécutées et ne peut pas être spécifiée pour les tables ayant des déclencheurs INSTEAD OF UPDATE. Voici un exemple de la procédure à suivre.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE SET NULL

Dans ce cas, vous avez défini la colonne de clé étrangère ‘customer_id’ dans la table “orders” pour qu’elle soit nulle si la ligne correspondante dans la table “customers” est supprimée ou mise à jour.

#4. DÉFINIR LA VALEUR PAR DÉFAUT

Ici, vous définissez toutes les valeurs qui rendent la clé étrangère par défaut si la ligne référencée dans la table parent est mise à jour ou supprimée.

Cette contrainte s’exécute si toutes les colonnes de la clé étrangère ont des définitions par défaut. Si une colonne est nulle, sa valeur par défaut est NULL. Notez que cette option ne peut pas être spécifiée pour les tables avec des déclencheurs INSTEAD OF UPDATE. Voici un exemple :

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT ;

Dans le cas ci-dessus, vous avez défini la valeur par défaut de “customer_id” dans la table “orders”, ce qui se produit lorsque la ligne correspondante dans la table “customers” est supprimée ou mise à jour.

Le mot de la fin

Dans ce guide, vous avez fait un rappel sur les contraintes de clé primaire et vous avez approfondi les contraintes de clé étrangère. Vous avez également découvert plusieurs techniques de création de contraintes de clé étrangère. Et bien qu’il existe de nombreuses façons de créer des contraintes de clé étrangère, cet article a démêlé les méthodes.

En espérant que vous avez saisi de nouvelles techniques, vous n’êtes pas limité à les combiner. Par exemple, les méthodes de contrainte CASCADE, SET NULL, SET DEFAULT et NO ACTION peuvent être combinées sur des tables avec des relations référentielles.

Si votre table rencontre NO ACTION, elle revient à d’autres règles de contrainte. Dans d’autres cas, une action DELETE peut déclencher une combinaison de ces règles, et la règle NO ACTION sera exécutée en dernier.

Consultez ensuite l’aide-mémoire SQL.