Modifier la conception d’une table à l’aide d’Access SQL

  • Article

Après avoir créé et rempli une table, vous devrez peut-être modifier la conception de la table. Pour ce faire, utilisez l’instruction ALTER TABLE . N'oubliez toutefois pas que la modification de la structure d'une table existante peut entraîner la perte de certaines données. Par exemple, la modification du type de données d'un champ peut engendrer la perte de données ou générer des erreurs d'arrondi, selon les types de données utilisés. Cette modification peut également briser d'autres parties de l'application qui font référence au champ modifié. Vous devez toujours faire preuve de prudence avant de modifier la structure d'une table existante.

L'instruction ALTER TABLE vous permet d'ajouter, de supprimer ou de modifier une colonne (ou un champ) mais aussi d'ajouter ou de supprimer une contrainte. Vous pouvez également déclarer une valeur par défaut pour un champ, mais vous ne pouvez modifier qu'un seul champ à la fois. Supposons que vous possédez une base de données de facturation et que vous voulez ajouter un champ à la table Clients. Pour ajouter un champ avec l'instruction ALTER TABLE, utilisez la clause ADD COLUMN en spécifiant le nom du champ, son type de données et la taille du type de données, le cas échéant.

ALTER TABLE tblCustomers 
   ADD COLUMN Address TEXT(30)

Pour modifier le type de données ou la taille d'un champ, utilisez la clause ALTER COLUMN en indiquant le nom du champ, le type de données souhaité et la taille voulue du type de données, le cas échéant.

ALTER TABLE tblCustomers 
   ALTER COLUMN Address TEXT(40)

Si vous souhaitez modifier le nom d’un champ, vous devez supprimer le champ, puis le recréer. Pour supprimer un champ, utilisez la clause DROP COLUMN uniquement avec le nom du champ.

ALTER TABLE tblCustomers 
   DROP COLUMN Address

Notez que cette méthode supprime les données existantes dans le champ. Pour conserver ces données, vous devez modifier le nom du champ en ouvrant la table en mode Création dans l'interface utilisateur d'Access ou écrire un code pour conserver les données actuelles dans une table temporaire et les rajouter à la table renommée. La valeur par défaut correspond à la valeur entrée dans un champ lorsqu'un nouvel enregistrement est ajouté à une table et qu'aucune valeur n'est spécifiée pour cette colonne. Pour définir une valeur par défaut pour un champ, utilisez le mot clé DEFAULT après avoir déclaré le type de champ dans une clause ADD COLUMN ou ALTER COLUMN.

ALTER TABLE tblCustomers 
   ALTER COLUMN Address TEXT(40) DEFAULT Unknown

Notez que la valeur par défaut n'est pas encadrée de guillemets simples. Si c'est le cas, les guillemets seront eux aussi insérés dans l'enregistrement. Le mot clé DEFAULT peut également être utilisé dans une instruction CREATE TABLE .

CREATE TABLE tblCustomers ( 
   CustomerID INTEGER CONSTRAINT PK_tblCustomers 
      PRIMARY KEY, 
   [Last Name] TEXT(50) NOT NULL, 
   [First Name] TEXT(50) NOT NULL, 
   Phone TEXT(10), 
   Email TEXT(50), 
   Address TEXT(40) DEFAULT Unknown)

Remarque

Vous ne pouvez exécuter l'instruction DEFAULT qu'au moyen du fournisseur Access OLE DB et d'ADO. Elle renverra un message d'erreur si vous utilisez l'interface utilisateur Access SQL View.

Contraintes

Vous pouvez utiliser les contraintes pour établir des clés primaires et une intégrité référentielle et restreindre les valeurs pouvant être entrées dans un champ. En général, les contraintes sont utilisées pour préserver l'intégrité et la cohérence des informations d'une base de données.

Il existe deux types de contrainte : les contraintes à champ unique ou de niveau champ et les contraintes multi-champs ou de niveau table. Les deux types de contraintes peuvent être utilisées dans les instructions CREATE TABLE et ALTER TABLE.

Une contrainte à champ unique, également appelée contrainte de niveau colonne, se déclare à l'aide du champ lui-même, une fois que le type de champ et de données ont été déclarés. Pour cet exemple, utilisez la table Clients et créez une clé primaire à champ unique sur le champ RéfClient. Pour ajouter la contrainte, utilisez le mot clé CONSTRAINT avec le nom du champ.

ALTER TABLE tblCustomers 
   ALTER COLUMN CustomerID INTEGER 
   CONSTRAINT PK_tblCustomers PRIMARY KEY

Notez que le nom de la contrainte est donné. Vous pouvez utiliser un raccourci pour déclarer la clé primaire qui omet entièrement la clause CONSTRAINT.

ALTER TABLE tblCustomers 
   ALTER COLUMN CustomerID INTEGER PRIMARY KEY

Toutefois, si vous utilisez la méthode du raccourci, Access générera de manière aléatoire un nom pour la contrainte et vous aurez donc des difficultés à le référencer dans le code. Il est judicieux de toujours nommer vos contraintes.

Pour supprimer une contrainte, utilisez la clause DROP CONSTRAINT avec l'instruction ALTER TABLE et spécifiez le nom de la contrainte.

