Leçon 9.1 · Temps de lecture : ~9 min

L'instruction SQL CREATE TABLE crée une nouvelle table : ses colonnes, leurs types de données et les règles que les données doivent respecter. Dans cette leçon, vous apprendrez à définir des colonnes, à choisir des types de données et à utiliser les principales contraintes : PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK et FOREIGN KEY.

SQL CREATE TABLE : définir une table

Jusqu'ici, vous avez travaillé avec des tables qui existaient déjà. CREATE TABLE appartient au langage de définition des données (DDL) : les commandes qui définissent la structure d'une base de données plutôt que ses données. Une table bien conçue stocke les données de façon compacte, rejette les valeurs invalides et garde cohérentes les relations entre tables.

Les exemples utilisent la syntaxe MySQL et MariaDB. Les bases d'exemple de SQLtest sont en lecture seule : pratiquez ces instructions dans le bac à sable, sur une base sans données d'exemple.

Syntaxe de CREATE TABLE

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    table_constraints
);

Chaque colonne a un nom, un type de données et, éventuellement, des contraintes et des paramètres. Les contraintes portant sur plusieurs colonnes, comme une clé primaire composée, s'écrivent après les colonnes.

Une première table

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    created_at TIMESTAMP
);

Résultat : une table students vide avec cinq colonnes.

Cette table fonctionne, mais elle accepte n'importe quoi : des id en double, des étudiants sans nom. Les contraintes corrigent cela.

Choisir les types de données

Choisissez le type le plus étroit adapté aux données : il économise de l'espace et rejette les valeurs erronées. Stockez les montants en DECIMAL, pas en FLOAT, qui ne peut pas représenter exactement de nombreuses fractions décimales. Pour en savoir plus, voir la leçon sur les types de données.

Clé primaire et AUTO_INCREMENT

Une clé primaire identifie chaque ligne : ses valeurs sont uniques et jamais NULL. Une clé de substitution est généralement générée par la base de données avec AUTO_INCREMENT :

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

INSERT INTO students (first_name, last_name)
VALUES ('ANNA', 'LEE'), ('MARK', 'STONE');

SELECT * FROM students;

Résultat : deux étudiants avec student_id 1 et 2, générés automatiquement.

D'autres bases génèrent les clés autrement : PostgreSQL utilise GENERATED ALWAYS AS IDENTITY ou SERIAL, SQL Server IDENTITY(1, 1).

NOT NULL, UNIQUE et DEFAULT

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• NOT NULL : la colonne doit avoir une valeur.
• UNIQUE : deux lignes ne peuvent pas avoir la même valeur. Les valeurs NULL sont autorisées et ne sont pas considérées comme des doublons.
• DEFAULT : la valeur utilisée quand un INSERT n'en fournit pas.

-- Erreur : Column 'hire_date' cannot be null
INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('JOHN', 'DOE', NULL);

Contraintes CHECK

CHECK définit une condition que chaque ligne doit respecter :

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    discount_pct INT DEFAULT 0 CHECK (discount_pct BETWEEN 0 AND 90)
);

-- Erreur : Check constraint 'products_chk_1' is violated
INSERT INTO products (product_name, price) VALUES ('Broken', -5);

MySQL applique CHECK depuis la version 8.0.16 et MariaDB depuis la 10.2 ; les anciennes versions de MySQL acceptaient la syntaxe mais ignoraient la condition.

Clés étrangères

Une clé étrangère relie une colonne à la clé primaire d'une autre table. La base de données rejette alors les valeurs qui n'existent pas dans la table référencée :

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

INSERT INTO departments (department_name) VALUES ('Sales');
INSERT INTO staff (full_name, department_id) VALUES ('ANNA LEE', 1);

-- Erreur : a foreign key constraint fails (le service 99 n'existe pas)
INSERT INTO staff (full_name, department_id) VALUES ('MARK STONE', 99);

ON DELETE définit ce qui arrive aux lignes enfants quand une ligne référencée est supprimée :

ON UPDATE fonctionne de la même façon quand la clé référencée change. Dans Sakila, la plupart des clés étrangères utilisent ON DELETE RESTRICT : c'est pourquoi vous ne pouviez pas supprimer un client ayant des locations dans la leçon 8.3.

IF NOT EXISTS

Créer une table qui existe déjà est une erreur. IF NOT EXISTS saute alors la création, ce qui est utile dans les scripts exécutés plusieurs fois :

CREATE TABLE IF NOT EXISTS departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Notez que les structures ne sont pas comparées : si une table de ce nom existe avec d'autres colonnes, elle reste telle quelle.

Encodage du texte

Pour les colonnes de texte, MySQL et MariaDB permettent de définir le jeu de caractères et la collation :

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) DEFAULT CHARSET = utf8mb4;

utf8mb4 stocke n'importe quel caractère Unicode, emoji compris ; la collation définit comment les chaînes sont comparées et triées. Depuis MySQL 8.0, utf8mb4 est la valeur par défaut.

Points clés :

• CREATE TABLE définit les colonnes, les types de données et les contraintes ; la nouvelle table est vide.
• Choisissez des types de données précis ; utilisez DECIMAL pour les montants.
• PRIMARY KEY identifie les lignes ; AUTO_INCREMENT génère les valeurs de clé.
• NOT NULL, UNIQUE, DEFAULT et CHECK garantissent des données valides.
• FOREIGN KEY relie les tables ; ON DELETE définit le sort des lignes enfants.
• IF NOT EXISTS évite une erreur quand la table existe déjà.

Questions fréquentes

Comment créer une table avec une clé primaire en SQL ?

Ajoutez PRIMARY KEY à la colonne clé : CREATE TABLE students (student_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);. Pour une clé sur plusieurs colonnes, écrivez PRIMARY KEY (col1, col2) après la liste des colonnes.

Quelle est la différence entre PRIMARY KEY et UNIQUE ?

Les deux interdisent les doublons. Une table n'a qu'une seule clé primaire, dont les colonnes ne peuvent pas être NULL. Une table peut avoir plusieurs contraintes UNIQUE, qui acceptent les valeurs NULL.

Comment ajouter une clé étrangère dans CREATE TABLE ?

Écrivez FOREIGN KEY (column) REFERENCES other_table (key_column) après les colonnes, éventuellement avec les actions ON DELETE et ON UPDATE. La colonne référencée doit être une clé primaire ou unique.