Aller au contenu principal

Typologie et usage des clés en bases de données relationnelles

Cette note est issue du cours IFT723 - Sujets approfondis en bases de données enseigné par Luc Lavoie à l'Université de Sherbrooke (Hiver 2025).

Clé candidate​

Définition

Une clé candidate est un ensemble de données permettant d'indexer chaque ligne d'une table donnée de manière différenciée.

Clés primaires et secondaires​

Clé primaire

Parmi les clés candidates d'une relation, une est désignée comme clé primaire ; les autres (s'il en est) sont désignées comme clés secondaires.

D'un point de vue strictement relationnel, ce concept est inutile. Cependant, le choix d'une clé candidate de référence (clé primaire) peut contribuer à la clarté et à la modifiabilité du schéma. En particulier, ce concept est utile lors de la définition de clés étrangères.

Critères de choix

Il n'existe pas de critères clairs pour le choix d'une clé primaire (parmi les clés candidates).

Ce choix peut se faire selon plusieurs critères :

  • Nombre d'attributs constitutifs
  • Taille totale
  • Flexibilité au moment des sélections
  • Flexibilité au moment des jointures

Représentation en SQL​

Clés primaires​

Utilisation du mot-clé PRIMARY KEY. Les attributs qui composent la clé sont automatiquement marqués NOT NULL.

Clés secondaires​

Utilisation du mot-clé UNIQUE. Les attributs associés ne sont pas automatiquement marqués NOT NULL.

Clés référentielles (ou étrangères)​

Définition

Une clé référentielle (ou clé étrangère, foreign key en anglais) est un ou plusieurs attributs dans une table qui font référence à la clé primaire (ou une clé candidate) d'une autre table.

Elle sert à établir et maintenir une relation logique entre deux tables, en assurant que la valeur d'un champ dans une table correspond à une valeur existante dans une autre table.

Clés relatives et absolues​

Clés relatives​

Définition

Une clé est dite relative si et seulement si :

  • au moins un sous-ensemble de ses attributs est une clé référentielle d'une autre relation ;
  • au moins un attribut lui est propre (c'est-à-dire ne participant à aucune clé référentielle).

Remarque : une clé relative comporte au moins deux attributs.

La représentation d'entités faibles produit naturellement des clés relatives.
La représentation générale des associations aussi, sauf lors des optimisations pour le cas 1-1 (fusion) et pour le cas 1-N (référentielle).

Clés absolues​

Définition

Une clé est dite absolue si et seulement si :

  • elle ne comporte qu'un seul attribut ;
  • cet attribut lui est propre.

Clés internes et externes​

Définition

Une clé interne est une clé absolue n'ayant aucune sémantique associée en regard du domaine d'application.

Remarque : une clé est dite externe si et seulement si elle n'est pas interne.

Les termes clés artificielles et clés naturelles peuvent être aussi utilisé.

Par définition, les clés artificielles n’ont pas vocation à être connues ni utilisées dans le monde réel. Ce sont des identifiants (uniques) ajoutés lors de la modélisation pour simplifier la dénotation d’un tuple dont les clés naturelles s’avèrent peu pratiques en regard de certains usages. On les ajoute parfois aussi parce que l’information caractérisant uniquement le tuple ne peut être obtenue dans le contexte du problème. Dans ce cas, on comprend qu’elles acquièrent une matérialité dans le monde réelle, car le besoin d’une identification unique s’y fera vraisemblablement ressentir aussi, ne serait-ce qu’en termes de l’utilisation du logiciel utilisant le modèle.

Intérêts des clés internes​

  • D'un point de vue strictement pratique, elle permet de distinguer des entrées qui ne possèdent pas de clé externe et ainsi éviter les amalgames accidentels (exemples : les animaux, les humains, les plantes...).

  • Elle permet aussi notamment d'éviter la propagation d'informations sensibles contenues dans les clés externes. Par exemple : le NAS (Numéro d'assurance sociale).

  • L'utilisation de clés internes permet aussi une meilleure évolutivité du schéma. En effet, l'unicité et l'immuabilité des clés externes sont rarement garanties à long terme.

  • Optimisation du temps de calcul en évitant d'avoir une clé primaire multi-attributs ou volumineuse.

Important

Ne jamais associer une signification à la valeur d'une clé interne, sinon elle n'est plus interne.

Exemple de clés externes​

Par définition, les codes ne sont pas des clés internes car leur création est associée à des règles externes. Par exemple :

  • Le matricule de la RAMQ (Régime d'assurance maladie du Québec) : référence au nom, prénom et date de naissance
  • Le numéro d'assurance sociale : référence au statut de résidence
  • Le CIP de l'Université de Sherbrooke : référence au nom et prénom
  • ...

Génération de clés internes​

Caractéristiques souhaitées​

  • Artificialité garantie lors de la génération
  • Unicité garantie lors de la génération
  • Unicité garantie dans un contexte transactionnel
  • Automatisation de la génération à l'insertion

Utilisation des UUID​

Il s'agit d'un identifiant unique de 128 bits utilisé pour identifier un objet de manière unique. Il est très pratique dans notre contexte pour identifier une entrée de table de manière unique. Référence

Exemple PostgreSQL​
create extension if not exists pgcrypto;
Remarque

Le générateur de UUID n'existe pas par défaut en PostgreSQL, il faut donc ajouter l'extension pgcrypto qui contient la méthode gen_random_uuid().

create table utilisateur (
    private_user_id UUID primary key default gen_random_uuid(),
    public_user_id UUID default gen_random_uuid() not null,
    username varchar not null unique,
    email varchar not null unique check (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    encrypted_password varchar not null
);