Intégrité des données

Ce chapitre explique comment les contraintes d'intégrité appliquent les règles métier associées à une base de données et empêchent la saisie d'informations non valides dans les tables.

Introduction à l'intégrité des données

Il est important que les données maintiennent l'intégrité des données, qui est conforme aux règles métier déterminées par l'administrateur de base de données ou le développeur d'applications.

Les règles indiquent des conditions et des relations qui doivent toujours être vraies ou fausses. Par exemple, chaque société définit ses propres politiques sur les salaires, les numéros d'employé, le suivi des stocks, etc.

Techniques de garantie de l'intégrité des données

Lors de la conception d'une application de base de données, les développeurs disposent de plusieurs options pour garantir l'intégrité des données stockées dans la base.

Les options disponibles sont les suivantes :

Voir aussi :

Avantages des contraintes d'intégrité

Une contrainte d'intégrité est un objet de schéma créé et supprimé à l'aide de SQL. Pour garantir l'intégrité des données, utilisez autant que possible des contraintes ou des assertions d'intégrité.

Voici quelques avantages des assertions et contraintes d'intégrité par rapport aux alternatives permettant d'assurer l'intégrité des données :

Voir aussi :

Types de contrainte d'intégrité

Oracle AI Database vous permet d'appliquer des contraintes au niveau de la table et de la colonne, ainsi que des assertions impliquant plusieurs tables.

Une contrainte spécifiée dans la définition d'une colonne ou d'un attribut est une spécification intégrée. Une contrainte spécifiée dans la définition de la table est une spécification hors ligne.

Une clé est la colonne ou l'ensemble de colonnes inclus dans la définition de certains types de contraintes d'intégrité. Les clés décrivent les relations entre les tables et les colonnes d'une base de donnée relationnelle. Les valeurs individuelles d'une clé sont appelées valeurs de clé.

Le tableau suivant décrit les types de contrainte. Chacun peut être spécifié en ligne ou hors ligne, à l'exception de NOT NULL, qui doit être en ligne.

Tableau 1 Types de contrainte d'intégrité

Type de contrainte Description Voir aussi
NOT NULL Autorise ou interdit l'insertion ou la mise à jour de lignes contenant une valeur null dans une colonne spécifiée. "Contraintes d'intégrité NOT NULL"
Clé unique Empêche plusieurs lignes d'avoir la même valeur dans la même colonne ou une combinaison de colonnes mais autorise la Présence de valeurs NULL. "Contraintes uniques"
Clé primaire Combine une contrainte NOT NULL et une contrainte unique. Elle interdit à plusieurs lignes d'avoir la même valeur dans la même colonne ou la même combinaison de colonnes et empêche les valeurs d'être NULL. "Contraintes de clé primaire"
Clé étrangère Désigne une colonne en tant que clé étrangère et établit une relation entre la clé étrangère et une clé primaire ou unique, appelée clé référencée. Contraintes de clé étrangère
Chèque Nécessite une valeur de base de données pour obéir à une condition spécifiée. "Contraintes de vérification"
REF Indique les types de manipulation de données autorisés sur les valeurs d'une colonne REF et la façon dont ces actions affectent les valeurs dépendantes. Dans une base de données relationnelle objet, un type de données intégré appelé REF encapsule une référence à un objet de ligne d'un type d'objet spécifié. Les contraintes d'intégrité référentielle sur les colonnes REF garantissent l'existence d'un objet de ligne pour REF. Guide du développeur Oracle AI Database Object-Relational pour en savoir plus sur les contraintes REF
Assertions Les assertions sont des expressions booléennes qui impliquent des sous-interrogations dans les tables de base de données d'une application. Les assertions ont la sémantique d'une contrainte, c'est-à-dire que la base de données est chargée de garantir la vérité de ces expressions booléennes pendant que les transactions modifient les données et valident ces modifications. Assertions SQL pour en savoir plus sur les assertions

Voir aussi :

