Rappels et concepts de base

Une clef primaire sert à identifier une ligne d'une table de façon unique. Dans un SGBDR la clef primaire est identique à une contrainte d'unicité et une contrainte NOT NULL, composée de une ou plusieurs colonnes.

Exemple en SQL  :

CREATE TABLE exemple (a integer, b integer, c integer, PRIMARY KEY(a,b)) ;

Une clé primaire indique qu'une colonne ou un groupe de colonnes (dans l'exemple ci-dessus a et b) peut être utilisé(e) comme identifiant unique des lignes de la table. L'ajout d'une clé primaire créera automatiquement un index unique sur la colonne ou le groupe de colonnes utilisé dans la clé primaire. Une table a, au plus, une clé primaire.

Le choix d'une clé primaire est une étape importante dans la conception d'une table. Il peut être nécessaire d'ajouter une colonne qui soit auto-incrémentée.

Si on considère pour les besoins de l'exemple, que l'on souhaite pouvoir gérer un propriétaire d'un véhicule même s'il ne possède pas de permis, alors le numéro de permis ne peut être la clef primaire. On va donc définir une nouvelle colonne id qui sera incrémentée automatiquement.

(On pourra consulter en complément cet extrait de la formation de Stéphane Crozat sur les clefs artificielles et les clefs signifiantes)

Le SQL pour créer la table s'écrira  :

CREATE TABLE proprietaire (

id SMALLINT AUTO_INCREMENT,

numeropermis VARCHAR(20),

nom VARCHAR(30) NOT NULL,

prenom VARCHAR(30),

PRIMARY KEY (id)

)

Nous verrons qu'il est possible de réaliser les requêtes SQL à l'aide des assistants sous PgAdminIII et vérifier la syntaxe SQL dans le dernier onglet de l'assistant :

on notera que smallserial et smallint AUTO_INCREMENT sont équivalent, de même que VARCHAR et character varying.

Une contrainte de clé étrangère stipule que les valeurs d'une colonne (ou d'un groupe de colonnes) doivent correspondre aux valeurs qui apparaissent dans les lignes d'une autre table. On dit que cela maintient l'intégrité référentielle entre les deux tables.

Si nous reprenons notre exemple Propriétaire / véhicule.

Si on considère qu'un véhicule doit toujours avoir un propriétaire préalablement existant, on peut déclarer :

CREATE TABLE vehicule (

numeroserie VARCHAR(20) PRIMARY KEY,

type VARCHAR(20),

marque VARCHAR(20),

id_proprietaire SMALLINT REFERENCE proprietaire(id)

)

Lors de la saisie d'un nouveau véhicule, le système vérifiera que l'id saisie dans id_proprietaire existe bien dans la table proprietaire.

Une table peut contenir plusieurs contraintes de clé étrangère. Les relations n-m entre tables sont implantées ainsi (voir par exemple l'extrait de la formation de Stéphane Crozat à partir d'ici).

voir également la documentation de PostgreSQL.