Contraintes de base de données dans Firebird et InterBase

_NOTICE: Ce document est un chapitre du livre « The InterBase World » écrit par Alexey Kovyazin et Serg Vostrikov.

Ce chapitre est consacré aux contraintes des bases de données InterBase et Firebird. Les contraintes de base de données sont des règles qui définissent les interrelations entre les tables et peuvent vérifier et modifier les données d’une base de données. Ces règles sont réalisées sous forme d’objets de base de données spéciaux. Le principal avantage de l’utilisation des contraintes réside dans la capacité à implémenter la vérification des données, et une partie de la logique métier de l’application, au niveau de la base de données, c’est-à-dire à centraliser et simplifier celle-ci, rendant ainsi le développement d’applications de bases de données plus facile et plus fiable.

Les développeurs débutants négligent souvent l’utilisation des contraintes de base de données, considérant qu’elles entravent le travail créatif. Cependant, en réalité, une telle opinion est formée sur une connaissance insuffisante de la théorie et de la pratique de la conception de bases de données.

Dans le même temps, les concepteurs les plus expérimentés osent refuser d’utiliser certains types de contraintes, ce qui permet à leurs applications de gagner en vitesse. L’expérience des concepteurs experts leur permet de très bien comprendre le fonctionnement du serveur et de prédire précisément son comportement dans des cas complexes. Il est donc préférable pour les programmeurs débutants en InterBase de ne pas recourir à de telles actions de la part de collègues expérimentés.

Dans ce livre, nous ne traitons pas de la conception de bases de données ; pour plus d’informations sur cette question, voir la liste de la littérature à la fin du livre. Ici, nous passerons en revue tous les types de contraintes dans une base de données InterBase et examinerons des exemples de leur application.

Types de contraintes dans une base de données

Il existe les types de contraintes suivants dans une base de données InterBase :

PRIMARY KEY ;
UNIQUE KEY ;
FOREIGN KEY

- peut activer des déclencheurs automatiques - ON UPDATE et ON DELETE ;

CHECK

Dans les chapitres précédents, nous avons mentionné certaines de ces contraintes, car cela était nécessaire pour la présentation logique du matériel, mais maintenant nous examinerons leur syntaxe, leur application et leur implémentation plus en détail. Les contraintes de base de données sont de deux types : basées sur un seul champ et basées sur plusieurs champs de la table. La syntaxe des deux types de contraintes est donnée ci-dessous.

= [CONSTRAINT contrainte]

[ …]