Contraintes d'intégrité NOT NULL

Une contrainte NOT NULL exige qu'une colonne d'une table ne contienne aucune valeur NULL. Un null est l'absence de valeur. Par défaut, toutes les colonnes d'une table autorisent les valeurs NULL.

Les contraintes NOT NULL sont destinées aux colonnes qui ne doivent pas manquer de valeurs. Par exemple, la table hr.employees requiert une valeur dans la colonne email. Une tentative d'insertion d'une ligne de salarié sans adresse e-mail génère une erreur :

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

Vous pouvez uniquement ajouter une colonne avec une contrainte NOT NULL si la table ne contient aucune ligne ou si vous indiquez une valeur par défaut.

Voir aussi :

Contraintes uniques

Une contrainte de clé unique requiert que chaque valeur d'une colonne ou d'un ensemble de colonnes soit unique. Aucune ligne d'une table ne peut contenir de valeurs en double dans une seule colonne (clé unique) ou dans un ensemble de colonnes (clé unique composite) avec une contrainte de clé unique.

Remarque : le terme clé fait uniquement référence aux colonnes définies dans la contrainte d'intégrité. Etant donné que la base de données applique une contrainte unique en créant ou en réutilisant implicitement un index sur les colonnes de clé, le terme clé unique est parfois utilisé de manière incorrecte comme synonyme de contrainte de clé unique ou d'index unique.

Les contraintes de clé unique sont appropriées pour toute colonne dans laquelle les valeurs en double ne sont pas autorisées. Les contraintes uniques diffèrent des contraintes de clé primaire, dont le but est d'identifier chaque ligne de la table de manière unique, et contiennent généralement des valeurs qui n'ont pas d'autre importance que d'être uniques. Exemples de clés uniques :

Comme indiqué dans l'exemple 4-1, une contrainte de clé unique existe sur la colonne email de la table hr.employees. La partie pertinente de la déclaration est la suivante :

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

La contrainte emp_email_uk garantit qu'aucun employé n'a la même adresse électronique, comme indiqué dans l'exemple suivant :

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Sauf si une contrainte NOT NULL est également définie, une valeur NULL satisfait toujours une contrainte de clé unique. Par conséquent, les colonnes avec des contraintes de clé uniques et des contraintes NOT NULL sont typiques. Cette combinaison force l'utilisateur à saisir des valeurs dans la clé unique et élimine la possibilité que de nouvelles données de ligne entrent en conflit avec des données de ligne existantes.

Remarque : en raison du mécanisme d'interrogation des contraintes de clé unique sur plusieurs colonnes, vous ne pouvez pas avoir de valeurs identiques dans les colonnes non NULL d'une contrainte d'une clé unique composite partiellement NULL.

Exemple 7-1 Contrainte unique

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Voir aussi :

Contraintes de clé primaire

Dans une contrainte de clé primaire, les valeurs du groupe d'une ou de plusieurs colonnes soumises à la contrainte identifient de manière unique la ligne. Chaque table peut avoir une clé primaire, qui en fait nomme la ligne et garantit l'absence de lignes en double.

Une clé primaire peut être naturelle ou de substitution. Une clé naturelle est un identificateur significatif constitué d'attributs existants dans une table. Par exemple, une clé naturelle peut être un code postal dans une table de consultation.

En revanche, une clé de substitution est un identificateur d'incrémentation généré par le système qui garantit l'unicité au sein d'une table. En général, une séquence ou un UUID génère des clés de substitution.

L'implémentation de la contrainte de clé primaire par Oracle AI Database garantit que les affirmations suivantes sont vraies :

Une situation typique appelant à une clé primaire est l'identifiant numérique d'un employé. Chaque employé doit avoir un ID unique. Un employé doit être décrit par une seule ligne dans la table employees.

L'exemple de la section Contraintes uniques indique qu'un employé existant a l'ID 202, où l'ID d'employé est la clé primaire. L'exemple suivant illustre une tentative d'ajout d'un salarié avec le même matricule et d'un salarié sans matricule :

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

