Comment créer des contraintes de clé étrangère dans SQL

Pour toute entreprise, la pertinence et la nécessité des données sont indéniables. La capacité de gérer et de stocker des bases de données représente une valeur ajoutée considérable.

Les bases de données s'appuient sur une structure conventionnelle, permettant d'organiser les données par connexions. Cela nous amène aux bases de données relationnelles, un système de gestion de données privilégié depuis les années 1970. Aujourd'hui, leur aptitude à manipuler les informations les place au premier plan.

Bien qu'une multitude de bases de données relationnelles soient disponibles, MySQL se distingue, occupant la seconde position au niveau mondial selon Statista (janvier 2022).

Dans un serveur SQL, les contraintes sont des règles et des restrictions appliquées à une ou plusieurs colonnes. Elles sont liées aux valeurs des colonnes et contribuent à garantir l'intégrité, la précision et la fiabilité des données.

En termes simples, seules les données qui respectent ces contraintes peuvent être intégrées à une colonne. L'opération d'insertion échoue si les critères ne sont pas respectés.

Cet article suppose une familiarité avec les bases de données relationnelles, en particulier MySQL, et vise à approfondir ces connaissances. Enfin, nous explorerons quelques astuces pour interagir avec les contraintes de clé étrangère.

Révision des contraintes de clé primaire

Dans SQL, une table est composée d'une ou plusieurs colonnes dont les valeurs clés permettent d'identifier précisément chaque ligne. La colonne ou les colonnes désignées comme clé primaire (PK) ont pour fonction de garantir l'intégrité des entités de la table. Les contraintes de clé primaire assurent l'unicité des données et sont souvent associées à une colonne d'identité.

Lors de la définition des contraintes de clé primaire, le moteur de base de données impose automatiquement l'unicité des données en générant des index spécifiques pour chaque colonne concernée. L'utilisation de clés primaires dans les requêtes est un avantage majeur, car elle permet un accès rapide aux informations.

Si les contraintes de clé primaire sont définies sur plusieurs colonnes, on parle de clé primaire composite ou composée. Dans ce cas, chaque colonne peut contenir des valeurs en double, mais la combinaison de toutes les colonnes de la clé primaire doit être unique.

Prenons l'exemple d'une table avec les colonnes `id`, `names` et `age`. Si vous définissez la contrainte de clé primaire sur la combinaison de `id` et `names`, des valeurs `id` ou `names` en double sont possibles. Toutefois, chaque combinaison doit être unique pour éviter les doublons de lignes. Vous pourriez avoir des enregistrements avec `id=1`, `name=Walter` et `age=22`, ainsi que `id=1`, `name=Henry` et `age=27`, mais vous ne pourriez pas avoir un autre enregistrement avec `id=1` et `name=Walter`, car cette combinaison n'est pas unique.

Voici quelques points importants à retenir :

• Une table ne peut avoir qu'une seule contrainte de clé primaire.
• Les clés primaires ne peuvent pas excéder 16 colonnes ni une longueur maximale de 900 caractères.
• Les index générés par les clés primaires peuvent augmenter ceux de la table. Cependant, une table ne peut pas avoir plus d'un index clusterisé et le nombre d'index non clusterisés est limité à 999.
• Si les types clustered ou non-clustered ne sont pas précisés, le clustered est choisi par défaut.
• 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 possibilité de valeur nulle est automatiquement désactivée pour les colonnes concernées.
• Lorsque les clés primaires sont définies sur le type de colonne défini par l'utilisateur du Common Language Runtime (CLR), l'implémentation du type doit supporter l'ordre binaire.

Examen des contraintes de clé étrangère

Une clé étrangère (FK) est une colonne ou une combinaison de colonnes servant à établir et maintenir une liaison entre deux tables, gérant ainsi les données à stocker dans une table à clé étrangère.

Une référence de clé étrangère consiste à lier deux tables en référençant la clé primaire d'une table dans une ou plusieurs colonnes d'une autre table.

Dans le cas d'une référence de clé étrangère, une connexion est créée entre deux tables lorsqu'une ou plusieurs colonnes contenant les clés primaires d'une table sont référencées par des colonnes d'une autre table.