= {UNIQUE | PRIMARY KEY

| CHECK ( )

| REFERENCES autre_table [( autre_col [, autre_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

}

La syntaxe des contraintes basées sur plusieurs champs est la suivante :

= [CONSTRAINT contrainte]

[< tcontrainte> …]

= {{PRIMARY KEY | UNIQUE} ( col [, col …])

| FOREIGN KEY ( col [, col …]) REFERENCES autre_table[( autre_col [, autre_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

| CHECK ( )}

La différence de syntaxe entre les contraintes basées sur un champ et celles basées sur plusieurs champs est évidente - dans ce dernier cas, nous pouvons spécifier plusieurs champs inclus dans la contrainte. Dans le cas d’une contrainte basée sur un seul champ, toutes les options décrites ne concernent que le champ actuel. Certes, ces deux types de contraintes ont une manière différente de leur application : les contraintes basées sur un champ sont simplement ajoutées à la définition du champ requis, et les contraintes basées sur plusieurs champs sont spécifiées après la virgule dans la définition générale de la table. Des exemples détaillés sont donnés dans les parties suivantes de ce chapitre.

Exemple de contrainte typique

En réalité, les contraintes basées sur un champ sont un cas particulier des contraintes basées sur plusieurs champs.

L’exemple de création d’une contrainte de clé primaire utilisant ces deux approches différentes est donné ci-dessous. Créons une table contenant un seul champ et définissons une contrainte de clé primaire sur celle-ci.

Voici l’exemple de la clé primaire utilisant la syntaxe de contrainte basée sur un champ :

CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); Dans cet exemple, une clé primaire nommée pktest est créée pour le champ ID_PK. En conséquence, nous avons une description plutôt compacte sur une seule ligne. Nous pouvons utiliser la syntaxe des contraintes basées sur plusieurs champs dans le même but : CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));

Création de contraintes

Examinons la création de contraintes plus en détail. Le premier élément dans la description de la syntaxe commune des contraintes est l’option [CONSTRAINT contrainte]. Comme vous pouvez le voir, cette option est placée entre crochets, c’est-à-dire qu’elle est facultative.

En utilisant cette option, vous pouvez définir le nom de la contrainte créée, que ce soit dans le cas de l’application de la syntaxe des contraintes basées sur un champ ou dans le cas des contraintes basées sur plusieurs champs. Si vous n’avez pas spécifié le nom de la contrainte, InterBase le générera automatiquement. Néanmoins, il est préférable de définir le nom de la contrainte créée pour améliorer la lisibilité du schéma de base de données et simplifier la gestion des contraintes par la suite.

Après avoir défini le nom de la contrainte, son type doit être défini. Examinons les différents types de contraintes dans l’ordre où ils sont indiqués dans la description de la syntaxe commune des contraintes.

Clés primaires et uniques

Les clés primaires sont l’un des principaux types de contraintes de base de données. Elles sont appliquées pour l’identification univoque des enregistrements dans la table. Supposons que nous stockions une liste de personnes dans une base de données. Il est tout à fait possible qu’il y ait deux (ou plus) personnes avec le même nom, prénom et patronyme. Comment pouvons-nous distinguer une personne d’une autre (certainement, la question est de distinguer une personne d’une autre selon les informations stockées dans une base de données) ?

Dans ce cas, « personne » est représentée par un enregistrement dans la table, nous pouvons donc poser une question plus générale : comment pouvons-nous distinguer un enregistrement dans (n’importe quelle) table d’un autre enregistrement dans la même table. Pour cela, des contraintes - les clés primaires - sont utilisées. La clé primaire représente un ou plusieurs champs dans la table, dont la combinaison est unique pour chaque enregistrement. Il n’y a pas de valeurs répétitives d’une clé primaire pour une même table.

Les clés uniques remplissent la même fonction - elles servent également à l’identification univoque des enregistrements dans la table. La différence entre les clés primaires et uniques est qu’il ne peut y avoir qu’une seule clé primaire dans la table, alors que pour les clés uniques, il peut y en avoir plusieurs. Il est à noter qu’une clé primaire et une clé unique peuvent toutes deux être utilisées comme base de référence pour les clés étrangères (voir plus loin).

La description formelle des notions de clés primaires et uniques, ainsi que d’autres définitions importantes, se trouve dans l’annexe « Glossaire » à la fin du livre. La syntaxe de création d’une clé primaire et d’une clé unique basée sur un champ unique est la suivante :

< pkukconstraint > = [CONSTRAINT contrainte] {PRIMARY KEY | UNIQUE}

Exemples de clés primaires et uniques :

CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*une clé primaire*/

uk1 VARCHAR(50) NOT NULL UNIQUE,/*une clé unique */

uk2 INTEGER NOT NULL UNIQUE /\* encore une clé unique */);

Syntaxe de création de clés primaires et uniques basées sur plusieurs champs :

= [CONSTRAINT contrainte] {PRIMARY KEY | UNIQUE} ( col [, col …])

Cette syntaxe permet de créer des clés sur la base d’une combinaison de champs. Voici les exemples de création de clés primaires et uniques à partir de plusieurs champs :

CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*clé primaire pkt basée sur deux champs*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*clé unique ukt1 basée sur deux champs*/

Notez que tous les champs inclus dans les clés primaires et uniques doivent être déclarés comme NOT NULL, car ces clés ne peuvent pas avoir de valeur indéfinie. Outre la création de la contrainte de clés primaires et uniques lors de la création de la table, il existe une capacité à ajouter des contraintes à une table qui existe déjà. Dans ce cas, l’instruction DDL : ALTER TABLE est utilisée. La syntaxe d’ajout de contraintes de clé primaire ou unique à une table existante est similaire à celle décrite ci-dessus :

ALTER TABLE nom_table ADD [CONSTRAINT contrainte] {PRIMARY KEY | UNIQUE} ( col [, col …])

Examinons l’exemple de création d’une clé primaire et unique en utilisant ALTER TABLE :

CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));

Ensuite, nous ajoutons les clés. La primaire d’abord :

ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);

Puis l’unique : ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);

Il convient de noter que seul le propriétaire de cette table ou l’administrateur système SYSDBA (pour plus de détails sur les propriétaires et l’utilisateur SYSDBA, voir le chapitre « Sécurité dans InterBase : utilisateurs, leurs fonctions et droits » - partie 4) peut effectuer l’ajout (ainsi que la suppression) de clés primaires et uniques à la table.