La base de données applique les contraintes de clé primaire avec un index. En général, une contrainte de clé primaire créée pour une colonne crée implicitement un index unique et une contrainte NOT NULL. Notez les exceptions suivantes à cette règle :

Par défaut, le nom de l'index créé implicitement est celui de la contrainte à clé primaire. Vous pouvez également indiquer un nom défini par l'utilisateur pour un index. Vous pouvez indiquer des options de stockage pour l'index en incluant la clause ENABLE dans l'instruction CREATE TABLE ou ALTER TABLE utilisée pour créer la contrainte.

Voir aussi : Oracle AI Database Get Started with Oracle AI Database Development et Oracle AI Database Development Guide pour découvrir comment ajouter des contraintes de clé primaire à une table

Contraintes de clé étrangère

Chaque fois que deux tables contiennent une ou plusieurs colonnes communes, Oracle AI Database peut appliquer la relation entre les deux tables via une contrainte de clé étrangère, également appelée contrainte d'intégrité référentielle.

Une contrainte de clé étrangère exige que pour chaque valeur de la colonne sur laquelle la contrainte est définie, la valeur de l'autre table et colonne spécifiées corresponde. Un salarié ne peut travailler que pour un service existant, par exemple, une règle d'intégrité référentielle.

Le tableau suivant répertorie les termes associés aux contraintes d'intégrité référentielle.

Tableau 2 Termes de contrainte d'intégrité référentielle

Terme Définition
Clé étrangère

Colonne ou ensemble de colonnes inclus dans la définition de la contrainte qui référence une clé référencée. Par exemple, la colonne department_id dans employees est une clé étrangère qui référence la colonne department_id dans departments.

Les clés étrangères peuvent être définies en tant que colonnes multiples. Cependant, une clé étrangère composite doit référencer une clé primaire composite ou une clé unique avec le même nombre de colonnes et les mêmes types de données.

La valeur des clés étrangères peut correspondre à la valeur de clé primaire ou unique référencée, ou être NULL. Si une colonne d'une clé étrangère composite est NULL, les parties non NULL de la clé ne doivent pas nécessairement correspondre à la partie correspondante d'une clé parent.
Clé référencée Clé unique ou clé primaire de la table référencée par une clé étrangère. Par exemple, la colonne department_id dans departments est la clé référencée pour la colonne department_id dans employees.
Table dépendante ou enfant Table qui inclut la clé étrangère. Cette table dépend des valeurs présentes dans la clé unique ou primaire référencée. Par exemple, la table employees est un enfant de departments.
Table référencée ou parent Table référencée par la clé étrangère de la table enfant. C'est la clé référencée de cette table qui détermine si des insertions ou des mises à jour spécifiques sont autorisées dans la table enfant. Par exemple, la table departments est un parent de employees.

La figure 7-1 présente une clé étrangère sur la colonne employees.department_id. Elle garantit que chaque valeur de cette colonne doit correspondre à une valeur de la colonne departments.department_id. Par conséquent, la colonne employees.department_id ne contient aucun numéro de département erroné.

Figure 1 : Contraintes d'intégrité référentielle

Description de l'image cncpt056.gif

Contraintes d'intégrité auto-référentielle

Une contrainte d'intégrité auto-référentielle est une clé étrangère qui référence une clé parent dans la même table.

Dans la figure suivante, une contrainte auto-référentielle garantit que chaque valeur de la colonne employees.manager_id correspond à une valeur existante dans la colonne employees.employee_id. Par exemple, le responsable de l'employé 102 doit exister dans la table employees. Cette contrainte élimine le risque de numéros d'employé erronés dans la colonne manager_id.

Figure 2 : Contraintes référentielles à table unique

Description de l'image cncpt057.gif

Valeurs NULL et clés étrangères