Par exemple, vous pourriez avoir une table, `Sales.SalesOrderHeader`, avec une clé étrangère liée à la table `Sales.Person`, car il existe un lien logique entre les vendeurs et les commandes clients.

Dans ce cas, `SalesPersonID` dans la colonne `SalesOrderHeader` correspond à la colonne de clé primaire de la table `SalesPerson`. La clé étrangère de la table `SalesPerson` est la colonne `SalesPersonID` dans `SalesOrderHeader`.

Cette relation établit une règle : une valeur `SalesPersonID` ne peut exister dans la table `SalesOrderHeader` que si elle est présente dans la table `SalesPerson`.

Une table peut référencer jusqu'à 253 autres colonnes et tables comme clés étrangères, également appelées références sortantes. Depuis 2016, le serveur SQL a porté de 253 à 10 000 le nombre de tables et de colonnes que l'on peut référencer dans une seule table, également appelées références entrantes. Toutefois, cette augmentation s'accompagne de certaines restrictions :

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

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

Comme nous l'avons vu, les contraintes de clé étrangère jouent un rôle primordial dans la protection de l'intégrité et de la cohérence des données au sein d'une base de données relationnelle. Voici une liste des raisons pour lesquelles elles sont essentielles :

• Intégrité référentielle : Les contraintes de clé étrangère assurent que chaque enregistrement d'une table enfant correspond à un enregistrement de la table parent, garantissant ainsi la cohérence des données entre les deux tables.
• 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, évitant ainsi les enregistrements orphelins qui pourraient entraîner une incohérence des données.
• 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 se basant sur les relations entre les tables.

Index des contraintes de clés étrangères

Contrairement aux contraintes de clé primaire, les contraintes de clé étrangère ne génèrent pas automatiquement d'index. Il est possible de créer manuellement des index pour les contraintes de clé étrangère, ce qui est bénéfique pour les raisons suivantes :

• Les colonnes de clé étrangère sont souvent utilisées dans les critères de jointure lors de la combinaison de données de tables liées dans des requêtes. Les index aident la base de données à trouver rapidement les données associées dans une table étrangère.
• En cas de modification des contraintes de clé primaire, elles sont vérifiées par rapport aux contraintes étrangères dans les tables associées.

La création d'index n'est pas obligatoire. Il est possible de combiner les données de deux tables sans spécifier de contraintes de clé primaire ni étrangère. Toutefois, l'ajout de contraintes de clé étrangère optimise les tables et leur combinaison dans une requête respectant les critères d'utilisation des clés. De plus, en cas de modification des contraintes de clé primaire, elles sont vérifiées par rapport aux contraintes étrangères correspondantes.

Conseils pour la création de contraintes de clé étrangère dans SQL

Après avoir exploré la théorie, il est temps de se pencher sur la pratique et les techniques de création de contraintes de clé étrangère ; c'est-à-dire, le « comment ».

Un champ `Foreign Key` dans une table fait référence à la `Primary Key` d'une autre table. La table contenant la clé primaire est la table parent, tandis que la table contenant la clé étrangère est appelée table enfant.

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

Lors de la création d'une table, il est également possible de créer une contrainte de clé étrangère pour maintenir l'intégrité référentielle. Voici la méthode :

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

Le code ci-dessus crée une table appelée `orders` avec une clé primaire entière `order_id`, un autre entier `customer_id` et la date `order_date`. Dans cet exemple, la contrainte `FOREIGN KEY` est ajoutée à la colonne `customer_id` et référence `customer_id` dans la table `customers`.

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

Si vous avez déjà créé une table et que vous souhaitez ajouter une contrainte de clé étrangère, utilisez l'instruction `ALTER TABLE`. Voici un exemple :

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

Dans ce cas, une colonne `customer_id` avec une contrainte de clé étrangère a été ajoutée à la table `orders`, faisant référence à la colonne `customer_id` dans la table `customers`.

Création d'une clé étrangère sans vérifier les données existantes

