FirebirdSQL logo
 DOMAININDEX 

contrainte de clé étrangère (FOREIGN KEY)

La contrainte de clé étrangère garantit que la ou les colonnes membres ne peuvent contenir que des valeurs qui existent dans la ou les colonnes spécifiées de la table principale.Ces colonnes de référence sont souvent appelées colonnes de destination.Ils doivent être la clé primaire ou une clé unique dans la table de destination.Ils peuvent ne pas avoir de contrainte NOT NULL` s’ils font partie de la contrainte de clé unique.

Les colonnes de clés externes ne nécessitent pas de contrainte `NOT NULL'.

Au niveau de la colonne, la contrainte de clé étrangère est définie en utilisant le mot-clé REFERENCES.

... ,
ARTIFACT_ID INTEGER REFERENCES COLLECTION (ARTIFACT_ID),

Dans cet exemple, la colonne ARTIFACT_ID fait référence à une colonne du même nom dans la table COLLECTION.

Une clé étrangère sur une ou plusieurs colonnes peut être définie au niveau de la table.Les clés étrangères sur plusieurs colonnes ne peuvent être définies qu’au niveau de la table.

La syntaxe pour définir une clé étrangère au niveau de la table est légèrement différente.Après avoir défini toutes les colonnes, avec leurs contraintes de niveau colonne, vous pouvez définir une contrainte de clé étrangère de niveau table nommée en utilisant les mots-clés FOREIGN KEY et les noms des colonnes pour lesquelles elle s’applique :

... ,
CONSTRAINT FK_ARTSOURCE FOREIGN KEY(DEALER_ID, COUNTRY)
  REFERENCES DEALER (DEALER_ID, COUNTRY),

Notez que les noms des colonnes dans la table cible (maître) peuvent être différents de ceux de la clé externe.

Note

Si aucune colonne cible n’est spécifiée, la clé étrangère fait automatiquement référence aux colonnes de la clé primaire de la table cible.

Actions clés externes

Pour une intégrité des données supplémentaire, des options facultatives peuvent être spécifiées pour assurer la cohérence des données entre les tables parent et enfant selon les règles spécifiées :

  • La phrase ON UPDATE spécifie ce qui arrive aux entrées d’une sous table lorsque la valeur de la clé primaire/unique d’une ligne de la table parent est modifiée.

  • La clause ON DELETE spécifie ce qui arrive aux entrées de la sous table lorsque la ligne correspondante dans la table principale est supprimée.

Pour garantir l’intégrité référentielle d’une clé étrangère lorsque la valeur d’une clé primaire ou unique connexe est modifiée ou supprimée, les actions suivantes peuvent être effectuées :

  • NO ACTION (par défaut) — aucune action ne sera effectuée ;

  • CASCADE — Lorsqu’une valeur de clé primaire est modifiée ou supprimée, les mêmes actions seront effectuées sur la valeur de la clé étrangère. Lors de la suppression d’une ligne dans la table principale, tous les enregistrements ayant les mêmes valeurs de clé étrangère que la valeur de clé primaire (unique) de la ligne de la table principale supprimée doivent être supprimés dans la sous-table. Lors de la mise à jour d’un enregistrement de la table principale dans une table secondaire, toutes les valeurs de la clé étrangère ayant les mêmes valeurs que la valeur de la clé primaire (unique) de la ligne de la table principale qui est modifiée doivent être modifiées ;

  • SET DEFAULT — les valeurs des clés étrangères de toutes les lignes correspondantes dans la sous-table sont définies à la valeur par défaut spécifiée dans la clause DEFAULT pour cette colonne ;

  • SET NULL — les valeurs des clés étrangères de toutes les lignes correspondantes dans la sous table sont mises à NULL.

Example 1. Clé externe avec mise à jour en cascade et mise à NULL en cas de suppression
CONSTRAINT FK_ORDERS_CUST
FOREIGN KEY (CUSTOMER) REFERENCES CUSTOMERS (ID)
ON UPDATE CASCADE ON DELETE SET NULL

Contrainte CHECK

La contrainte CHECK spécifie la condition que les valeurs placées dans cette colonne doivent satisfaire.Une condition est une expression logique, également appelée prédicat, qui peut renvoyer les valeurs TRUE, FALSE, et UNKNOWN. La condition est satisfaite si le prédicat renvoie TRUE ou UNKNOWN (équivalent à NULL). Si le prédicat renvoie FALSE, la valeur ne sera pas acceptée.Cette condition est utilisée lors de l’ajout d’une nouvelle ligne à la table (l’opérateur INSERT) et lors du changement d’une valeur existante d’une colonne de la table (l’opérateur UPDATE) ainsi que les opérateurs dans lesquels une de ces actions peut se produire (UPDATE OR INSERT, MERGE).

Important

Lorsque vous utilisez la phrase CHECK pour une colonne basée sur un domaine, n’oubliez pas que l’expression dans CHECK ne fait que compléter une condition de vérification qui peut déjà être définie dans le domaine.

Au niveau de la colonne ou de la table, l’expression dans la phrase CHECK fait référence à la valeur entrante en utilisant les identifiants de colonne, contrairement aux domaines où la contrainte CHECK utilise le mot-clé VALUE à cette fin.

Example 1. Vérifier les limites des niveaux de colonnes et de tables
CREATE TABLE PLACES (
  ...
  LAT DECIMAL(9, 6) CHECK (ABS(LAT) <= 90),
  LON DECIMAL(9, 6) CHECK (ABS(LON) <= 180),
  ...
  CONSTRAINT CHK_POLES CHECK (ABS(LAT) < 90 OR LON = 0)
);

Privilèges d’exécution

La clause optionnelle SQL SECURITY dans la spécification de la table permet de spécifier avec quels privilèges les colonnes calculées sont calculées.Si l’option INVOKER est sélectionnée, les colonnes calculées le sont avec les privilèges de l’utilisateur appelant.Si l’option DEFINER est sélectionnée, les colonnes calculées sont calculées avec les privilèges de l’utilisateur définissant (propriétaire). Par défaut, les colonnes calculées sont calculées avec les privilèges de l’utilisateur appelant.En outre, les triggers héritent des privilèges d’exécution des tables, s’ils ne sont pas surchargés dans les triggers eux-mêmes.

Tip

Les privilèges d’exécution par défaut pour les objets de métadonnées nouvellement créés peuvent être modifiés à l’aide de la commande

ALTER DATABASE SET DEFAULT SQL SECURITY {DEFINER | INVOKER}