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

Si vous possédez une entreprise, vous devez avoir rencontré la valeur et le besoin de données dans votre entreprise. Avoir les moyens de stocker et de manipuler des bases de données ajoute plus de 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, nous amenant aux bases de données relationnelles, qui ont été adoptées comme une forme de gestion des données depuis les années 1970. Et sur le marché actuel, les bases de données relationnelles sont privilégiées pour leurs capacités de manipulation des données.

Bien qu'il existe de nombreuses bases de données relationnelles disponibles, MySQL a atteint le premier rang, se classant comme le numéro deux mondial, selon Statesman, à compter de janvier 2022.

Dans le serveur SQL, les contraintes sont des règles et des limitations prédéfinies appliquées dans une ou plusieurs colonnes ; ils sont liés 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 termes simples, seules les données qui respectent la règle de contrainte sont insérées avec succès dans la colonne. L'opération d'insertion est terminée si les données ne répondent pas aux critères.

Cet article suppose que vous avez rencontré des bases de données relationnelles, en particulier MySQL, et que vous avez hâte de renforcer vos connaissances dans le domaine. En fin de compte, je partagerai quelques conseils pour interagir avec les contraintes de clé étrangère.

Contraintes de clé primaire - Un récapitulatif

Une table en SQL implique une ou plusieurs colonnes contenant des valeurs clés qui identifient précisément chaque ligne dans les systèmes. La ou les colonnes intitulées clé primaire (PK) de la table ont pour rôle de faire respecter l'intégrité de l'entité de la table. Les contraintes de clé primaire garantissent des données uniques et sont souvent définies sur une colonne d'identité.

Lors de la spécification des contraintes de clé primaire pour votre table, le moteur de base de données impose automatiquement l'unicité des données en générer des index uniques pour chacune des colonnes principales. Les clés primaires offrent un avantage extrême lorsqu'elles sont utilisées dans des requêtes en fournissant 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 ou composée. Et dans ce cas, chaque colonne de clé primaire peut contenir des valeurs en double. Cependant, les valeurs combinées de toutes les colonnes de la clé primaire doivent être uniques.