Lors de l'ajout d'une contrainte de clé étrangère, la base de données vérifie automatiquement si les données existantes sont cohérentes avec la contrainte. Si vous êtes certain de la cohérence des données et souhaitez ajouter une contrainte sans vérification, voici la démarche :

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. Cette méthode est particulièrement utile pour les grandes quantités de données, où le processus de validation pourrait prendre beaucoup de temps.

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

Lors de la création de contraintes de clé étrangère, vous pouvez spécifier l'action à entreprendre si la ligne référencée est mise à jour ou supprimée. C'est là qu'interviennent les contraintes d'intégrité référentielle en cascade, qui déterminent les actions à réaliser. Elles incluent :

1. NO ACTION

Par défaut, la règle `NO ACTION` est appliquée lors de la création d'une contrainte de clé étrangère. Cela signifie qu'aucune action n'est effectuée 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 en cas de violation de la contrainte de clé étrangère. Cependant, cette méthode est déconseillée, car elle peut entraîner des problèmes d'intégrité référentielle, la contrainte de clé étrangère devant être appliquée. Voici un exemple d'implémentation :

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

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é étrangère. Elle indique que chaque mise à jour ou suppression d'une ligne dans la table parent entraîne la mise à jour ou la suppression correspondante des lignes référencées dans la table enfant. Cette technique est efficace 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
ON UPDATE CASCADE;

L'utilisation de la règle `CASCADE` requiert une grande vigilance, car elle peut entraîner des conséquences indésirables si elle est mal gérée. Par exemple, des suppressions accidentelles de données ou la création de références circulaires. Il est donc conseillé d'utiliser cette option avec précaution et uniquement en cas de besoin.

Il existe certaines règles concernant l'utilisation de `CASCADE` :

• L'option `CASCADE` ne peut pas être spécifiée si une colonne d'horodatage fait partie de la clé étrangère ou est référencée.
• Si votre table comporte un déclencheur `INSTEAD OF DELETE`, vous ne pouvez pas spécifier `ON DELETE CASCADE`.
• Si votre table comporte un déclencheur `INSTEAD OF UPDATE`, vous ne pouvez pas spécifier `ON UPDATE CASCADE`.

3. SET NULL

Lorsqu'une ligne correspondante est supprimée ou mise à jour dans la table parent, toutes les valeurs composant la clé étrangère sont définies sur null. Cette règle de contrainte exige que les colonnes de clé étrangère soient nullables et ne peut ê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 NULL
ON UPDATE SET NULL

Dans cet exemple, si la ligne correspondante dans la table `customers` est supprimée ou mise à jour, la colonne de clé étrangère `customer_id` de la table `orders` est définie sur null.

4. SET DEFAULT

Ici, toutes les valeurs qui constituent la clé étrangère sont définies à leur valeur par défaut si la ligne référencée dans la table parent est mise à jour ou supprimée.

Cette contrainte s'applique uniquement si toutes les colonnes de la clé étrangère ont des définitions par défaut. Si une colonne est nullable, sa valeur par défaut est définie sur NULL. Il est important de noter 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 l'exemple ci-dessus, la valeur de `customer_id` dans la table `orders` est définie à sa valeur par défaut lorsque la ligne correspondante dans la table `customers` est supprimée ou mise à jour.

Conclusion

Ce guide a permis de revoir les contraintes de clé primaire et d'approfondir la connaissance des contraintes de clé étrangère. Nous avons exploré diverses techniques de création de ces dernières. Bien qu'il existe de nombreuses méthodes de création, cet article en a présenté les plus importantes.

N'oubliez pas que vous pouvez combiner ces différentes techniques. Par exemple, les méthodes de contrainte `CASCADE`, `SET NULL`, `SET DEFAULT` et `NO ACTION` peuvent être utilisées conjointement sur des tables liées par des relations référentielles.

Si votre table rencontre `NO ACTION`, les autres règles de contrainte sont prises en compte. Dans d'autres cas, une action `DELETE` peut déclencher une combinaison de ces règles, la règle `NO ACTION` étant exécutée en dernier.

Pour aller plus loin, n'hésitez pas à consulter une feuille de triche SQL.