Tables. Clés primaires et générateurs
NOTICE: This document is the chapter from the book “The InterBase World” which was written by Alexey Kovyazin and Serg Vostrikov.
InterBase est un SGBD relationnel. Cela signifie en outre que toutes les données dans InterBase sont stockées sous forme de tables. La table, telle qu’elle est réalisée du point de vue SQL, est très similaire à la table ordinaire que l’on peut dessiner à la main sur une feuille de papier ou créer dans un programme comme Microsoft Excel. Les tables dans InterBase ont des colonnes et des lignes où les données sont placées. La table doit nécessairement avoir un nom, unique au sein d’une base de données. Les tables sont le principal support de stockage des informations dans une base de données, et par conséquent, vous devez être très prudent lors de la création de tables.
Il existe des règles décrivant comment créer des tables dans la base de données relationnelle, reflétant les données du monde réel et permettant en même temps d’organiser un stockage efficace des informations dans une base de données. Le processus d’application de ces règles pour concevoir une base de données « correcte » est appelé normalisation. Nous avons volontairement mis le mot « correcte » entre guillemets, car « base de données normalisée » et « base de données optimisée » ne sont pas des synonymes. Vous n’êtes pas obligé de vous conformer sans ambiguïté aux règles de normalisation - appliquez toujours la correction en fonction des spécifications du problème donné.
La normalisation des tables dans une base de données est examinée en détail dans le livre [14. et par conséquent, nous n’essaierons pas d’embrasser l’inembrassable et nous reviendrons à notre sujet de discussion - les tables InterBase. Considérons la syntaxe de la phrase DDL (DDL - Data Definition Language, pour plus de détails voir le glossaire) qui permet de créer des tables :
CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);
Ici, table est un nom de la table créée, - la description des colonnes (parfois nous dirons - champs) de la table créée. L’option table [EXTERNAL [FILE] “”] signifie que la soi-disant table externe, qui n’est pas stockée dans un fichier de base de données partagé, mais dans un fichier séparé avec un nom , sera créée. Comme vous pouvez le voir, tout est simple - nous définissons un nom de table et les colonnes qu’elle contient. Maintenant, nous examinerons en détail comment définir les colonnes. La syntaxe de création d’une colonne est décrite par la phrase DDL suivante :
= col { datatype | COMPUTED [BY] (< expr>)} | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[COLLATE collation]
C’est une définition assez grande, cependant dans la définition d’une colonne seule une petite partie des phrases données est obligatoire. Chaque colonne de la table doit avoir un nom, unique au sein de la table, ainsi qu’un type de données défini par la phrase datatype, ou une expression pour calculer la valeur d’une colonne (pour les colonnes calculées), ou le domaine (voir ci-dessous), défini par domain. Les types de données ont été examinés dans le chapitre « Types de données » ; par conséquent, vous pouvez facilement comprendre comment l’expression SQL pour créer une table est formée.
Connectons-nous à notre base de données FIRSTBASE.gdb créée précédemment dans le chapitre « Créer une base de données », et essayons de travailler avec les tables en pratique. En ce qui concerne la création, la suppression et la mise à jour de tables, n’importe quel outil administratif InterBase - parmi ceux listés dans l’application « Outils pour administrateurs et développeurs InterBase », ainsi que l’utilitaire standard isql.exe d’un ensemble de fourniture de tout clone InterBase conviendra.
Voici un exemple d’une table simple appelée TABLE_EXAMPLE et contenant 3 champs de divers types :
CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Cette table illustre le cas le plus fréquemment rencontré dans le processus de développement d’une base de données. Cependant, il existe également d’autres méthodes pour définir des champs. Par exemple, nous pouvons définir un type de champ en utilisant des domaines. Le domaine est un type défini par un utilisateur pour la commodité d’appliquer certaines combinaisons de paramètres de type. Par exemple, il est possible de définir le domaine D_ID pour spécifier des champs d’identifiants. Après avoir défini le domaine, nous pouvons l’utiliser pour définir le type d’un champ :
CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Le champ ID aura le type défini par le domaine D_ID. Ainsi, après avoir défini le type du champ dans le domaine, les vérifications et contraintes requises, nous pouvons appliquer ce domaine plusieurs fois pour créer des champs de même fonction. Par exemple, monétaire sans copie fastidieuse et dangereuse de définitions de types variables. La troisième façon de définir une colonne dans la table est de la définir comme calculée (COMPUTED BY) et de spécifier une condition selon laquelle sa valeur sera calculée. Par exemple, nous pouvons souhaiter avoir une colonne calculant 10 % de la valeur du champ PRICE_1 dans notre table. Dans ce cas, la commande suivante doit être écrite :
CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));
Mais ne pensez pas que dès que nous insérons les données dans le champ PRICE_1, le champ PRICE_10 contiendra un dixième de la valeur de ce champ. Non, le processus ici est plus compliqué. En réalité, nous obtiendrons un dixième requis seulement en référençant le champ PRICE_10, par exemple lors de l’exécution d’une requête SELECT sur cette table. C’est-à-dire qu’aucune donnée n’est stockée dans un champ calculé, et le calcul de l’expression liée à un champ est effectué, et le résultat est produit comme réponse à la requête.
Donc, nous avons examiné 3 façons principales de spécifier des champs dans la table. Maintenant, examinons en détail les options qui peuvent être définies lors de la création d’une colonne. L’option [DEFAULT {literal | NULL | USER}] - permet de définir une valeur de colonne par défaut. C’est très pratique pour le remplissage automatique des données. Il y a 3 façons de définir une valeur par défaut. La première est désignée comme literal et permet de définir des valeurs par défaut comme des constantes de texte, des nombres ou des dates. Par exemple, nous pouvons générer les expressions suivantes pour créer une colonne avec des valeurs par défaut de texte : NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’
Ainsi, tous les champs insérés dans la table prendront les valeurs par défaut, c’est-à-dire que si une autre valeur n’a pas été définie pour le champ NAME, la chaîne ‘Vasily Stanislavovich’ apparaîtra. La deuxième façon de définir une valeur par défaut est de spécifier DEFAULT NULL dans la définition d’une colonne. Et dans les enregistrements créés à nouveau, la valeur de cette colonne sera NULL, si une autre valeur, bien sûr, n’a pas été explicitement définie. Un exemple :
PRICE_1 DOUBLE PRECISION DEFAULT NULL
La troisième façon de définir une valeur par défaut est de spécifier DEFAULT USER dans la définition d’une colonne. Ainsi, dans les enregistrements créés à nouveau, ce champ contiendra le nom de l’utilisateur actuel, c’est-à-dire l’utilisateur qui a établi une connexion avec InterBase et exécuté cette insertion (pour plus de détails sur les utilisateurs, voir le chapitre « Sécurité dans InterBase : utilisateurs, leurs fonctions et droits » (partie 4)). Pour certains champs, il est essentiel que le champ ait une valeur non vide. Par exemple, un champ qui ne peut pas être vide selon les spécifications du problème. Afin de définir une contrainte au niveau de la base de données selon laquelle un champ doit avoir une valeur définie, il est nécessaire d’ajouter ce qui suit à la description d’une colonne :
NAME VARCHAR(80) NOT NULL
Ainsi, il y aura un champ dans lequel des valeurs nulles ne peuvent pas être stockées. Habituellement, la contrainte NOT NULL est combinée avec l’option DEFAULT qui attribue définitivement une valeur correcte à ce champ. Mais souvent, la contrainte NOT NULL ne suffit pas. Par exemple, dans le cas du stockage de prix dans une base de données, il est assez clair qu’ils ne peuvent pas prendre des valeurs négatives (bien que ce serait génial si nous étions payés en plus lors de l’achat de biens). Afin de faire vérifier par le serveur les valeurs des prix insérés dans une base de données sur une condition de positivité, il est nécessaire de définir une colonne de la manière suivante :
PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)
Les valeurs insérées dans la colonne PRICE_1 seront vérifiées sur une condition de positivité. Il convient de noter que différentes options cohérentes peuvent être combinées, et par exemple, nous pouvons définir une valeur non vide et une vérification de positivité :
PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)
Lors de la création des colonnes, certaines options ne peuvent pas être combinées, par exemple il est impossible de définir NULL par défaut et simultanément une contrainte de valeur non vide. Il convient de noter que les vérifications peuvent effectuer un ensemble de fonctions utiles sur la gestion des données dans une base de données. Nous examinerons leur utilisation en détail dans le chapitre « Contraintes de base de données ».
Donc, nous avons examiné les façons de créer des tables et des champs avec différentes options. Cependant, il y a des cas où nous devons modifier une table qui existe déjà. Certes, nous pouvons recréer la table entièrement. D’abord, nous devons exécuter la commande de suppression de la table puis la recréer. Par exemple :
DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);
Mais une telle façon de modifier des tables a des inconvénients significatifs. Lors de la suppression d’une table avec la commande DROP, toutes les données que la table contient sont supprimées et afin de ne pas les perdre, il est nécessaire de les copier dans des tables temporaires. C’est assez pénible. Par conséquent, il existe la commande ALTER TABLE pour une modification facile de la structure des tables qui permet d’ajouter de nouveaux champs, de supprimer des champs existants, ainsi que d’ajouter / supprimer des contraintes d’intégrité référentielle.
Par exemple, nous voulons ajouter une autre colonne à la table destinée à stocker les données sur le patronyme d’une personne :
ALTER TABLE Table_example ADD Patronimic VARCHAR(80);
Après l’exécution de cette commande, notre table Table_example aura une nouvelle colonne avec le nom Patronimic et le type VARCHAR (80). Si nous voulons supprimer une colonne avec le nom NAME de la table, nous devons exécuter ce qui suit :
ALTER TABLE Table_example DROP Name;
Vous pouvez voir la syntaxe complète de l’instruction ALTER TABLE dans [1.. C’est une commande très utile, et nous l’utiliserons souvent.
Et que faire, demanderez-vous, s’il est nécessaire de modifier une colonne ? Par exemple, nous avons décidé que pour stocker des noms, il est préférable d’utiliser le champ HUMAN_NAME plutôt que NAME. Dans ce cas, nous pouvons appliquer ALTER TABLE
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Si nous avons décidé de modifier le type d’un champ, par exemple pour augmenter le nombre de caractères stockés dans un champ, nous devrons changer le domaine de ce champ en utilisant l’instruction ALTER DOMAIN (voir le chapitre « Types de données » ci-dessus).
Donc, nous avons examiné la création et la modification de tables dans InterBase. Maintenant, il est temps d’approfondir un peu la théorie des bases de données. InterBase, comme cela a déjà été dit, est une base de données relationnelle. Cela signifie en outre que chaque enregistrement dans la table doit avoir une caractéristique selon laquelle un enregistrement peut être distingué d’un autre. Le mécanisme spécial des clés uniques sert à cet effet.
Clés primaires dans les tables
Certes, nous pouvons créer une table qui ne contient aucune clé. Cela ne nous est pas interdit. Mais, comme cela a été dit précédemment, la création d’une base de données efficace est impossible sans respecter les règles de normalisation. La présence de clés est l’élément le plus important de la normalisation. Par conséquent, bien que nous ne visions pas à examiner la théorie et la normalisation des bases de données, nous devons introduire une définition des clés et examiner leur fonction dans InterBase. Nous irons pas à pas et nous commencerons par le type de clé le plus courant - une clé primaire.
Alors, qu’est-ce qu’une clé primaire ? C’est un ou plusieurs champs dans la table, identifiant de manière unique les enregistrements au sein de cette table. Cela semble difficile, mais en réalité tout est très simple. Imaginez une table ordinaire, par exemple la feuille de comptabilité. Quelle est la toute première colonne ? C’est exact, le numéro de série - 1, 2, 3 … Ce numéro désigne une ligne unique dans la table, et il suffit de connaître ce numéro pour trouver une chaîne dans cette table. Dans cet exemple, ce sera une clé primaire. La grande majorité des tables dans une base de données relationnelle a nécessairement une clé primaire (PK - abréviation de Primary key). La directive courante lors de la création des tables est de créer une clé primaire. Une clé primaire peut être créée lors de la création d’une table ou plus tard. Supposons qu’au moment de la création d’une table, nous ayons décidé que le champ ID sera notre clé primaire. Alors nous pouvons ajouter une clé primaire de la manière suivante :
CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));
Qu’a-t-il fallu faire pour créer une clé primaire pour la table table_example ? Cherchons ce qui a varié dans la définition de la table ? Tout d’abord, la colonne ID a reçu une définition supplémentaire NOT NULL. C’est important, car la clé primaire doit être unique et ne doit pas contenir de valeurs indéfinies. Et NULL, comme vous le savez, est une valeur indéfinie. Ainsi, tous les champs inclus dans une clé primaire doivent avoir la contrainte NOT NULL. Pour compléter la création d’une clé primaire, il faut écrire à la fin de la table : CONSTRAINT ()
Vous pouvez trouver la syntaxe complète des contraintes dans le chapitre « Contraintes de base de données » þ. 1, et pour notre exemple de clé primaire, cela ressemblera à :
CONSTRAINT pkTable PRIMARY KEY (ID)
Ici, pkTable est le nom de la clé primaire, et ID sont les colonnes qu’elle contient. Cette façon de définir les clés primaires pour les tables est pratique lors de la création en masse de tables (par exemple, lors de la construction du prototype d’une base de données sur la base de scripts obtenus par différents outils CASE). Mais que faire si nous devons ajouter / supprimer une clé primaire à une table qui existe déjà et qui est remplie de données ? Pour cela, une autre extension de la commande - ALTER TABLE - doit être appliquée. Un exemple d’ajout d’une clé primaire à notre table :
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);
Ainsi, la table Table_example aura exactement la même clé primaire que dans l’exemple précédent lorsqu’elle a été créée avec la table. Pour supprimer une clé primaire, la commande suivante doit être saisie :
ALTER TABLE Table_example DROP CONSTRAINT pkTable;
Ainsi, la clé nommée pkTable sera supprimée de la base de données.
Générateurs - meilleurs amis des clés primaires
Nous devons dire quelques mots sur l’implémentation d’une clé primaire. Comme elle est destinée à garantir l’unicité, deux enregistrements dans une même table ne peuvent pas avoir les mêmes valeurs pour cette clé. C’est-à-dire que, pour satisfaire cette condition, lors de l’insertion d’un nouvel enregistrement dans la table, InterBase doit vérifier tous les enregistrements de la table et déterminer si la table contient de telles valeurs ou non. Pour une recherche rapide, InterBase dispose d’un mécanisme d’index - des objets spéciaux d’InterBase qui permettent de trouver un enregistrement dans la table très rapidement. Par conséquent, lors de la création et de la suppression de la clé primaire, un index est créé ou supprimé pour ce champ (ou ces champs) inclus dans la clé primaire.
Comme il a été dit précédemment, la clé primaire peut contenir plusieurs champs. Ainsi, nous pouvons remarquer l’unicité d’une combinaison de valeurs de ces champs. Par exemple, si nous définissons une clé pour les champs ID et NAME, le serveur contrôlera qu’il n’y a pas de combinaisons identiques de ces champs dans la table. C’est-à-dire que les combinaisons des champs ID et 1 et « Ivanov », 2 et « Ivanov » seront correctes car elles diffèrent par les valeurs du champ ID.
Ainsi, la clé primaire peut inclure plusieurs champs de n’importe quel type. Cependant, en pratique, le type de clé le plus courant est le compteur - un champ entier qui contient des valeurs croissantes. Pourquoi en est-il ainsi ? C’est le reflet d’un vieux débat entre les clés naturelles et les clés de substitution. Le concept des clés naturelles dit que nous devrions essayer d’utiliser comme clé les valeurs réellement existantes dans le domaine de données que la base de données reflète. Par exemple, si nous développons un système d’enregistrement des personnes pour un bureau des passeports, selon ce concept, une combinaison du numéro et de la série du passeport devrait être prise comme clé primaire. En effet, chaque personne doit avoir une combinaison unique de numéro et de série de passeport. Cependant, que faire du fait qu’une personne peut changer de passeport au cours de sa vie (en raison de l’atteinte d’un âge défini, du mariage, etc.) ? Dans ce cas, nous devrons changer le numéro et la série du passeport associés à la personne concernée, c’est-à-dire, en réalité, changer notre clé primaire. Cela est indésirable du point de vue du développement d’applications de bases de données : en tenant compte d’un système ramifié de communication entre les tables (le chapitre suivant y est consacré), le développeur devra faire de grands efforts pour contrôler cette situation.
Par conséquent, une clé de substitution est utilisée dans la plupart des cas. Substitut - signifie artificiel, c’est-à-dire n’existant pas dans le domaine de données que notre base de données décrit, et créé artificiellement - pour la commodité du développement d’applications de bases de données. Comme il a été dit, généralement un compteur est une clé primaire. Certains SGBD, tels que Paradox et MS SQL, ont un type spécial - le compteur (auto-incrément). Lors de l’ajout d’un nouvel enregistrement à la table, la valeur du champ augmente automatiquement avec ce type de la valeur d’un incrément - généralement par unités. Dans InterBase, il n’y a pas de champ de type compteur, mais ce comportement peut être réalisé. Pour créer le champ qui serait automatiquement rempli lors de l’ajout d’un enregistrement à la table, une collection de ressources est utilisée : le premier d’entre eux est le générateur.
Qu’est-ce qu’un générateur ? En termes simples, un générateur est un compteur nommé. Dans une base de données, nous pouvons créer un compteur, lui donner un nom unique dans cette base et contrôler les valeurs de ce compteur. Ce sera un générateur. Voici un exemple d’instructions DDL qui vous l’expliqueront :
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
Dans la première ligne de cet exemple, un générateur nommé g1 est créé, et dans la deuxième ligne, la valeur 2445 est assignée à ce générateur. Maintenant, la question est de savoir comment utiliser le générateur obtenu. Il existe une fonction intégrée GEN_ID dans InterBase pour obtenir et modifier les valeurs des générateurs. Cette fonction prend comme paramètres le nom du générateur et la valeur de l’incrément, qui doit être appliquée au générateur donné, et retourne la valeur entière correspondant à la valeur du générateur, obtenue en ajoutant l’incrément à celle-ci. Voici un exemple d’appel de la fonction GEN_ID dans un déclencheur ou une procédure stockée :
Current_value = GEN_ID (g1, 1)
Si nous voulons recevoir la valeur du générateur, nous pouvons utiliser la requête suivante :
SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE
Comme la table RDB $ Database contient toujours un seul enregistrement, nous recevrons une valeur du générateur g1 comme résultat de la requête donnée.
Ici, current_value est une variable (dans les chapitres suivants, vous trouverez des informations sur la façon d’utiliser les variables dans InterBase), g1 - un générateur, 1 - un incrément. Dans cet exemple, la valeur du générateur g1 sera transférée à la variable current_value après l’ajout de l’incrément 1, c’est-à-dire la valeur suivante du générateur. Notez que l’incrément peut ne pas être égal à 1 ! De plus, il peut même être négatif : Current_value = GEN_ID (g1, -23)
En exécutant cette fonction, la valeur actuelle du générateur g1 sera moins 23. Comme vous pouvez le voir, la gamme d’applications possibles des générateurs est assez large - elle peut être utilisée non seulement pour obtenir des valeurs de clés primaires, mais aussi pour surveiller les changements globaux dans une base de données.
Les personnes familières avec les bases de données peuvent poser une question : « que se passera-t-il si plusieurs clients essaient simultanément de mettre des données dans la même table et « tirent » simultanément les générateurs ? Recevront-ils les mêmes ou différentes valeurs du générateur ? ». Ils recevront sans ambiguïté des valeurs DIFFÉRENTES du générateur. Quelle que soit la « simultanéité » de la tentative d’obtenir la valeur du générateur, chaque demandeur recevra une valeur unique. Cela est garanti par la « construction » des générateurs : ils fonctionnent au niveau le plus bas du serveur et aucun processus d’enregistrement ou d’insertion ne les influence - on dit souvent que les générateurs fonctionnent « hors du contexte des transactions ». Si vous voulez en savoir plus sur les transactions, lisez le chapitre « Transactions. Paramètres des transactions » (partie 1) ; comment les générateurs sont organisés - « Structure de la base de données InterBase » (partie 4). Eh bien, du côté des générateurs, nous avons un mécanisme fiable pour créer des clés primaires uniques. Cependant, pouvons-nous utiliser ce mécanisme ? Comment mettre la valeur reçue du générateur dans un champ de la clé primaire ?
Pour cela, il y a deux façons - insérer une clé primaire au nom du client et au nom du serveur. Pour maîtriser la première façon, nous devons nous référer au chapitre « Utilisation des composants principaux de FIBPlus » et pour comprendre la seconde - au chapitre « Déclencheurs » (partie 1). Ici, nous examinerons brièvement le point principal des deux façons.
Dans le cas de la création d’une clé primaire au nom du client, ce qui suit se produit. Lorsque l’enregistrement qui sera inséré dans une base de données est généré, l’appel de la fonction GEN_ID (, 1) est exécuté et la valeur reçue est substituée à cet enregistrement. Ensuite, l’insertion dans la table a lieu, et nous sommes garantis de recevoir une clé primaire unique.
La deuxième façon - créer une clé primaire au nom du serveur - élimine en général tout souci du côté du client concernant la valeur de la clé primaire. Dans ce cas, lors de l’insertion de l’enregistrement, le déclencheur fonctionne - un objet spécial de la base de données, qui peut effectuer toutes les opérations lors de l’insertion / suppression / mise à jour des enregistrements dans les tables. Et dans ce déclencheur, les opérations suivantes sont effectuées : appel de la fonction GEN_ID, réception de la valeur requise du générateur et son insertion dans la table. L’avantage de la deuxième façon est que lors du développement d’une application cliente, il n’y a pas besoin de se soucier de la création d’une clé primaire du tout, la seule chose à faire est d’écrire un déclencheur nécessaire une fois. Mais l’inconvénient est que nous ne pouvons pas recevoir la valeur de la clé générée dans l’application juste après l’insertion ! Si nous utilisons la première façon, nous pouvons recevoir la valeur de la clé primaire, bien que nous devions nous soucier de sa création à chaque insertion. Il est difficile de dire avec certitude quelle façon est meilleure, tout dépend du problème spécifique. Plus loin dans ce livre, nous examinerons les variantes possibles pour résoudre les questions concernant le travail avec une clé primaire.
Conclusion
Ainsi, dans ce chapitre, nous avons examiné comment créer et mettre à jour des tables dans InterBase, ainsi que gérer les clés primaires. Ainsi, nous avons considéré les principaux objets d’InterBase qui peuvent être conditionnellement appelés statiques, car ils stockent uniquement les informations et n’effectuent pas leur conversion. Ensuite, nous parlerons des moyens de contrôle des informations et de conversion des informations dans une base de données.