Le modèle relationnel permet à la valeur des clés étrangères de correspondre à la valeur de clé primaire ou unique référencée, ou d'être NULL. Par exemple, une ligne dans hr.employees peut ne pas indiquer d'ID de département.

Si une colonne d'une clé étrangère composite est NULL, les parties non NULL de la clé ne doivent pas nécessairement correspondre à la partie correspondante d'une clé parent. Par exemple, une table reservations peut contenir une clé étrangère composite sur les colonnes table_id et date, mais table_id est NULL.

Modifications de clé parent et clés étrangères

La relation entre la clé étrangère et la clé parent a des implications pour la suppression des clés parent. Par exemple, si un utilisateur tente de supprimer l'enregistrement de ce service, que deviennent les enregistrements des employés de ce service ?

Lorsqu'une clé parent est modifiée, les contraintes d'intégrité référentielle peuvent spécifier les actions suivantes à effectuer sur les lignes dépendantes d'une table enfant :

Le tableau 7-3 décrit les instructions LMD autorisées par les différentes actions référentielles sur les valeurs de clé dans la table parent et les valeurs de clé étrangère dans la table enfant.

Tableau 3 Instructions LMD autorisées par la mise à jour et la suppression sans action

Instruction DML Emis sur table parent Table Emis pour enfant
INSERT Toujours OK si la valeur de clé parent est unique OK uniquement si la valeur de clé étrangère existe dans la clé parent ou est partiellement ou entièrement NULL
UPDATE NO ACTION Autorisé si l'instruction ne laisse aucune ligne dans la table enfant sans valeur de clé parent référencée Autorisé si la nouvelle valeur de clé étrangère référence toujours une valeur de clé référencée
DELETE NO ACTION Autorisé si aucune ligne de la table enfant ne référence la valeur de clé parent Toujours OK
DELETE CASCADE Toujours OK Toujours OK
DELETE SET NULL Toujours OK Toujours OK

Remarque : d'autres actions référentielles non prises en charge par les contraintes d'intégrité FOREIGN KEY d'Oracle Database peuvent être appliquées à l'aide de déclencheurs de base de données. Reportez-vous à Présentation des déclencheurs.

Voir aussi : Référence du langage SQL Oracle AI Database pour en savoir plus sur la clause ON DELETE

Index et clés étrangères

En règle générale, les clés étrangères doivent être indexées. La seule exception est lorsque la clé unique ou primaire correspondante n'est jamais mise à jour ou supprimée.

L'indexation des clés étrangères dans les tables enfant présente les avantages suivants :

Voir aussi :

Contraintes de vérification

Une contrainte de vérification sur une colonne ou un ensemble de colonnes requiert qu'une condition spécifiée soit vraie ou inconnue pour chaque ligne.

Si le code DML entraîne l'évaluation de la condition de la contrainte sur False, l'instruction SQL est annulée (rollback). Le principal avantage des contraintes de vérification est la capacité à appliquer des règles d'intégrité très spécifiques. Par exemple, vous pouvez utiliser des contraintes de vérification pour appliquer les règles suivantes dans la table hr.employees :

L'exemple suivant crée une contrainte de salaire maximum sur employees et montre ce qui se passe lorsqu'une instruction tente d'insérer une ligne contenant un salaire supérieur au maximum :

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

La définition d'une colonne peut comporter plusieurs contraintes Check qui référencent la colonne. Par exemple, la colonne salary peut avoir une contrainte qui empêche les valeurs supérieures à 10000 et une contrainte distincte qui empêche les valeurs inférieures à 500.

S'il existe plusieurs contraintes de vérification pour une colonne, elles doivent être conçues de manière à ce que leurs objectifs ne soient pas contradictoires. Aucun ordre d'évaluation des conditions ne peut être supposé. La base de données ne vérifie pas que les conditions de vérification ne s'excluent pas mutuellement.

Voir aussi : Référence du langage SQL Oracle AI Database pour en savoir plus sur les restrictions relatives aux contraintes de vérification

Contraintes CHECK contrôlables

