Créer les tables

Solution

1. Clic droit sur Tables → Refresh. Les deux tables apparaissent.

2. pgAdmin affiche integer, character varying(50), date. Remarquez que VARCHAR est le nom court de character varying : les deux écritures sont équivalentes.

3. CURRENT_DATE est la date du jour, calculée par PostgreSQL au moment de l'insertion.

Combiné à DEFAULT, cela veut dire : si on ne précise pas de date d'inscription, mets celle d'aujourd'hui. C'est exactement ce qu'on veut pour une inscription en bibliothèque.

À tester : essayez d'insérer un auteur sans nom.

INSERT INTO auteurs (prenom) VALUES ('Émile');

ERROR: null value in column "nom" violates not-null constraint

La contrainte NOT NULL a fait son travail.

Solution

1. Les quatre problèmes

• Aucune clé primaire. Rien n'empêche deux membres d'avoir le même numéro, ni de laisser le numéro vide.
• Le numéro n'est pas auto-incrémenté. Il faudrait le gérer à la main, avec un risque de doublon.
• Le nom peut être vide. Un membre sans nom n'a aucun sens.
• L'email n'est pas unique. Deux membres pourraient partager la même adresse, ce qui empêcherait de s'en servir pour les retrouver.

2 et 3. La version corrigée

CREATE TABLE membres (
    num_membre  INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom         VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    age         INTEGER CHECK (age >= 0 AND age < 130),
    cotisation  NUMERIC(6,2) DEFAULT 0 CHECK (cotisation >= 0)
);

Ce qui a été ajouté :

• PRIMARY KEY GENERATED ALWAYS AS IDENTITY sur le numéro ;
• NOT NULL sur le nom et l'email ;
• UNIQUE sur l'email ;
• un CHECK sur l'âge, pour écarter les saisies absurdes ;
• un CHECK sur la cotisation, plus un DEFAULT 0.

Remarque sur l'âge : stocker un âge est en réalité une mauvaise idée — il change tous les ans. On stocke une date_naissance et on calcule l'âge quand on en a besoin. C'est une erreur de conception classique.

Solution

Exemple avec le cabinet médical.

DROP TABLE IF EXISTS consultations;
DROP TABLE IF EXISTS medecins;
DROP TABLE IF EXISTS patients;

CREATE TABLE patients (
    num_patient     INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom             VARCHAR(50) NOT NULL,
    prenom          VARCHAR(50) NOT NULL,
    date_naissance  DATE NOT NULL,
    telephone       VARCHAR(20),
    numero_national VARCHAR(15) UNIQUE
);

CREATE TABLE medecins (
    num_medecin   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    nom           VARCHAR(50) NOT NULL,
    prenom        VARCHAR(50),
    specialite    VARCHAR(60) NOT NULL,
    numero_inami  VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE consultations (
    num_consultation INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    date_consult     DATE NOT NULL DEFAULT CURRENT_DATE,
    heure            TIME,
    motif            TEXT,
    montant          NUMERIC(6,2) CHECK (montant >= 0),
    num_patient      INTEGER,
    num_medecin      INTEGER
);

Réponse à la question 4 : oui, il doit fonctionner les deux fois.

C'est tout l'intérêt du DROP TABLE IF EXISTS. Sans lui, la deuxième exécution échouerait avec relation already exists.

Attention à l'ordre des DROP. Ici consultations vient en premier parce qu'elle dépendra des deux autres dès la fiche 7. Créez toujours vos scripts avec les DROP dans l'ordre inverse des CREATE, même avant d'avoir ajouté les clés étrangères : vous n'aurez pas à y revenir.

Un point à noter : les colonnes num_patient et num_medecin existent déjà, mais ce ne sont pour l'instant que de simples entiers. Rien n'empêche encore d'y mettre n'importe quoi. C'est la fiche 7 qui va les transformer en vraies clés étrangères.