Contraintes d'intégrité en SQL

Contraintes d'intégrité de domaine en SQL

Les contraintes d'intégrité de domaine définissent les valeurs autorisées pour une colonne donnée. En appliquant ces contraintes, tu peux restreindre les données saisies dans une colonne spécifique, ce qui garantit des valeurs de données cohérentes dans toute ta base de données.

Voici quelques contraintes d'intégrité de domaine couramment utilisées :

• Type de données - La colonne doit contenir des valeurs d'un type de données spécifique.
• Format des données - Le format des valeurs d'une colonne doit suivre un modèle défini.
• Plage - Les valeurs doivent être comprises dans une plage spécifiée.
• Énumération - Les valeurs de la colonne ne peuvent être prises que dans un ensemble prédéfini de valeurs.

Contraintes d'intégrité d'entité en SQL

Les contraintes d'intégrité d'entité impliquent l'identification unique des lignes d'une table de base de données, de sorte qu'il n'y ait pas de valeurs dupliquées ou nulles dans une colonne de clé primaire. Une clé primaire est une colonne unique dans une table qui identifie de façon unique chaque ligne de la table. Cette contrainte permet de maintenir l'unicité et l'intégrité des données en empêchant l'existence de lignes en double.

Contrainte d'intégrité référentielle en SQL

La contrainte d'intégrité référentielle garantit que les relations entre les tables sont maintenues de manière cohérente. Elle est appliquée en utilisant des clés étrangères, qui sont des colonnes d'une table qui font référence à une clé primaire d'une autre table. La clé étrangère permet de maintenir l'intégrité référentielle entre deux tables liées en s'assurant que les modifications de la clé primaire d'une table sont reflétées dans la clé étrangère correspondante d'une autre table.

Il y a deux règles principales à respecter lorsqu'il s'agit de contraintes d'intégrité référentielle :

• Si une valeur de clé primaire est mise à jour ou supprimée, les valeurs de clé étrangère correspondantes dans la table liée doivent également être mises à jour ou supprimées.
• Toute nouvelle valeur de clé étrangère ajoutée à la table connexe doit avoir une valeur de clé primaire correspondante dans l'autre table.

Mise en œuvre de contraintes d'intégrité complexes en SQL

Les contraintes d'intégrité complexes en SQL impliquent la mise en œuvre de contraintes personnalisées et l'utilisation de déclencheurs pour des scénarios spécifiques. Ces contraintes permettent à la base de données de gérer des exigences de données plus complexes que ne le permettent les contraintes standard. La mise en œuvre de contraintes complexes permet de maintenir efficacement l'intégrité, la cohérence et l'exactitude des données dans la base de données tout en répondant à des besoins professionnels spécifiques.

Création de contraintes personnalisées

La création de contraintes personnalisées consiste à définir des règles basées sur des exigences spécifiques qui ne peuvent pas être traitées à l'aide des contraintes standard prédéfinies. Ces contraintes permettent de maintenir la validité et la cohérence des données en fonction de la logique commerciale spécifique. Les contraintes personnalisées peuvent être mises en œuvre à l'aide des contraintes SQL CHECK.

Voici quelques aspects essentiels à prendre en compte lors de la création de contraintes personnalisées :

• Les contraintes personnalisées doivent être basées sur les colonnes d'une même table.
• La contrainte doit consister en une expression logique, et non en une requête SQL.
• La contrainte doit être vérifiée et maintenue pendant toutes les opérations INSERT, UPDATE et DELETE sur la table.

Pour créer une contrainte personnalisée en SQL, tu peux utiliser le mot clé CHECK suivi de la condition de contrainte souhaitée.

CREATE TABLE Employees ( employee_id INT PRIMARY KEY, salary NUMERIC(10,2), join_date DATE, CHECK (join_date > '2000-01-01' AND salary >= 20000) ) ;

En créant des contraintes personnalisées, tu peux maintenir l'intégrité des données et appliquer des règles commerciales spécifiques qui ne sont pas réalisables à l'aide des contraintes SQL standard.

Utilisation des déclencheurs pour les contraintes complexes

Les déclencheurs sont un outil puissant de SQL qui facilite la mise en œuvre de contraintes d'intégrité complexes. Les déclencheurs SQL sont liés à des événements spécifiques tels que INSERT, UPDATE, DELETE ou même des événements multiples sur des tables ou des vues spécifiques. Les déclencheurs sont automatiquement invoqués lorsque les événements définis se produisent, ce qui permet de vérifier les contraintes et d'appliquer les altérations automatiquement.

Lorsque tu dois appliquer des contraintes complexes qui impliquent plusieurs tables ou des calculs complexes, les déclencheurs deviennent une méthode appropriée pour maintenir l'intégrité des données.

Les déclencheurs peuvent être classés en deux types :

• Déclencheurs avant - Ces déclencheurs sont exécutés avant l'événement spécifié. Ils sont utiles pour maintenir des contraintes complexes en validant et en manipulant les données avant qu'elles ne soient stockées de façon permanente dans la base de données.
• Déclencheurs après - Ces déclencheurs sont exécutés après l'événement spécifié. Ils peuvent être utilisés pour maintenir l'intégrité des données en effectuant des actions supplémentaires comme la mise à jour d'autres tables ou la génération d'entrées de journal pour maintenir une piste d'audit.