Clés étrangères

La prochaine contrainte fréquemment utilisée dans les bases de données InterBase est la contrainte de clé étrangère. C’est un outil très puissant pour assurer l’intégrité référentielle dans une base de données, qui permet non seulement de superviser la présence de références correctes dans une base de données, mais aussi de contrôler ces références automatiquement !

Le principe de la création d’une clé étrangère est le suivant : si deux tables servent à stocker des informations interdépendantes, il est nécessaire de garantir que cette interrelation sera toujours correcte. Par exemple, le document « lettre de voiture » contenant un en-tête général (date, numéro de la lettre de voiture, etc.) et un ensemble d’enregistrements détaillés (description des marchandises, quantité, etc.).

Pour stocker un tel document, deux tables sont créées dans une base de données - une pour stocker les en-têtes des lettres de voiture, et la seconde - pour stocker le contenu de la lettre de voiture - les enregistrements sur les marchandises et leur quantité. Ces tables sont appelées table principale et table subordonnée, ou table maître et table de détail.

Selon le bon sens, le contenu de la lettre de voiture ne peut pas exister sans la présence de son en-tête. En d’autres termes, nous ne pouvons pas insérer un enregistrement sur les marchandises si nous n’avons pas créé l’en-tête de la lettre de voiture, et nous ne pouvons pas supprimer un enregistrement d’en-tête s’il existe des enregistrements sur les marchandises. Pour réaliser un tel comportement, la table d’en-tête et la table de détails sont reliées à l’aide d’une contrainte de clé étrangère.

Examinons le sens de la définition de contraintes de clé étrangère à l’exemple des tables contenant les informations sur les lettres de voiture. Pour cela, nous créerons deux tables pour stocker la lettre de voiture - la table TITLE pour stocker l’en-tête et la table INVENTORY pour stocker les informations sur les marchandises incluses dans la lettre de voiture.

CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));

Notez que nous avons défini une clé primaire dans la table d’en-tête basée sur le champ ID_TITLE immédiatement. Les autres champs de la table TITLE contiennent des informations triviales sur l’en-tête de la lettre de voiture - date, numéro, commentaire.

Définissons maintenant la table pour stocker les informations sur les marchandises incluses dans la lettre de voiture :

CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);

Voyons quels champs sont inclus dans la table INVENTORY. Premièrement, il y a ID_INVENTORY - une clé primaire de cette table. Ensuite vient le champ entier FK_TITLE servant de référence à l’identifiant ID_TITLE de l’en-tête dans la table des en-têtes de lettres de voiture. Suivent ensuite les champs ProductName, Kolvo et Positio décrivant la description des marchandises, leur quantité et une position dans la lettre de voiture. Le champ FK_TITLE est le plus important pour notre exemple. Si nous voulons afficher les informations sur les marchandises d’une certaine lettre de voiture, nous devons utiliser la requête suivante, dans laquelle le paramètre mas_ID_TITLE définit l’identifiant de l’en-tête :

SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE

Virtuellement, dans la situation décrite, rien n’empêche de remplir la table INVENTORY avec des enregistrements se référant à des enregistrements inexistants dans la table TITLE. De plus, rien n’empêche de supprimer l’en-tête d’une lettre de voiture déjà existante, ce qui pourrait rendre les enregistrements sur les marchandises « sans propriétaire ». Le serveur n’interdira pas d’exécuter toutes ces insertions et suppressions. Ainsi, le contrôle de l’intégrité des données dans une base de données est entièrement confié à l’application cliente. Cependant, vous savez que plusieurs applications, développées peut-être par différents programmeurs, peuvent travailler avec une même base de données, ce qui peut conduire à des interprétations différentes des données et à des erreurs. Par conséquent, il est essentiel de définir la contrainte explicite selon laquelle seuls les enregistrements sur les marchandises ayant la référence correcte à l’en-tête de la lettre de voiture peuvent être insérés dans la table INVENTORY. C’est, en fait, une contrainte de clé étrangère qui permet d’insérer uniquement les valeurs qui se trouvent dans l’autre table dans les champs inclus dans les contraintes.