Une contrainte de vérification marquée comme PRECHECK peut être vérifiée en dehors de la base de données.

La définition d'une contrainte CHECK sur l'état PRECHECK est effectuée lors de la création ou de la modification de la table. L'état PRECHECK complète les états ENABLE et VALIDATE d'une contrainte et une contrainte dont les trois états peuvent être actifs en même temps.

Si vous utilisez JSON pour exprimer la contrainte de vérification, Oracle AI Database vous permet d'exporter un schéma JSON et de valider les données JSON par rapport à un valideur de schéma JSON dans le client d'application. Lorsqu'une contrainte est définie sur l'état PRECHECK, elle indique qu'elle dispose d'un schéma JSON équivalent qui conserve la sémantique de la contrainte. Les développeurs d'application client peuvent pré-valider la contrainte au sein de l'application client. La base de données vérifie la contrainte et une erreur est générée si elle ne peut pas être exprimée dans le schéma JSON.

Rubriques connexes

Assertions

Les assertions sont des expressions booléennes qui impliquent des sous-interrogations dans les tables de base de données d'une application.

Les assertions ont la sémantique d'une contrainte, c'est-à-dire que la base de données est chargée de garantir la vérité de ces expressions booléennes pendant que les transactions modifient les données et valident ces modifications.

L'instruction SQL suivante crée une assertion, nommée NO_TRAINERS_IN_BOSTON, pour exiger qu'aucun formateur ne soit employé dans les services basés à Boston. Le niveau supérieur NOT EXISTS représente une expression booléenne qui exige que l'ensemble de résultats de la jointure entre les tables EMP et DEPT reste vide.

CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
   (SELECT 'trainer in Boston'
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
      AND  e.job = 'TRAINER'
      AND  d.loc = 'BOSTON'));

Les assertions sont des objets de niveau schéma. Cela implique que si des tables d'autres schémas sont référencées par l'expression booléenne, le privilège objet ASSERTION REFERENCES est requis pour accéder à ces tables. Il ne s'agit pas d'une clé étrangère qui s'étend sur deux schémas, c'est-à-dire qu'un privilège objet REFERENCES doit être en place pour créer une clé étrangère qui pointe vers une clé primaire d'une table dans un schéma différent.

Les assertions sont des contraintes génériques d'intégrité des données. Ils pourraient être utilisés pour remplacer les contraintes CHECK, PRIMARY KEY, UNIQUE et FOREIGN KEY.

Par exemple, supposons les contraintes suivantes sur la table EMP :

Ces contraintes peuvent être implémentées avec les affirmations suivantes :

CREATE ASSERTION commission_not_greater_than_salary CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  e.comm > e.sal));
CREATE ASSERTION emp_empno_pk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.empno = e2.empno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_ename_deptno_uk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.ename = e2.ename
     AND  e1.deptno = e2.deptno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_fk_dept CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  NOT EXISTS
     (SELECT ''
      FROM   dept d
      WHERE  d.deptno = e.deptno)));

Cependant, nous vous conseillons fortement de toujours utiliser des contraintes chaque fois que cela est possible, et d'utiliser uniquement des assertions lorsque vous ne pouvez pas utiliser une contrainte.

Rubriques connexes

Etats des contraintes et assertions d'intégrité

Dans le cadre de la définition de contrainte ou d'assertion, vous pouvez indiquer comment et quand Oracle AI Database doit appliquer la contrainte ou l'assertion, déterminant ainsi l'état de la contrainte ou de l'assertion.

Recherche les données modifiées et existantes

La base de données vous permet d'indiquer si une contrainte ou une assertion s'applique à des données existantes ou futures. Si une contrainte ou une assertion est activée, la base de données vérifie les nouvelles données au fur et à mesure qu'elles sont entrées ou mises à jour. Les données non conformes à la contrainte ou à l'assertion ne peuvent pas entrer dans la base de données.

