Partager via

Définir des relations entre les tables à l’aide d’Access SQL

Les relations correspondent aux associations établies entre deux ou plusieurs tables. Elles se fondent sur des champs communs de plusieurs tables et utilisent souvent des clés primaires ou étrangères.

Une clé primaire correspond au(x) champ(s) utilisé(s) pour identifier chaque enregistrement d'une table de manière unique. Elle doit satisfaire à trois exigences : elle ne peut être Null, elle doit être unique et vous ne pouvez en définir qu'une seule par table. Vous pouvez définir une clé primaire soit en créant un index de clé primaire une fois la table créée, soit en utilisant la clause CONSTRAINT dans la déclaration de table, comme indiqué dans les exemples ci-dessous. Une contrainte limite les valeurs entrées dans un champ.

Une clé étrangère correspond à un ou plusieurs champs d'une table qui renvoie à la clé primaire d'une autre table. Les données des champs des deux tables sont parfaitement identiques et la table contenant l'enregistrement de clé primaire (la table principale) doit avoir des enregistrements existants pour que la table avec l'enregistrement de clé étrangère (la table étrangère) contiennent les enregistrements correspondants ou associés. À l'instar des clés primaires, vous pouvez définir des clés étrangères dans la déclaration de table à l'aide de la clause CONSTRAINT.

Il existe trois types de relation :

  • Un-à-un Il n'existe qu'un et un seul enregistrement dans la table étrangère pour chaque enregistrement de la table principale.
  • Un-à-plusieurs Il existe un ou plusieurs enregistrements connexes dans la table étrangère pour chaque enregistrement de la table principale.
  • Plusieurs-à-plusieurs Il existe plusieurs enregistrements connexes dans la table étrangère pour chaque enregistrement de la table principale et il existe plusieurs enregistrements dans la table principale pour chaque enregistrement de la table étrangère.

Par exemple, supposons que vous voulez ajouter une table Factures à une base de données Facturation. Chaque client de la table Clients peut avoir plusieurs factures dans la table Factures (scénario classique un-à-plusieurs). Vous pouvez prendre la clé primaire de la table Clients et la définir en tant que clé étrangère dans la table Factures et établir ainsi la relation approprié entre les tables.

Lors de la définition des relations entre les tables, vous devez établir les déclarations CONSTRAINT au niveau du champ. Ce qui signifie que les contraintes sont définies au sein d'une instruction CREATE TABLE. Pour appliquer les contraintes, utilisez le mot clé CONSTRAINT après une déclaration de champ, nommez la contrainte et la table à laquelle elle fait référence et nommez le ou les champs de cette table qui constitueront la clé étrangère correspondante.

L'instruction suivante suppose que la table tblClients a déjà été créée et qu'elle a une clé primaire définie sur le champ RéfClient. L'instruction crée la table tblFactures, en définissant sa clé primaire sur le champ RéfFacture. Elle crée aussi la relation un-à-plusieurs entre les tables tblClients et tblFactures en définissant un autre champ RéfClient dans la table tblFactures. Ce champ est défini comme une clé étrangère qui fait référence au champ RéfClient dans la table des clients. Notez que le nom de chaque contrainte suit le mot clé CONSTRAINT.

CREATE TABLE tblInvoices  
    (InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY, 
    CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID  
        REFERENCES tblCustomers (CustomerID), 
    InvoiceDate DATETIME, 
    Amount CURRENCY)

Notez que l'index de clé primaire (PK_InvoiceID) de la table des factures est déclaré dans l'instruction CREATE TABLE. Pour améliorer les performances de la clé primaire, un index est automatiquement créé ; vous ne devez donc pas utiliser une instruction CREATE INDEX distincte. Créez maintenant une table d’expédition qui contiendra l’adresse d’expédition de chaque client. Supposons qu’il n’y aura qu’un seul enregistrement d’expédition pour chaque enregistrement client. Vous allez donc établir une relation un-à-un.

CREATE TABLE tblShipping  
    (CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY 
        REFERENCES tblCustomers (CustomerID), 
    Address TEXT(50), 
    City TEXT(50), 
    State TEXT(2), 
    Zip TEXT(10))

Notez que le champ RéfClient est à la fois la clé primaire de la table de livraison et la référence de clé étrangère à la table clients.

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és dans l’instruction CREATE TABLE ou 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. Utilisez la table des 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 attribué. 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. Supposons que vous souhaitiez restreindre (ou limiter) les valeurs des champs prénom et nom de famille 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 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 des 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

Notez 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.

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.