Une telle contrainte peut être créée à l’aide d’une clé étrangère. Pour l’exemple donné, nous devons définir des contraintes de clé étrangère pour le champ FK_TITLE et le lier à la clé primaire ID_TITLE dans TITLE. Nous pouvons ajouter une clé étrangère à une table existante à l’aide de la commande suivante :

ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)

Fréquemment, lors de l’ajout d’une clé étrangère, l’erreur apparaît - object is in use. Le problème est que pour créer une clé étrangère, nous devons ouvrir une base de données en mode exclusif - afin qu’il n’y ait aucun autre utilisateur en même temps. Nous ne devons pas non plus faire référence à la table modifiée - cela peut provoquer l’erreur object is in use.

Ici, INVENTORY est le nom de la table pour laquelle la contrainte de clé étrangère est définie ; fktitle1 est un nom de la clé étrangère ; FK_TITLE - les champs constituant la clé étrangère ; TITLE est le nom de la table fournissant les valeurs (la base de référence) pour la clé étrangère ; ID_TITLE - les champs de la clé primaire ou unique dans la table TITLE, qui servent de base de référence pour la clé étrangère. Une syntaxe complète de la contrainte de clé étrangère (avec la possibilité de créer des contraintes basées sur plusieurs champs) est donnée ci-dessous :

= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Comme vous pouvez le voir, les définitions contiennent un grand ensemble d’options. Pour commencer, considérons une définition de base d’une clé étrangère, qui est la plus fréquemment utilisée dans les bases de données réelles, puis nous analyserons les options possibles.

Une forme déclarative d’une contrainte de clé étrangère est le plus souvent utilisée lorsqu’un ensemble de champs (col [, col …]), qui constituera la contrainte, est spécifié ; ainsi que l’autre_table qui contient une liste de valeurs possibles pour la clé étrangère dans les champs [(other_col [, other_col …])].

Voici l’exemple d’une telle définition lors de la création de la table :

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);

Notez que dans cette définition, les mots-clés FOREIGN KEY sont omis, ainsi que le seul champ FK_TITLE est implicitement utilisé comme clé étrangère. Une forme plus complète de création de la clé étrangère simultanément avec la table est donnée dans l’exemple suivant :

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);

Utilisation de NULL dans les champs d’une clé étrangère

Dans les champs, sur la base desquels une clé étrangère est créée, il est permis d’appliquer des champs NULL. Cette possibilité est ajoutée pour permettre les références mutuelles. Par exemple, s’il existe deux tables qui se référencent mutuellement à l’aide de clés étrangères. Si nous n’autorisons pas la référence vide (c’est-à-dire pour NULL) dans ces clés étrangères, il sera impossible d’ajouter un enregistrement aux tables jointes : pour ajouter un enregistrement à la première table, il est nécessaire d’avoir un enregistrement dans la deuxième table, et vice versa.

L’utilisation de NULL comme référence vide permet de créer des références mutuelles de deux tables se référençant croisées, et également de stocker des structures hiérarchiques dans des tables relationnelles - dans ce cas, les nœuds racines se réfèrent à des enregistrements « vides » (c’est-à-dire qu’ils contiennent simplement NULL).

Capacités étendues de prise en charge de l’intégrité référentielle à l’aide d’une clé étrangère

Habituellement, la variante déclarative d’une contrainte de clé étrangère est suffisante, le serveur surveille seulement qu’il sera impossible d’insérer des valeurs incorrectes dans la table avec la clé étrangère ou - en essayant de le faire, une erreur apparaît. Mais InterBase permet d’exécuter un ensemble d’opérations automatiques lors de la modification / suppression d’une clé étrangère. À cette fin, l’ensemble suivant d’options de clé étrangère est utilisé :

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Ces options permettent de définir différentes opérations lors de la mise à jour ou de la suppression des valeurs de clé étrangère.

Par exemple, nous pouvons définir que lors de la suppression d’une clé primaire dans la table maître, tous les enregistrements avec la même clé étrangère dans la table subordonnée doivent être supprimés. Dans ce cas, nous devons définir une clé étrangère de la manière suivante :

ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE

En réalité, pour l’implémentation de ces opérations, il existe un déclencheur système qui exécute certaines opérations. Dans le tableau 1.2, il y a une description des opérations de différentes options (notez que les options NO ACTION|CASCADE|SET DEFAULT|SET NULL ne peuvent pas être utilisées dans une même phrase ON XXX).

Tableau 1.2