Par exemple, l'activation d'une contrainte NOT NULL sur employees.department_id garantit que chaque ligne future possède un ID de service. Si une contrainte est désactivée, la table peut contenir des lignes qui violent la contrainte.

Vous pouvez définir des contraintes et des assertions sur l'un des modes de validation suivants :

Le comportement de VALIDATE et NOVALIDATE dépend toujours de l'activation ou de la désactivation de la contrainte ou de l'assertion. Le tableau suivant récapitule les relations.

Tableau 4 Vérifications des données modifiées et existantes

Données modifiées Données existantes Synthèse
ENABLE VALIDATE Les données existantes et futures doivent obéir à la contrainte ou à l'affirmation. Une tentative d'application d'une nouvelle contrainte ou assertion à une table alimentée génère une erreur si des lignes existantes violent la contrainte ou l'assertion.
ENABLE NOVALIDATE La base de données vérifie la contrainte ou l'assertion, mais elle n'est pas nécessairement vraie pour toutes les lignes. Par conséquent, les lignes existantes peuvent violer la contrainte ou l'assertion, mais les lignes nouvelles ou modifiées doivent être conformes aux règles. Ce mode est souvent utilisé dans les data warehouses qui contiennent des données existantes dont l'intégrité a déjà été vérifiée.
DISABLE VALIDATE La base de données désactive la contrainte, supprime son index et empêche la modification des colonnes contraintes. Une assertion ne peut pas être dans cet état.
DISABLE NOVALIDATE La contrainte ou l'assertion n'est pas vérifiée et n'est pas nécessairement vraie.

Voir aussi : Référence du langage SQL d'Oracle AI Database pour en savoir plus sur les états de contrainte

Vérification de la validité des contraintes et assertions par la base de données

Chaque contrainte et assertion est soit dans un état non différable (par défaut), soit dans un état différable. Cet état détermine quand Oracle AI Database vérifie la validité de la contrainte ou de l'assertion.

Le graphique suivant présente les options pour les contraintes et les assertions pouvant être différées.

Figure 3 : Options pour les contraintes et assertions pouvant être différées

Description de l'image cncpt313.gif

Contraintes et assertions non transférables

Dans une contrainte non transférable, Oracle AI Database ne reporte jamais la vérification de validité de la contrainte ou de l'assertion jusqu'à la fin de la transaction. Au lieu de cela, la base de données vérifie la contrainte ou l'assertion à la fin de chaque instruction. Si la contrainte ou l'assertion est violée, l'instruction est annulée.

Par exemple, une contrainte NOT NULL non applicable existe pour la colonne employees.last_name. Si une session tente d'insérer une ligne sans nom, la base de données annule immédiatement l'instruction car la contrainte NOT NULL est violée. Aucune ligne n'est insérée.

Contraintes et assertions pouvant être différées

Une contrainte ou assertion pouvant être différée permet à une transaction d'utiliser la clause SET CONSTRAINT pour différer la vérification de cette contrainte ou assertion jusqu'à ce qu'une instruction COMMIT soit émise. Si vous apportez des modifications à la base de données susceptibles de violer la contrainte ou l'assertion, ce paramètre vous permet de désactiver la contrainte ou l'assertion jusqu'à ce que toutes les modifications soient terminées.

Vous pouvez définir le comportement par défaut lorsque la base de données vérifie la contrainte ou l'assertion pouvant être différée. Vous pouvez spécifier l'un des attributs suivants :

Supposons qu'une contrainte NOT NULL pouvant être différée sur employees.last_name soit définie sur INITIALLY DEFERRED. Un utilisateur crée une transaction avec 100 instructions INSERT, dont certaines ont des valeurs NULL pour last_name. Lorsque l'utilisateur tente de valider, la base de données annule les 100 instructions. Toutefois, si cette contrainte était définie sur INITIALLY IMMEDIATE, la base de données n'annulerait pas la transaction.