ALTER TABLE tblCustomers 
   DROP CONSTRAINT PK_tblCustomers

Les contraintes peuvent également être utilisées pour restreindre les valeurs autorisées pour un champ. Vous pouvez limiter les valeurs à NOT NULL ou UNIQUE, ou vous pouvez définir une contrainte de vérification, qui est un type de règle métier qui peut être appliqué à un champ. Imaginez que vous souhaitez restreindre (ou restreindre) les valeurs des champs prénom et nom pour qu’elles soient uniques, ce qui signifie qu’il ne doit jamais y avoir une combinaison de prénom et de nom identique pour deux enregistrements dans la table. Étant donné qu’il s’agit d’une contrainte à plusieurs champs, elle est déclarée au niveau de la table, et non au niveau du champ. Utilisez la clause ADD CONSTRAINT et définissez une liste à plusieurs champs.

ALTER TABLE tblCustomers 
   ADD CONSTRAINT CustomerID UNIQUE 
   ([Last Name], [First Name])

Une contrainte CHECK est une fonctionnalité SQL puissante qui vous permet d'ajouter une validation de données à une table en créant une expression qui peut renvoyer à un ou plusieurs champs dans une ou plusieurs tables. Supposons que vous voulez vous assurer que les montants entrés dans un enregistrement de facture sont toujours supérieurs à 0,00 $. Pour cela, utilisez une contrainte CHECK en déclarant le mot clé CHECK et votre expression de validation dans la clause ADD CONSTRAINT d'une instruction ALTER TABLE.

ALTER TABLE tblInvoices 
   ADD CONSTRAINT CheckAmount 
   CHECK (Amount > 0)

L’expression utilisée pour définir une contrainte de vérification peut également faire référence à plusieurs champs de la même table ou à des champs d’autres tables, et peut utiliser toutes les opérations valides dans Microsoft Access SQL, telles que les instructions SELECT , les opérateurs mathématiques et les fonctions d’agrégation. L'expression qui définit la contrainte CHECK ne peut pas compter plus de 64 caractères.

Supposons que vous voulez vérifier la limite de crédit de chaque client avant qu'il ne soit ajouté à la table Clients. À l'aide d'une instruction ALTER TABLE et des clauses ADD COLUMN et CONSTRAINT, créez une contrainte qui cherchera la valeur dans la table LimiteCrédit afin de vérifier la limite de crédit du client. Utilisez les instructions SQL suivantes pour créer la table tblLimiteCrédit, ajoutez le champ LimiteClient à la table tblClients, ajoutez la contrainte CHECK à la table tblClients et testez la contrainte CHECK.

CREATE TABLE tblCreditLimit ( 
   Limit DOUBLE) 
 
INSERT INTO tblCreditLimit 
   VALUES (100) 
 
ALTER TABLE tblCustomers 
   ADD COLUMN CustomerLimit DOUBLE 
 
ALTER TABLE tblCustomers 
   ADD CONSTRAINT LimitRule 
   CHECK (CustomerLimit <= (SELECT Limit 
      FROM tblCreditLimit)) 
 
UPDATE TABLE tblCustomers 
   SET CustomerLimit = 200 
   WHERE CustomerID = 1

N’oubliez pas que lorsque vous exécutez l’instruction UPDATE TABLE , vous recevez un message indiquant que la mise à jour n’a pas réussi car elle a violé la contrainte de vérification. Si vous mettez à jour le champ CustomerLimit avec une valeur égale ou inférieure à 100, l’opération réussira.

Mises à jour et suppressions en cascade

Vous pouvez également utiliser des contraintes pour établir une intégrité référentielle entre des tables de bases de données. L'intégrité référentielle signifie que les données sont cohérentes et ne sont pas endommagées. Par exemple, si vous avez supprimé un enregistrement client mais que l'enregistrement livraison de ce client est resté dans la base de données, les données sont incohérentes car la table Livraison contient à présent un enregistrement orphelin. L'intégrité référentielle est établie lorsque vous créez une relation entre des tables.

En plus de créer une intégrité référentielle, vous pouvez également veiller à ce que les enregistrements des tables référencées restent synchronisés en utilisant des mises à jour et suppressions en cascade. Par exemple, lorsque les mises à jour et suppressions en cascade sont déclarées, si vous supprimez l'enregistrement client, l'enregistrement livraison de ce client est supprimé automatiquement.

Pour activer les mises à jour et suppressions en cascade, utilisez les mots clés ON UPDATE CASCADE et/ou ON DELETE CASCADE dans la clause CONSTRAINT d’une instruction ALTER TABLE. Notez qu’ils doivent s’appliquer à la clé étrangère.

ALTER TABLE tblShipping 
   ADD CONSTRAINT FK_tblShipping 
   FOREIGN KEY (CustomerID) REFERENCES 
      tblCustomers (CustomerID) 
   ON UPDATE CASCADE 
   ON DELETE CASCADE

Assistance et commentaires

Avez-vous des questions ou des commentaires sur Office VBA ou sur cette documentation ? Consultez la rubrique concernant l’assistance pour Office VBA et l’envoi de commentaires afin d’obtenir des instructions pour recevoir une assistance et envoyer vos commentaires.