Événement	Opération
NO ACTION	CASCADE	SET DEFAULT	SET NULL
ON DELETE	Lors de la suppression d’une clé étrangère, ne rien faire - utilisé par défaut	Lors de la suppression, supprimer tous les enregistrements liés de la table subordonnée	Lors de la modification, définir un champ de clé étrangère comme valeur par défaut	Lors de la modification, définir un champ de clé étrangère à NULL
ON UPDATE	Lors de la modification, ne rien faire - utilisé par défaut	Lors de la modification d’un enregistrement, modifier tous les enregistrements liés dans la table subordonnée	Lors de la suppression, définir un champ de clé étrangère comme valeur par défaut	Lors de la suppression, définir un champ de clé étrangère à NULL

Si nous ne spécifions rien ou spécifions NO ACTION, nous devons nous occuper nous-mêmes de la modification d’une clé étrangère (en cas de modification d’une clé primaire), et lors de la suppression de la clé primaire, nous devons supprimer les enregistrements de la table subordonnée au préalable. Soyez très attentif lors de l’utilisation de l’option CASCADE : son utilisation imprudente peut entraîner la suppression d’un grand nombre d’enregistrements liés.

Contrainte CHECK

L’une des contraintes les plus utiles dans une base de données est la contrainte de vérification (check constraint). Sa fonction est très simple - vérifier la valeur insérée dans la table pour toute condition et selon l’exécution de cette condition, insérer les données ou non. Sa syntaxe est assez simple :

= [CONSTRAINT constraint] CHECK ( )}

Ici, constraint est un nom de contrainte ; est une condition de recherche, dans laquelle la valeur insérée / mise à jour peut être utilisée comme paramètre. Si la condition de recherche est remplie, il est permis d’insérer / mettre à jour cette valeur, si elle ne l’est pas - une erreur apparaît. L’exemple le plus simple de check :

create table checktst( ID integer CHECK(ID>0));

Ce check détermine si la valeur insérée / mise à jour du champ ID est supérieure à zéro, et selon le résultat permet d’insérer / mettre à jour une nouvelle valeur ou d’informer de l’erreur (voir le chapitre « Capacités étendues du langage des procédures stockées InterBase » (partie 1)).

Il existe également des variantes plus compliquées de checks. Une syntaxe complète de la condition de recherche est la suivante :

= {

{ | ()}

| [NOT] BETWEEN AND

| [NOT] LIKE [ESCAPE ]

| [NOT] IN ( [ , …] | )

| IS [NOT] NULL

| {[NOT] {= | < | >} | >= | <=}

{ALL | SOME | ANY} ()

| EXISTS ( )

| SINGULAR ( )

| [NOT] CONTAINING

| [NOT] STARTING [WITH]

| ()

| NOT

| OR

| AND }

Ainsi, CHECK offre un grand ensemble d’options pour vérifier les valeurs insérées / mises à jour. Vous devez vous souvenir des contraintes suivantes lors de l’utilisation de CHECK :

Les données pour CHECK sont prises uniquement à partir d’un enregistrement courant. Vous ne devez pas prendre les données pour l’expression dans CHECK à partir d’autres enregistrements de la même table - ils peuvent être modifiés par d’autres utilisateurs
Un champ peut avoir une seule contrainte CHECK
Si pour la définition d’un champ, un domaine ayant une contrainte de domaine CHECK est utilisé, il ne peut pas être redéfini au niveau d’un champ concret dans la table. Il faut dire que CHECK est implémenté par des déclencheurs système, par conséquent nous devons être plus prudents lors de l’utilisation de conditions très longues, qui peuvent fortement ralentir les processus d’insertion et de mise à jour des enregistrements.

Suppression de contraintes

Très souvent, nous supprimons différentes contraintes pour les raisons les plus diverses. Afin de supprimer une contrainte, nous devons utiliser l’instruction ALTER TABLE de la forme suivante : ALTER TABLE tablename DROP CONSTRAINT constraintname

constraintname est un nom de la contrainte qui doit être supprimée. Si un certain nom a été spécifié lors de la création de la contrainte, nous devons l’utiliser, mais si ce n’est pas le cas, nous devons ouvrir n’importe quel outil d’administration InterBase, rechercher toutes les contraintes qui y sont liées et découvrir quel nom système InterBase a généré pour la contrainte requise.

Il convient de noter que seul le propriétaire de la table ou l’administrateur système SYSDBA peut supprimer les contraintes.