Création de tables

La syntaxe générale pour créer une nouvelle table dans la base de données est :

CREATE TABLE nom_table (
    attribut1 DOMAINE1 contrainte_eventuelle,
    attribut2 DOMAINE2 contrainte_eventuelle,
    …,
    contraintes_table
    ) ;

Vocabulaire

En SQL, on appelle cette instruction une requête

Pour éviter de supprimer une table déjà existante par « maladresse », il est recommandé d’ajouter un critère de vérification en ligne 1 :

CREATE TABLE IF NOT EXISTS nom_table (

Types de données

Contrairement à d’autres SGBD, SQLite admet un typage dynamique, ce qu’il faut prendre en compte à l’insertion de données.

Les cinq grands domaines de données sont :

• NULL : l’information est manquante ou inconnue.
• INTEGER : entier positif ou négatif (sur 1, 2, 3, 4 ou 8 octets).
• REAL : décimaux en valeur « exacte ».
• TEXT : chaîne de caractères entourée d’apostrophes ou de guillemets.
• BLOB : tout le reste.

Remarques

1. Pour aller plus loin, vous pouvez consulter le site sqlitetutorial.net.

2. Pour utiliser des données sous forme de date et d’heure, tout est détaillé dans cette page.

Exemple

Pour illustrer ce cours nous allons construire une base de données pour un forum.

On commence par la création de la table des sujets qui contiendra les différents sujets du forum.

CREATE TABLE Sujets (
    id_sujet  INTEGER PRIMARY KEY,
    nom       TEXT NOT NULL
);

Les domaines SQL usuels

Les SGBD plus « rigoureux » admettent les domaines suivants :

• INT : entier positif ou négatif.
• REAL : décimaux en valeur « exacte ».
• BOOLEAN : booléen.
• CHAR(n) : chaîne de caractères de longueur n.
• VARCHAR(n) : chaîne de caractères de longueur au plus n.
• TEXT : chaîne de caractères de longueur quelconque.
• DATE : Date au format "AAAA-MM-JJ".
• TIME : Temps au format "HH:MM:SS".

Contraintes d'attribut

Il y a quatre sortes de contraintes pour les attributs. Celles-ci sont cumulables :

• UNIQUE : chaque valeur doit être différente des autres.
• NOT NULL : cet attribut n’a aucune valeur manquante ou inconnue.
• PRIMARY KEY : l’attribut est clef primaire de la table.
  Les contraintes UNIQUE et NOT NULL sont donc incluses dedans.
• CHECK : contrainte de valeur sur l’attribut.

Exemple

On continue avec la création de la table des utilisateurs car elle comporte de nombreuses contraintes d'attribut :

CREATE TABLE Utilisateurs (
    nom_utilisateur   TEXT PRIMARY KEY CHECK(length(nom_utilisateur) <= 20),
    email            TEXT NOT NULL UNIQUE,
    motdepasse        TEXT NOT NULL,
    num_telephone     INTEGER,
    date_inscription  DATE NOT NULL,
    email_valide      BOOLEAN NOT NULL
);

Contraintes de table

Rappel de la syntaxe de création de table

CREATE TABLE nom_table (
    attribut1 DOMAINE1 contrainte_eventuelle,
    attribut2 DOMAINE2 contrainte_eventuelle,
    …,
    contraintes_table
    ) ;

Les contraintes de colonnes peuvent aussi être placées dans la partie des contraintes de table (ligne 5) en indiquant cette contrainte puis en écrivant l'attribut (ou les attributs) correspondant(s) entre parenthèses :

    contrainte(nom_attribut)

C'est aussi dans cette partie qu'on définit les clefs étrangères :

    FOREIGN KEY (nom_attribut) 
        REFERENCES table_associée(nom_attribut_associé)

Exemple

On termine les création des tables par de la table des message qui comporte deux clés externes :

CREATE TABLE Messages (
    id_message        INTEGER PRIMARY KEY,
    nom_utilisateur    TEXT NOT NULL,
    id_sujet         INTEGER NOT NULL,
    contenu          TEXT NOT NULL,
    date_publication   DATE,
    heure_publication  TIME,
    FOREIGN KEY (nom_utilisateur) 
        REFERENCES Utilisateurs(nom_utilisateur),
    FOREIGN KEY (id_sujet) 
        REFERENCES Sujets(id_sujet)
);