Gestion des contraintes SQLite

Introduction

Dans ce labo, vous explorerez la gestion des contraintes SQLite pour garantir l'intégrité des données. Vous commencerez par définir des contraintes de clé étrangère (foreign key constraints) pour établir des relations entre les tables, en implémentant des contraintes CHECK, en créant des clés composites, et enfin, en testant les violations de contraintes pour comprendre comment SQLite applique ces règles. Cette expérience pratique vous fournira une compréhension concrète de la manière de maintenir la cohérence des données au sein de vos bases de données SQLite.

Skills Graph

Créer des tables avec une contrainte de clé étrangère (Foreign Key Constraint)

Dans cette étape, vous allez créer deux tables, customers et orders, et établir une contrainte de clé étrangère entre elles. Cette contrainte garantira que chaque commande est associée à un client valide.

Tout d'abord, ouvrez l'interpréteur de commandes SQLite (SQLite shell) en exécutant la commande suivante dans votre terminal :

sqlite3 /home/labex/project/database.db

Cette commande ouvre une connexion au fichier de base de données SQLite nommé database.db. Si le fichier n'existe pas, SQLite le créera. Vous devriez maintenant voir l'invite sqlite>.

Maintenant, créez la table customers avec la commande SQL suivante :

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Cette commande crée une table nommée customers avec quatre colonnes : customer_id, first_name, last_name et email. La colonne customer_id est la clé primaire (primary key) et s'incrémentera automatiquement pour chaque nouveau client.

Ensuite, créez la table orders avec une contrainte de clé étrangère faisant référence à la table customers :

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Cette commande crée une table nommée orders avec quatre colonnes : order_id, customer_id, order_date et total. La colonne order_id est la clé primaire. La clause FOREIGN KEY (customer_id) REFERENCES customers(customer_id) établit une contrainte de clé étrangère, garantissant que le customer_id dans la table orders fait référence à un customer_id valide dans la table customers.

Pour vérifier que les tables ont été créées, vous pouvez utiliser la commande suivante :

.tables

Cette commande listera toutes les tables de la base de données. Vous devriez voir à la fois customers et orders dans la sortie.

Insérer des données et tester la contrainte de clé étrangère (Foreign Key Constraint)

Dans cette étape, vous allez insérer des données dans les tables customers et orders et tester la contrainte de clé étrangère.

Tout d'abord, vous devez activer les contraintes de clé étrangère dans SQLite (elles sont désactivées par défaut) :

PRAGMA foreign_keys = ON;

Maintenant, insérez des données dans la table customers :

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]');

Cette commande insère deux nouveaux clients dans la table customers. Le customer_id sera automatiquement attribué.

Maintenant, insérez une commande dans la table orders, en faisant référence à l'un des clients existants :

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

Cette commande insère une nouvelle commande dans la table orders, en l'associant au client dont le customer_id est 1.

Ensuite, essayez d'insérer une commande avec un customer_id qui n'existe pas dans la table customers :

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

Vous verrez un message d'erreur similaire à celui-ci : Error: FOREIGN KEY constraint failed. Cela confirme que la contrainte de clé étrangère fonctionne correctement, vous empêchant de créer une commande pour un client inexistant.

Pour afficher les données dans les tables, vous pouvez utiliser les commandes suivantes :

SELECT * FROM customers;
SELECT * FROM orders;

Ces commandes afficheront le contenu des tables customers et orders, respectivement.

Implémenter une contrainte CHECK

Dans cette étape, vous allez créer une nouvelle table customers_with_check qui inclut une contrainte CHECK pour garantir que toutes les adresses e-mail contiennent le symbole @.

Tout d'abord, créons une nouvelle table avec la contrainte CHECK directement :

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

Cette commande crée une nouvelle table nommée customers_with_check avec une contrainte CHECK sur la colonne email qui garantit qu'elle contient le symbole @. L'opérateur LIKE est utilisé pour la correspondance de motifs (pattern matching), et % est un caractère générique (wildcard character) qui correspond à n'importe quelle séquence de caractères.

Maintenant, copions les données client existantes dans la nouvelle table :

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

Maintenant, essayez d'insérer un nouveau client avec une adresse e-mail invalide :

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

Vous devriez voir un message d'erreur similaire à celui-ci : Error: CHECK constraint failed: email. Cela indique que la contrainte CHECK a empêché l'insertion de l'adresse e-mail invalide.

Pour vérifier que la contrainte CHECK fonctionne, insérez un client avec une adresse e-mail valide :

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

Cette commande devrait s'exécuter avec succès, car l'adresse e-mail contient le symbole @.

Créer une table avec une clé composite (Composite Key)

Dans cette étape, vous allez créer une table nommée enrollments avec une clé composite constituée des colonnes student_id et course_id.

Exécutez l'instruction SQL suivante dans l'interpréteur (shell) SQLite :

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

Cette commande crée une table nommée enrollments avec trois colonnes : student_id, course_id et enrollment_date. La clause PRIMARY KEY (student_id, course_id) spécifie que la clé primaire (primary key) de la table est constituée à la fois des colonnes student_id et course_id. Cela signifie que la combinaison de student_id et course_id doit être unique pour chaque ligne de la table.

Maintenant, insérez des données dans la table enrollments :

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

Cela insérera trois lignes dans la table enrollments.

Ensuite, essayez d'insérer une ligne avec les mêmes student_id et course_id qu'une ligne existante :

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

Vous devriez voir un message d'erreur similaire à celui-ci : Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Cela indique que la contrainte de clé composite a empêché l'insertion de la ligne en double.

Pour vérifier que la table a été créée correctement, vous pouvez utiliser la commande .tables dans l'interpréteur SQLite :

.tables

Vous devriez voir enrollments dans la sortie.

Résumé

Dans ce TP (travaux pratiques), vous avez appris à définir et à implémenter des contraintes dans SQLite pour garantir l'intégrité des données. Vous avez créé des tables avec des contraintes de clé étrangère (foreign key constraints), implémenté des contraintes CHECK pour valider les données, et créé des tables avec des clés composites (composite keys) pour identifier de manière unique les lignes en fonction de plusieurs colonnes. En comprenant et en utilisant ces contraintes, vous pouvez construire des bases de données robustes et fiables qui maintiennent la cohérence des données.