Suite du DDL (Data Definition Language)

Nous avons vu que :

• Le DDL permet de créer, modifier ou supprimer des objets de la base de données, comme des tables.
• Lorsqu'on crée une table, on doit indiquer le type de données pour chaque colonne (le domaine d'application)
• Selon le modèle relationnel, une table doit avoir une clé primaire
• Les tables doivent avoir des clés étrangères pour assurer les relations entre elles

Ainsi, lors de la création des tables dans une base de données, on utilise des clés et des contraintes pour garantir la cohérence, la fiabilité et la qualité des données.

Règle importante

Bien qu'il soit possible de créer des contraintes sans les nommer, les bonnes pratiques requiert de toujours nommer les contraintes de façon claire.

Pourquoi ?

• Une contrainte peut facilement être modifiée ou supprimée au besoin

  ALTER TABLE emprunt DROP CONSTRAINT pk_emprunt;

  Sinon on doit supprimer la colonne entière (et ses données) et la recréer (et ses données)

• Une erreur déclenchée par une contrainte nommée est beaucoup plus claire

  ORA-00001: unique constraint (PK_EMPRUNT) violated

  Au lieu de

  ORA-00001: unique constraint (SYS_C004892) violated ???

---

Convention de nommage

1. Utiliser un préfixe explicite

   Type de contrainte	Préfixe
   Clé primaire	pk_
   Clé étrangère	fk_
   Clé secondaire / Unique	uk_
   Check	ck_
   Non nul	nn_

2. Nommer la table

   Si le champ n'a pas le nom de la table intégré

3. Nommer le champ

   Si le champ ne possède pas et le nom de champ et le nom de la table

Exemples :

Pour le champ noEmploye de la table Employe, on veut créer la clé primaire :

noEmploye NUMBER(4) CONSTRAINT pk_noEmploye PRIMARY KEY

Ici, le champ contient le nom de la table, donc le préfixe + nom de champ est suffisant.

---

Pour le champ age de la table Client, on veut s'assurer que l'âge est au moins de 18 ans :

age NUMBER(2) CONSTRAINT ck_client_age CHECK (age > 18)

Ici, le champ age ne contient pas le nom de la table, donc la contrainte doit contenir le préfixe, le nom de la table et finalement le nom du champ.

Dans le cours!

Puisque dans le cours on exige que chaque champ soit nommé avec le nom de la table, les contraintes pourront être nommées avec le préfixe et le nom du champ.

1️⃣ Clé primaire (PRIMARY KEY)

La clé primaire identifie de façon unique chaque ligne d’une table.

Règles :

• Unique pour chaque ligne
• Obligatoire, jamais que cette information n'est nulle (implicite dans la clé primaire)
• Une seule clé primaire par table (constituée de une à plusieurs colonnes)

Exemples :

✏ Si la clé primaire n'est qu'une seule colonne :

CREATE TABLE etudiant (
    noDA            NUMBER(7) CONSTRAINT pk_etudiant PRIMARY KEY,
    prenom          VARCHAR2(50),
    nom             VARCHAR2(50),
    date_naissance  DATE,
    courriel        VARCHAR2(100),
    date_inscription DATE
);

✏ Si la clé primaire s'applique sur plus d'une colonne :

CREATE TABLE emprunt (
    numero_etudiant NUMBER(8),
    numero_livre    NUMBER(6),
    date_emprunt    DATE,
    date_retour     DATE,

    CONSTRAINT pk_emprunt
        PRIMARY KEY (numero_etudiant, numero_livre, date_emprunt)
);

2️⃣ Clé secondaire (clé unique)

Une clé secondaire est un champ qui :

• identifie aussi une ligne de façon unique
• n’est pas la clé primaire
• sera souvent utilisée pour des recherches

Exemples :

✏ Si la clé secondaire n'est qu'une seule colonne :

--Deux etudiants ne peuvent avoir le même courriel
CREATE TABLE etudiant (
    noDA            NUMBER(7) CONSTRAINT pk_etudiant PRIMARY KEY,
    prenom          VARCHAR2(50),
    nom             VARCHAR2(50),
    date_naissance  DATE,
    courriel        VARCHAR2(100) CONSTRAINT uk_etudiant_courriel UNIQUE,
    date_inscription DATE
);

✏ Si la clé secondaire s'applique sur plus d'une colonne :

CREATE TABLE inscription (
    numero_inscription NUMBER(8) CONSTRAINT pk_inscription PRIMARY KEY,
    numero_etudiant  NUMBER(7),
    code_cours       VARCHAR2(10),
    session          VARCHAR2(3),
    date_inscription DATE,

    CONSTRAINT uk_inscription_etudiant_cours_session
        UNIQUE (numero_etudiant, code_cours, session)
);

3️⃣ Clé étrangère (FOREIGN KEY)

Une clé étrangère crée un lien entre deux tables.

Elle associe une clé primaire d'une table à un enregistrement d'une autre table.

Elle garantit que la valeur existe dans une autre table.

Exemples :

✏ Si la clé étrangère n'est qu'une seule colonne :