Si une contrainte provoque une action, la base de données considère cette action comme faisant partie de l'instruction qui l'a provoquée, que la contrainte soit différée ou immédiate. Par exemple, la suppression d'une ligne dans departments entraîne la suppression de toutes les lignes dans employees qui référencent la ligne de département supprimée. Dans ce cas, la suppression de employees est considérée comme faisant partie de l'instruction DELETE exécutée sur departments.

Voir aussi : Référence du langage SQL d'Oracle AI Database pour plus d'informations sur les attributs de contrainte et leurs valeurs par défaut

Exemples de vérification de contrainte et d'assertion

Les exemples suivants illustrent le moment où Oracle AI Database effectue la vérification des contraintes et des assertions.

Supposons les éléments suivants :

Exemple : insertion d'une valeur dans une colonne de clé étrangère lorsqu'aucune valeur de clé parent n'existe

Cet exemple concerne l'insertion de la première ligne dans la table employees. Aucune ligne n'existe actuellement. Comment saisir une ligne si la valeur de la colonne manager_id ne peut pas référencer une valeur existante dans la colonne employee_id ?

Les possibilités sont les suivantes :

Les valeurs par défaut sont incluses dans une instruction INSERT avant l'analyse de l'instruction. Par conséquent, les valeurs de colonne par défaut sont soumises à toute vérification des contraintes d'intégrité.

Exemple : mise à jour de toutes les valeurs de clé étrangère et de clé parent

Dans cet exemple, une contrainte auto-référentielle rend les entrées de la colonne manager_id de employees dépendantes des valeurs de la colonne employee_id.

La société a été vendue. En raison de cette vente, tous les numéros d'employé doivent être mis à jour pour correspondre à la valeur actuelle plus 5000 afin de se coordonner avec les numéros d'employé de la nouvelle société. Comme le montre le graphique suivant, certains employés sont également des responsables :

Figure 4 : Tableau des employés avant les mises à jour

Description de l'image cncpt058.gif

Les matricules de responsable étant également des matricules de salarié, ils doivent également augmenter de 5000. Vous pouvez exécuter l'instruction SQL suivante pour mettre à jour les valeurs :

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

Bien qu'une contrainte soit définie pour vérifier que chaque valeur manager_id correspond à une valeur employee_id, l'instruction précédente est valide car la base de données vérifie effectivement les contraintes une fois l'instruction terminée. La figure 7-5 montre que la base de données effectue les actions de l'ensemble de l'instruction SQL avant de vérifier les contraintes.

Figure 5 : Vérification des contraintes

Description de l'image cncpt059.gif

Les exemples de cette section illustrent le mécanisme de vérification des contraintes lors des instructions INSERT et UPDATE, mais la base de données utilise le même mécanisme pour tous les types d'instruction LMD. La base de données utilise le même mécanisme pour tous les types de contrainte, pas seulement les contraintes auto-référentielles. Remarque : les opérations sur une vue ou un synonyme sont soumises aux contraintes d'intégrité définies dans les tables de base.

Exemple : chaque manager doit avoir un commis dans le même service

Cet exemple crée une assertion pour s'assurer que chaque manager a un employé dans le même département.

Dans cet exemple, vous devez d'abord insérer le commis, puis le responsable. Lors de l'exécution de plusieurs insertions de ligne, comme pour les contraintes, les assertions sont validées après l'exécution de chaque instruction INSERT. Vous pouvez définir cette assertion sur DEFERRABLE afin de vous permettre d'insérer d'abord le gestionnaire, puis le greffier. La validation aurait lieu à l'emplacement COMMIT au lieu de chaque instruction INSERT.

CREATE ASSERTION manager_has_clerk check
(NOT EXISTS
  (SELECT 'manager without clerk'
   FROM   employees m
   WHERE  m.job_id = 'ST_MAN'
     AND  NOT EXISTS
            (SELECT 'a clerk in same department'
             FROM   employees c
             WHERE  c.job_id = 'ST_CLERK'
               AND  c.department_id = m.department_id)));