CONSTRAINT, clause

Syntaxe

Contrainte sur champ unique :

CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]}

Contrainte sur plusieurs champs :

CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
NOT NULL (notnull1[, notnull2 [, ...]]) |
FOREIGN KEY [NO INDEX] (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]}

La clause CONSTRAINT comprend les deux parties suivantes :

Remarques

Vous utilisez la syntaxe pour une contrainte sur champ unique dans la clause de définition de champ d’une instruction ALTER TABLE ou CREATE TABLE qui suit immédiatement la spécification du type de données du champ.

Vous utilisez la syntaxe pour une contrainte sur plusieurs champs chaque fois que vous utilisez la contrainte mot réservé en dehors d’une clause de définition du champ dans une instruction ALTER TABLE ou CREATE TABLE.

La clause CONSTRAINT vous permet de désigner un champ comme l’un des types suivants de contraintes :

• Vous pouvez utiliser le mot réservé UNIQUE pour désigner un champ en tant que clé unique. Cela signifie que deux enregistrements dans la table peuvent avoir la même valeur dans ce champ. Vous pouvez contraindre tout champ ou toute liste de champs comme uniques. Si une contrainte sur plusieurs champs est désignée comme clé unique, les valeurs combinées de tous les champs dans l’index doivent être uniques, même si deux enregistrements ou plus ont la même valeur dans un seul des champs.

• Vous pouvez utiliser les mots réservés PRIMARY KEY pour désigner un champ ou un ensemble de champs dans une table en tant que clé primaire. Toutes les valeurs dans la clé primaire devant être uniques et non Null, il ne peut y avoir qu’une seule clé primaire pour une table.

  Remarque : Ne définissez pas une contrainte PRIMARY KEY sur une table possédant déjà une clé primaire. Si vous le faites, une erreur se produit.

• Vous pouvez utiliser les mots réservés FOREIGN KEY pour désigner un champ en tant que clé étrangère. Si la clé primaire de la table étrangère est constituée de plus d’un champ, vous devez utiliser une définition de contrainte sur plusieurs champs répertoriant tous les champs de référencement, le nom de la table étrangère et les noms des champs référencés dans la table étrangère dans le même ordre que celui dans lequel les champs de référencement sont répertoriés. Si le ou les champs référencés constituent la clé primaire de la table étrangère, il est inutile de spécifier les champs référencés. Par défaut, le moteur de base de données se comporte comme si les champs référencés constituaient la clé primaire de la table étrangère.

  Des contraintes de clé étrangère définissent des actions spécifiques effectuées quand une valeur de clé primaire correspondante est modifiée :

• Vous pouvez spécifier des actions à effectuer sur la table étrangère en fonction d’une action correspondante appliquée à une clé primaire dans la table sur laquelle la clause CONSTRAINT est définie. Par exemple, envisagez la définition suivante pour la table Customers (Clients) :

CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))

Envisagez la définition suivante de la table Orders (Commandes), qui définit une relation de clé étrangère référençant la clé primaire de la table Customers (Clients) :

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE

Des clauses ON UPDATE CASCADE et ON DELETE CASCADE sont définies sur la clé étrangère. La clause ON UPDATE CASCADE signifie que, si l’identificateur d’un client (CustId) est mis à jour dans la table Customers (Clients), la mise à jour est répercutée en cascade dans la table Orders (Commandes). Chaque commande contenant une valeur d’ID client correspondante est automatiquement mise à jour avec la nouvelle valeur. La clause ON DELETE CASCADE signifie que, si un client est supprimé de la table Customers (Clients), toutes les lignes de la table Orders (Commandes) contenant la même valeur d’ID client sont également supprimées.

Considérez la définition différente ci-dessous de la table Orders (Commandes), qui utilise l’action SET NULL au lieu de l’action CASCADE :

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL

La clause ON UPDATE SET NULL signifie que, si l’ID d’un client (CustId) est mis à jour dans la table Customers (Clients), les valeurs de clé étrangère correspondantes dans la table Orders (Commandes) sont automatiquement définies sur NULL. De même, la clause ON DELETE SET NULL signifie que, si un client est supprimé de la table Customers (Clients), toutes les clés étrangères correspondantes dans la table Orders (Commandes) sont automatiquement définies sur NULL.

Pour empêcher la création automatique d’index pour des clés étrangères, vous pouvez utiliser le modificateur NO INDEX. N’utilisez cette forme de définition de clé étrangère que dans les cas où les valeurs d’index obtenues seraient fréquemment dupliquées. Quand les valeurs d’un index de clé étrangère sont fréquemment dupliquées, l’utilisation d’un index peut être moins efficace qu’une simple analyse de table. La tenue à jour de ce type d’index, avec des lignes insérées et supprimées dans la table, a pour effet de dégrader les performances et n’offre aucun avantage.