CREATE TABLE cours (
    code_cours     VARCHAR2(10) CONSTRAINT pk_cours PRIMARY KEY,
    nom_cours      VARCHAR2(100),
    ponderation    VARCHAR2(5),
    id_departement NUMBER(5) CONSTRAINT fk_cours_departement 
                    REFERENCES departement       
);

✏ Si la clé étrangère fait référence à une table dont la clé primaire est composite (plus d'une colonne) :

CREATE TABLE participation_evenement (
    id_evenement NUMBER(5),
    id_membre    NUMBER(8),
    annee        NUMBER(4),
    date_inscription DATE,

    CONSTRAINT pk_participation
        PRIMARY KEY (id_membre, id_evenement, annee),

    CONSTRAINT fk_participation_evenement
        FOREIGN KEY (id_evenement, annee)
        REFERENCES evenement(id_evenement, annee)
);

4️⃣ Contrainte NOT NULL

La contrainte NOT NULL oblige une colonne à toujours contenir une valeur.

Exemple :

nom VARCHAR2(50) NOT NULL

Le nom doit toujours être fourni.

Comment l'écrire de façon complète :

CREATE TABLE cours (
    code_cours     VARCHAR2(10) CONSTRAINT pk_cours PRIMARY KEY,
    nom_cours      VARCHAR2(100) CONSTRAINT nn_nom_cours NOT NULL,
    ponderation    VARCHAR2(5),
    id_departement NUMBER(5) CONSTRAINT fk_cours_departement 
                    REFERENCES departement       
);

Pour modifier la contrainte, NOT NULL est la seule qui peut être modifiée sans être nommée :

ALTER TABLE nom_table 
MODIFY nom_colonne NULL;

• NULL indique que la colonne peut maintenant contenir des valeurs NULL
• La colonne doit déjà exister dans la table
• Il n’est pas nécessaire de connaître le nom de la contrainte NOT NULL.
• Si la colonne contient déjà des données, Oracle autorise la modification tant que les lignes existantes respectent la nouvelle règle (ici aucune restriction, puisque NULL est autorisé).

5️⃣ Contraintes d’intégrité CHECK

Les contraintes CHECK ajoute un niveau de validation sur les données, en plus de NOT NULL et du type de données.

Elles assurent que les valeurs entrées respectent des conditions précises.

Exemples :

✏ Si la contrainte s'applique sur une colonne :

CREATE TABLE abonnements (
...
    type_abonnement VARCHAR2(20) CONSTRAINT ck_type_abonnement 
            CHECK (type_abonnement IN ('mensuel', 'trimestriel', 'annuel')),
...
);

Ou encore :

CREATE TABLE membres (
...
    age NUMBER(2) CONSTRAINT ck_membre_age CHECK (age > 18),
...
);

✏ Si la contrainte s'applique sur plus d'une colonne :

CREATE TABLE reservation_hotel (
    id_reservation   NUMBER CONSTRAINT pk_reservation_hotel PRIMARY KEY,
    nom_client       VARCHAR2(50) NOT NULL,
    date_arrivee     DATE,
    date_depart      DATE,

    CONSTRAINT ck_dates_reservation CHECK (date_depart > date_arrivee)
);

6️⃣ Valeur par défaut (DEFAULT)

L'orsqu'une valeur par défaut est indiquée, elle est automatiquement utilisée si aucune valeur n’est fournie lors de l'insertion de données.

Exemple :

CREATE TABLE reservation_hotel (
    id_reservation   NUMBER CONSTRAINT pk_reservation_hotel PRIMARY KEY,
    nom_client       VARCHAR2(50) NOT NULL,
    date_arrivee     DATE DEFAULT SYSDATE,
    date_depart      DATE DEFAULT SYSDATE + 2
);

On peut aussi indiquer que la valeur par défaut sera la prochaine valeur d'une séquence :

Exemple :

CREATE TABLE reservation_hotel (
    id_reservation   NUMBER 
                        DEFAULT seq_reservation.NEXTVAL
                        CONSTRAINT pk_reservation_hotel PRIMARY KEY,
    nom_client       VARCHAR2(50) NOT NULL,
    date_arrivee     DATE DEFAULT SYSDATE,
    date_depart      DATE DEFAULT SYSDATE + 2
);

🆘 Ajouter, supprimer, modifier des contraintes

Une fois qu'une table a été créée, il est possible de modifier les contraintes si les données respectent les modifications.

Ajout d'une contrainte

ALTER TABLE nomTable 
ADD CONSTRAINT nomContrainte contrainteAjoutee;

Exemples :

ALTER TABLE etudiant 
ADD CONSTRAINT uk_etudiant_email UNIQUE (email);

ALTER TABLE etudiant 
ADD CONSTRAINT ck_client_age CHECK (age > 0);

Suppression d'une contrainte

ALTER TABLE nomTable 
DROP nomContrainte;

Pour les contrainte NOT NULL non nommée :

ALTER TABLE nom_table 
MODIFY nom_colonne NULL;

Pas de modification

En Oracle, il n’est pas possible de modifier directement une contrainte existante (comme on pourrait modifier une colonne).

💡 La méthode standard consiste à :

1. Supprimer la contrainte existante
2. Ajouter une nouvelle contrainte avec les nouvelles règles