CREATE TRIGGER check_project_budget AFTER INSERT ON tasks FOR EACH ROW BEGIN DECLARE project_total_cost NUMERIC(10,2) ; DECLARE project_budget NUMERIC(10,2) ; SELECT SUM(estimated_cost) INTO project_total_cost FROM tasks WHERE project_id = NEW.project_id ; SELECT budget INTO project_budget FROM projects WHERE project_id = NEW.project_id ; IF project_total_cost > project_budget THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Project budget exceeded!' ; END IF ; END ;

En résumé, l'utilisation de déclencheurs te permet de maintenir des contraintes complexes impliquant plusieurs tables et calculs, en garantissant l'intégrité et la cohérence des données, ainsi que le respect des règles de gestion. En comprenant quand et comment utiliser les contraintes personnalisées et les déclencheurs, tu peux construire une base de données robuste et optimisée pour tes besoins spécifiques.

Assurer la cohérence des données avec les contraintes d'intégrité en SQL

Les contraintes d'intégrité jouent un rôle essentiel pour assurer la cohérence des données dans les bases de données SQL en appliquant des règles pour maintenir l'intégrité des données, l'unicité et les relations référentielles entre les tables. Cette section aborde en profondeur les aspects de la modification et de l'abandon des contraintes et du maintien de l'intégrité référentielle lors des mises à jour et des suppressions.

Modifier et supprimer des contraintes

Lorsqu'il s'agit d'une base de données évolutive, il se peut que tu aies besoin de modifier ou de supprimer des contraintes pour tenir compte des changements dans la structure des données ou des exigences de l'entreprise. En SQL, tu peux facilement le faire en utilisant l'instruction ALTER TABLE pour modifier ou supprimer des contraintes.

Cependant, lorsque tu modifies ou supprimes des contraintes, il est essentiel de suivre certaines méthodes et bonnes pratiques pour éviter de compromettre l'intégrité et la cohérence de tes données. Voyons comment effectuer ces actions correctement et en toute sécurité à l'aide des étapes suivantes :

1. Identifier la contrainte à modifier ou à supprimer - Tu peux généralement trouver ces informations dans le schéma de la table ou en interrogeant l'INFORMATION_SCHEMA.
2. Assurer l'intégrité des données - Avant de modifier ou de supprimer une contrainte, assure-toi que les données existantes respectent les nouvelles exigences de la contrainte, ou règle les conflits potentiels si la contrainte est supprimée.
3. Effectuer la modification ou l'abandon à l'aide de l'instruction ALTER TABLE - Émettre les commandes SQL appropriées pour appliquer les modifications aux contraintes selon les besoins.
4. Vérifier les modifications - Confirmer que les contraintes ont été modifiées ou abandonnées avec succès en examinant le schéma de table mis à jour ou en exécutant des requêtes de test.

1. Identifie la contrainte

   :SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Employees'

   ;
2. Assure-toi que toutes les valeurs de salaire existantes sont égales ou supérieures à 18 000.
3. Effectue la modification à l'aide de l'instruction ALTER

   TABLE:ALTER TABLE Employees DROP CONSTRAINT salary_constraint ; ALTER TABLE Employees ADD CONSTRAINT salary_constraint CHECK (salary >= 18000) 

   ;
4. Vérifie les modifications en contrôlant le schéma de la table et en exécutant des requêtes de test.

Maintien de l'intégrité référentielle lors des mises à jour et des suppressions

L'intégrité référentielle, comme mentionné précédemment, est essentielle pour préserver la cohérence et la fiabilité des relations entre les tables de ta base de données. Les mises à jour et les suppressions sur les colonnes de clé primaire ou de clé étrangère peuvent potentiellement rompre l'intégrité référentielle si elles ne sont pas traitées avec soin. Il est donc crucial de se pencher sur l'impact de ces opérations sur les tables liées.

En SQL, tu peux utiliser des actions référentielles pour maintenir l'intégrité référentielle pendant les mises à jour et les suppressions. Ces actions définissent la manière dont les modifications d'une table sont propagées aux tables connexes. Les principales actions référentielles sont :

• CASCADE - Cette action met à jour ou supprime automatiquement les lignes de clé étrangère correspondantes dans la table liée lorsque la clé primaire de la table parente est mise à jour ou supprimée.
• SET NULL - Si la clé primaire est mise à jour ou supprimée, cette action définit les valeurs de clé étrangère correspondantes dans la table liée à NULL.
• SET DEFAULT - Cette action définit les valeurs de clé étrangère de la table liée à leurs valeurs par défaut lorsque la clé primaire de la table parent est mise à jour ou supprimée.
• PAS D'ACTION - Aucune action n'est effectuée sur la table liée lorsque la clé primaire est mise à jour ou supprimée dans la table parent. Cependant, la norme SQL exige que la mise à jour ou la suppression de la clé primaire soit rejetée si elle viole la contrainte d'intégrité référentielle.

Pour maintenir l'intégrité référentielle, tu peux choisir l'une des actions ci-dessus qui correspond le mieux à tes besoins spécifiques en matière de données. Définis ces actions à l'aide de la définition de la contrainte FOREIGN KEY dans l'instruction CREATE TABLE ou ALTER TABLE.

CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, product_id INT, quantity INT, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON UPDATE CASCADE ON DELETE CASCADE ) ;

En suivant ces directives, tu peux maintenir efficacement l'intégrité référentielle lors des mises à jour et des suppressions et assurer la qualité et la cohérence des données dans ta base de données SQL.