Un bon exemple est un cas où vous avez une table avec des colonnes `id`, `noms` et `âge`. Lorsque vous définissez sa contrainte de clé primaire sur la combinaison de `id` et `noms`, vous pouvez avoir des instances en double de `id` ou `noms` valeurs. Néanmoins, chaque combinaison doit être unique pour éviter les lignes en double. Ainsi, vous pourriez avoir des enregistrements avec `id = 1` et `nom=Walter`, et `-22 ans "et `id = 1', 'nom=Henri` et `âge = 27`, mais vous ne pouvez pas avoir d'autres enregistrements avec `id = 1` et `nom=Walter` car la combinaison n'est pas unique.

Voici quelques aspects essentiels à connaître :

1. Une table contient 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 clusterisés sur une table ne peut pas dépasser 1 et le nombre d'index non clusterisés sur une table est limité à 999.
4. Lorsque clustered et non-clustered ne sont pas spécifiés pour une contrainte de clé, clustered est pris automatiquement.
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, toutes les colonnes liées dans la contrainte ont leur possibilité de valeur nulle automatiquement définie sur non nulle.
6. Lorsque les clés primaires sont définies sur Common Language Runtime (CLR) type de colonne défini par l'utilisateur, l'implémentation du type doit prendre en charge l'ordre binaire.

Contraintes de clé étrangère - Un aperçu

Une clé étrangère (FK) implique une colonne ou une combinaison de plusieurs utilisées pour créer et lier un lien entre deux tables et gère 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 autre table.

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

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

Ici le SalesPersonID dans la colonne SalesOrderHeader fusionne avec la colonne de clé primaire du SalesPerson tableau. le SalesPerson table la clé étrangère est la SalesPersonID colonne dans le SalesOrderHeader.

Cette relation définit une règle : un SalesPersonID la valeur ne peut pas être dans votre SalesOrderHeader table si elle n'existe pas dans le SalesPerson tableau.

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 appelées références entrantes, de 253 à 10000 XNUMX. Cependant, cette augmentation s'accompagne de certaines restrictions :

1. Les références de clé étrangère dépassant 253 ne sont disponibles que pour les opérations DELETE DML. MERGE et UPDATE ne sont pas pris en charge.
2. Les tables avec des références de clé étrangère à elles-mêmes sont au maximum de 253 références de clé étrangère.
3. 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és étrangères sont limitées à 253.

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

Comme mentionné précédemment, les contraintes de clé étrangère jouent un rôle essentiel dans la sauvegarde de l'intégrité et de la cohérence des données dans la base de données relationnelle. Voici une ventilation des raisons pour lesquelles les contraintes de clé étrangère sont essentielles.

1. Intégrité référentielle – Les contraintes de clé étrangère garantissent que chaque enregistrement de table enfant correspond à un enregistrement de table primaire, garantissant ainsi la cohérence des données entre les deux tables.
2. Empêcher les enregistrements orphelins – Si vous supprimez une table parent, les contraintes de clé étrangère garantissent que votre table enfant associée est également supprimée, empêchant ainsi les instances d'enregistrements orphelins qui pourraient entraîner une incohérence des données.
3. Performance améliorée – 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és étrangères

Les contraintes de clé étrangère ne créent pas automatiquement les index correspondants comme le primaire. Vous pouvez créer manuellement des index pour les contraintes de clé étrangère ; il est avantageux 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 en faisant correspondre les colonnes liées à la contrainte. Les index aident la base de données à trouver des données associées dans une table étrangère.
• Si vous modifiez les contraintes de clé primaire, elles sont vérifiées avec les contraintes étrangères dans les tables associé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é primaire et étrangère. Cependant, l'ajout de contraintes de clé étrangère 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 étrangères liées.

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

Vous avez déjà passé beaucoup de temps sur la spéculation ; répondu le pourquoi. Déplaçons notre attention et réduisons-la aux tactiques de création de contraintes de clé étrangère ; répondre au comment.

Un champ `Foreign Key` dans une table fait référence à la `Primary Key` d'un autre. La table avec la clé primaire est votre table parent. Et la table avec la clé étrangère s'appelle la table enfant. Plongeons dedans.

Creating a Foreign Key While Creating a Table

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

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 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 référence le 'customer_id' dans votre table 'customers'.

Creating a Foreign Key After Creating a Table

Supposons que vous ayez déjà créé une table et que vous souhaitiez ajouter une contrainte de clé étrangère ; utilisez le `ALTER TABLE` 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 colonne 'customer_id' de contrainte de clé étrangère dans la table 'orders' pour référencer la colonne 'customer_id' dans la table 'customers'.

Creating a Foreign Key Without Checking for Existing Data

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

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

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

Creating a Foreign Key via DELETE/UPDATE

Lorsque vous créez des contraintes de clé étrangère, vous pouvez diriger l'action à entreprendre dans les cas où 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. Ils comprennent:

# 1. PAS D'ACTION

Comme avec de nombreuses autres bases de données, la règle "NO 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 est violée. Cependant, cela n'est pas recommandé car cela peut entraîner des problèmes d'intégrité référentielle car la contrainte de clé étrangère doit être appliquée. Voici un exemple de la façon de procéder :

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. Une fois mis en place, cela 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 puissante 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;

Vous devez être attentif lorsque vous utilisez cette règle, car elle peut entraîner des conséquences indésirables si elle n'est pas utilisée avec précaution. Vous voudriez é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.

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

• Vous ne pouvez pas spécifier CASCADE si une colonne d'horodatage fait partie de la clé étrangère ou référencée.
• Si votre table a 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. FIXER NULL

Lorsque vous supprimez ou mettez à jour une ligne correspondante 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 pour s'exécuter et ne peut pas être spécifiée pour les tables ayant des déclencheurs INSTEAD OF UPDATE. Voici un exemple de la façon de le faire.

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" sur null si la ligne correspondante dans la table "customers" est supprimée ou mise à jour.

# 4. DÉFINIR PAR DEFAUT

Ici, vous définissez toutes les valeurs qui font de la clé étrangère la valeur par défaut à condition que la ligne référencée dans la table parent soit mise à jour ou supprimée.

Cette contrainte s'exécute si toutes les colonnes de 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. 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 le « customer_id » dans la table « commandes » à sa valeur par défaut, ce qui se produit lorsque la ligne correspondante dans la table « clients » est supprimée ou mise à jour.

Mot de la fin

Dans ce guide, vous avez eu un rappel sur les contraintes de clé primaire et approfondi les contraintes de clé étrangère. Vous avez également rencontré plusieurs techniques pour créer des 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évoilé les méthodes.

Et 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.

Ensuite, consultez le Aide-mémoire SQL.