SQLShack (Français)
dans l’article précédent commonly used SQL Server Constraints: NOT NULL, UNIQUE and PRIMARY KEY, nous avons décrit, en détail, les trois premiers types de contraintes SQL Server; NOT NULL, UNIQUE and PRIMARY KEY. Dans cet article, nous allons discuter des trois autres contraintes; clé étrangère, vérification et défaut en décrivant chacune brièvement et en fournissant des exemples pratiques.,
contrainte de clé étrangère
Une clé étrangère est une clé de base de données utilisée pour lier deux tables ensemble. La contrainte de clé étrangère identifie les relations entre les tables de base de données en référençant une colonne, ou un ensemble de colonnes, dans la table enfant qui contient la clé étrangère, à la colonne de clé primaire ou à l’ensemble de colonnes, dans la table Parent.
la relation entre les tables enfant et parent est maintenue en vérifiant l’existence des valeurs de clé étrangère de la table enfant dans la clé primaire de la table parent référencée avant d’insérer ces valeurs dans la table enfant., De cette façon, la contrainte de clé étrangère, dans la table enfant qui fait référence à la clé primaire dans la table parent, appliquera l’intégrité référentielle de la base de données. L’intégrité référentielle garantit que la relation entre les tables de base de données est préservée pendant le processus d’insertion des données. Rappelez-vous que la contrainte de clé primaire garantit qu’aucune valeur nulle ou dupliquée pour la ou les colonnes sélectionnées ne sera insérée dans cette table, ce qui impose l’intégrité de l’entité pour cette table., L’intégrité d’entité appliquée par la clé primaire et l’intégrité référentielle appliquée par la clé étrangère forment ensemble l’intégrité de clé.
la contrainte de clé étrangère diffère de la contrainte de clé primaire en ce sens que vous ne pouvez créer qu’une seule clé primaire par table, avec la possibilité de créer plusieurs contraintes de clé étrangère dans chaque table en référençant plusieurs tables parentes. Une autre différence est que la clé étrangère permet d’insérer des valeurs NULL s’il n’y a pas de contrainte NOT NULL définie sur cette clé, mais que la clé primaire n’accepte pas les valeurs Null.,
la contrainte de clé étrangère vous permet également de contrôler l’action qui sera effectuée lorsque la valeur référencée dans la table parent est mise à jour ou supprimée, en utilisant les clauses ON UPDATE et ON DELETE. Les actions prises en charge qui peuvent être effectuées lors de la suppression ou de la mise à jour des valeurs de la table parent incluent:
- aucune ACTION: lorsque les clauses ON UPDATE ou ON DELETE sont définies sur aucune ACTION, l’opération de mise à jour ou de suppression effectuée dans la table parent échouera avec une erreur.,
- CASCADE: en définissant les clauses ON UPDATE ou ON DELETE sur CASCADE, la même action effectuée sur les valeurs référencées de la table parent sera répercutée sur les valeurs associées dans la table enfant. Par exemple, si la valeur référencée est supprimée dans la table parent, toutes les lignes associées de la table enfant sont également supprimées.
- SET NULL: avec cette option ON UPDATE et ON DELETE clauses, si les valeurs référencées dans la table parent sont supprimées ou modifiées, toutes les valeurs associées dans la table enfant sont définies sur la valeur NULL.,
- définir par défaut: L’utilisation de L’option Définir par défaut des clauses ON UPDATE et ON DELETE spécifie que, si les valeurs référencées dans la table parent sont mises à jour ou supprimées, les valeurs associées dans la table enfant avec des colonnes de clé étrangère seront définies sur sa valeur par défaut.
Vous pouvez ajouter la contrainte de clé étrangère lors de la définition de la colonne à l’aide de L’instruction CREATE TABLE T-SQL, ou l’ajouter après la création de la table à l’aide de L’instruction ALTER TABLE T-SQL. Nous allons créer deux nouvelles tables pour comprendre la fonctionnalité de contrainte de clé étrangère., La première table agira comme la table parente avec la colonne ID définie comme une colonne de clé primaire. La deuxième table agira comme la table enfant, avec la colonne ID définie comme la colonne de clé étrangère qui fait référence à la colonne ID sur la table parent.,> ID INT PRIMARY KEY,
Après avoir créé les deux tableaux, nous allons insérer trois enregistrements de la table parent, et de deux enregistrements de la table enfant, en suivant les instructions INSERT:
Le résultat va vous montrer que les trois dossiers sont correctement insérés dans la table parent., Le premier enregistrement que nous avons essayé d’insérer dans la table enfant est inséré sans aucune erreur car la valeur ID de 1 existe déjà dans la table parent.,parce que la valeur ID de 4 n’existe pas dans la table parent, et en raison de la contrainte de clé étrangère, vous ne pourrez pas insérer une valeur ID dans la table enfant qui n’existe pas dans la table parent:
en vérifiant le contenu des tables parent et enfant, vous verrez qu’un seul enregistrement est inséré dans la table enfant, comme vous pouvez le voir ci-dessous:
Comme nous n’avons pas mentionné le nom de la contrainte de clé étrangère lors de la création de la table enfant, SQL Server lui attribuera un nom unique que nous pourrons récupérer à partir de information_schema.,TABLE_CONSTRAINTS system view using the following query:
|
1
2
3
4
5
6
7
8
|
SELECT CONSTRAINT_NAME,
TABLE_SCHEMA ,
TABLE_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.,onstraintDemoChild
ADD CONSTRAINT FK__ConstraintDe__ID
FOREIGN KEY (ID) REFERENCES ConstraintDemoParent(ID);
|
l’opération échouera, comme la valeur ID de 4 n’existe pas dans la table parent et rompt l’intégrité référentielle entre les tables enfant et parent, comme le montre le message d’erreur suivant:
pour pouvoir créer la contrainte de clé étrangère dans la table enfant, nous devons d’abord éliminer ce conflit en supprimant ou en mettant à jour cet enregistrement., Here we will try to modify the ID value with the following UPDATE command:
|
1
2
3
|
UPDATE ., SET ID =2 WHERE ID = 4
|
maintenant, la contrainte de clé étrangère sera créée avec succès sans aucune erreur, comme indiqué ci-dessous:
de colonnes pour limiter la plage de valeurs, qui peuvent être insérées dans ces colonnes, en utilisant une condition prédéfinie., La contrainte CHECK entre en action pour évaluer les valeurs insérées ou modifiées, où la valeur qui satisfait à la condition sera insérée dans la table, sinon, l’opération insert sera supprimée. Il est permis de spécifier plusieurs contraintes de vérification pour la même colonne.
définir la condition de contrainte de vérification est en quelque sorte similaire à l’écriture de la clause WHERE d’une requête, en utilisant les différents opérateurs de comparaison, tels que AND, OR, BETWEEN, IN, LIKE and IS NULL pour écrire son expression booléenne qui retournera TRUE, FALSE ou UNKNOWN., La contrainte CHECK renverra une valeur inconnue lorsqu’une valeur NULL est présente dans la condition. La contrainte CHECK est utilisée principalement pour appliquer l’intégrité du domaine en limitant les valeurs insérées à celles qui suivent les règles de valeurs, de plage ou de format définies.
créons une nouvelle table simple qui a trois colonnes; la colonne ID qui est considérée comme la clé primaire de cette table, nom et salaire. Une contrainte de vérification est définie sur la colonne salaire pour s’assurer qu’aucune valeur nulle ou négative n’est insérée dans cette colonne.,v>6
Si vous exécutez les trois instructions d’insertion ci-dessous:
Il ressort clairement du résultat généré que le premier enregistrement est inséré sans erreur car la valeur de salaire fournie répond à la condition de vérification.,comme vous pouvez le voir dans le message d’erreur suivant:
La Vérification des données de la table vous montre que seule la première ligne qui a passé la condition de contrainte de vérification sera insérée dans la table comme ci-dessous:
Nous avons mentionné plus tôt dans cet article que la contrainte CHECK permet d’insérer des valeurs Null si les colonnes participantes autorisent null, et les valeurs NULL seront évaluées comme inconnues sans générer d’erreur., Ceci est clair à partir de l’enregistrement ci-dessous qui est inséré avec succès, bien que la valeur fournie de la colonne Salary soit NULL:
Si vous consultez la définition de la contrainte CHECK dans L’instruction CREATE TABLE précédente, vous verrez que nous n’avons pas mentionné le nom Dans ce cas, SQL Server attribuera un nom unique à cette contrainte qui peut être affiché en interrogeant INFORMATION_SCHEMA.TABLE_CONSTRAINTS vue système pour la table ConstraintDemo4., Le résultat sera comme indiqué ci-dessous:
la contrainte CHECK peut être supprimée à l’aide de L’instruction ALTER TABLE T-SQL.,onstraint now, with the following ALTER DATABASE … ADD CONSTRAINT T-SQL statement:
|
1
2
3
4
5
|
ALTER TABLE ConstraintDemo4
ADD CONSTRAINT CK__Constrain__Salar
CHECK (Salary>0);
|
Adding the CHECK constraint will fail., Cela est dû au fait que, pendant que le serveur SQL vérifie les données déjà existantes pour la condition de contrainte de vérification, une ou plusieurs valeurs qui ne répondent pas à la condition de contrainte sont trouvées, comme vous pouvez le voir dans le message d’erreur suivant:
Pour pouvoir définir à nouveau la contrainte de vérification, nous devons modifier ou supprimer les données qui empêchent la création de la contrainte.,de484761″>
maintenant, la contrainte de vérification peut être définie sans problème comme indiqué ci-dessous:
SQL Server vous permet de désactiver la contrainte de vérification pour des cas particuliers, tels que l’insertion condition de contrainte à des fins de test, ou en raison d’une modification de la logique métier., Ce cas n’est valable que pour les contraintes CHECK et FOREIGN KEY que vous pouvez désactiver temporairement.,écrit le nom de la contrainte, comme dans la commande T-SQL ci-dessous:
|
1
2
3
|
ALTER TABLE ConstraintDemo4 NOCHECK CONTRAINTE
|
a Partir de la définition de la contrainte, vous pouvez spécifier que la contrainte sera créé sans vérifier les lignes existantes, mais la contrainte sera marqué comme non digne de confiance., peut activer toutes les contraintes de vérification, en même temps,en utilisant la commande T-SQL ci-dessous:
|
1
2
3
|
ALTER TABLE constraintdemo4 CHECK constraint all |
lorsque vous activez la contrainte de vérification précédente, vous verrez que SQL Server ne se plaindra pas des données non cochées qui rompent la condition de contrainte., Dans ce cas, l’intégrité du domaine des données n’est pas maintenue.
Pour résoudre ce problème, la commande DBCC CHECKCONSTRAINTS peut être facilement utilisée pour identifier les données qui violent la condition de contrainte dans une table ou une contrainte spécifiée, en tenant compte de ne pas exécuter cette commande aux heures de pointe, car cela affectera les performances du serveur SQL en raison de l’absence d’un instantané de base de données.,v id= »ff419aa09d »>
le résultat vous montrera les deux valeurs de salaire qui rompent la condition de contrainte de vérification, comme indiqué ci-dessous:
une contrainte de vérification peut également être créée à l’aide de l’outil SQL Server Management Studio, en cliquant avec le bouton droit sur la table requise et en sélectionnant l’option design., Dans la fenêtre de conception affichée, faites un clic droit et choisissez L’option Vérifier les contraintes comme suit:
dans la fenêtre vérifier les contraintes, vous pouvez spécifier le nom de la contrainte de vérification, l’expression et si la vérification des données existantes est requise, comme indiqué ci-dessous:
contrainte par défaut
une contrainte par défaut est utilisée pour fournir une valeur de colonne par défaut pour les lignes insérées si aucune valeur n’est spécifiée pour cette colonne dans l’instruction INSERT., La contrainte par défaut aide à maintenir l’intégrité du domaine en fournissant des valeurs appropriées pour la colonne, au cas où l’utilisateur ne fournirait pas de valeur pour celle-ci. La valeur par défaut peut être une valeur constante, une valeur de fonction système ou NULL.,elow:
|
1
2
3
4
|
ALTER TABLE constraintdemo5
add constraint DF__constrain__emplo default (GETDATE()) for employeedate
|
de plus, la contrainte par défaut peut être définie à l’aide de SQL Server Management Studio, en cliquant avec le bouton droit sur la table requise et en choisissant l’option design., Sélectionnez ensuite la colonne à laquelle vous affecterez une valeur par défaut en parcourant la fenêtre des propriétés de colonne comme indiqué ci-dessous:
j’espérais que cet article, et le précédent, ont aidé à expliquer les six types de contraintes SQL Server. N’hésitez pas à poser vos questions dans les commentaires ci-dessous.,NLY utilisé SQL Server contraintes: NOT NULL, UNIQUE et PRIMARY KEY
Liens utiles
- créer des contraintes de vérification
- contraintes
- par défaut
- créer des relations de clé étrangère
- vérifier les contraintes
- auteur
- articles récents
Il est expert en solutions Microsoft Certified en gestion et analyse de données, Microsoft Certified Solution Associate en administration et développement de bases de données SQL, Azure Developer Associate et Microsoft Certified Trainer.
En outre, il contribue avec ses conseils SQL dans de nombreux blogs.,
Afficher tous les articles par Ahmad Yaseen
- Comment faire pour surveiller Azure Data Factory – 15 janvier 2021
- Utilisation de la Source de Contrôle dans Azure Data Factory – 12 janvier 2021
- à l’Aide de Modèles dans Azure Data Factory – 8 janvier 2021