Data Definition Language

SQL (Structured Query Language) est le langage par excellence pour la manipulation de bases de données relationnelles. Il est défini par une norme depuis près de 30 ans et est reconnu par la plupart des SGBD. Il comprend 2 volets ou sous-langages que sont le DDL (Data Definition Language) et le DML (Data Manipulation Language).

Data Definition Language (DDL)

Le DDL comprend un ensemble de commandes ou déclarations qui permettent d’agir sur la structure de la base de données. En particulier, il sera utilisé pour créer ou supprimer les tables, les colonnes des tables, ajouter ou modifier des contraintes.

Création d’une table

La commande suivante crée une table portant le nom MY_TABLE et 5 colonnes, chacune imposant un type de donnée différent.

create table MY_TABLE ( COL1    char(10),
                        COL2    decimal(9,2),
                        COL3    text(),
                        COL4    varchar(255),
                        COL5    int()
                        );

Le langage SQL est capable de prendre en considération un grand nombre de types de données. Cependant, chaque SGBD définit son propre jeu de contraintes en ce qui concerne les types de données qu’il est capable de gérer. Dans le cas de SQLite et contrairement à d’autres SGBD tels que MySQL, la définition des types de colonne n’est pas contraignante, mais s’apparente plutôt à fixer une préférence ou affinité quant aux types de données qu’elles contiennent.

SQLite considère uniquement 6 affinités de types de données: TEXT, INTEGER, REAL, NUMERIC et BLOB. Ces affinités sont déduites des types de valeurs spécifiées dans la déclaration de création de la table. Par exemple, toute colonne dont le type de données contient la chaine de caractères “char” recevra l’affinité TEXT. Pour plus de précision, consultez https://www.sqlite.org/datatype3.html.

Avec SQLite, la définition de table précédente pourra donc prendre la forme suivante:

create table MY_TABLE ( COL1    TEXT,
                        COL2    REAL,
                        COL3    TEXT,
                        COL4    TEXT,
                        COL5    INTEGER
                        );

Attributs obligatoires ou facultatifs

La contrainte NOT NULL doit être attachée à chaque définition de colonne qui correspond à un attribut obligatoire. En conséquence, ne rien mentionner conduit à rendre un attribut facultatif.

create table MY_TABLE ( COL1    TEXT NOT NULL,
                            COL2    REAL,
                            COL3    TEXT,
                            COL4    TEXT,
                            COL5    INTEGER
                            );

Les identifiants

Pour rappel, un identifiant est utilisé pour identifier de manière non équivoque chaque élément d’une table. Par principe, tout identifiant est donc unique dans la table dans laquelle il joue ce rôle. Une même table peut donc contenir plusieurs champs ayant ce caractère unique et pouvant jouer le rôle d’identifiant, mais il sera toujours nécessaire de distinguer au moins un identifiant principal qui sera l’identifiant primaire de la table. La définition de l’identifiant primaire s’obtient simplement de la manière suivante:

create table MY_TABLE ( COL1    TEXT NOT NULL,
                        COL2    REAL,
                        COL3    TEXT,
                        COL4    TEXT,
                        COL5    INTEGER,
                        primary key (COL1)
                        );

Si la table contient un ou des identifiants secondaires (c’est à dire d’autres colonnes contenant des valeurs uniques), ils pourront être référencés à l’aide de la clause unique():

create table MY_TABLE ( COL1    TEXT,
                        COL2    REAL,
                        COL3    TEXT,
                        COL4    TEXT,
                        COL5    INTEGER,
                        primary key (COL1),
                        unique (COL5)
                        );

Les clés étrangères

Les clés étrangères sont créées en utilisant la clause foreign key tel qu’illustré ci-dessous:

create table publication   (
    pubmed_id   INTEGER NOT NULL,
    title       TEXT,
    journal     TEXT,
    year        TEXT,
    primary key (pubmed_id)
);

create table author       (
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    institution TEXT NOT NULL,
    primary key (first_name, last_name, institution)
);

create table author_publication (
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    institution TEXT NOT NULL,
    pubmed_id INTEGER NOT NULL,
    primary key (first_name, last_name, institution, pubmed_id),
    foreign key (first_name, last_name, institution) references author(first_name, last_name, institution),
    foreign key (pubmed_id) references publication(pubmed_id)
);

La déclaration foreign key (pubmed_id) references publication(pubmed_id) signifie que la colonne pubmed_id de la table author_publication contiendra des références à la clé primaire (soit la colonne pubmed_id) de la table qu’elle cible, soit publication.

Par défaut avec SQlite, la déclaration foreign key n’est pas contraignante, c’est à dire qu’elle n’empêche pas la création d’un enregistrement une clé étrangère qui n’existe pas dans la table cible. Pour rendre la clé étrangère contraignante, il est nécessaire de forcer cette contrainte avec PRAGMA foreign_keys = ON; avant d’effectuer la requête d’insertion (actif pour toute la durée de la session).

insert into author VALUES 
    ('Jean', 'Dupont', 'ULiège'),
    ('Marc', 'Dupond', 'ULiège');

insert into publication VALUES
    (1, 'Un premier titre', 'Un premier Journal', '1900'),
    (2, 'Un premier titre', 'Un premier Journal', '1901');

PRAGMA foreign_keys=ON;

insert into author_publication VALUES
    ('Jean', 'Dupont', 'ULiège', 1),
    ('Jean', 'Dupont', 'ULiège', 3);

Forme synthétique des contraintes

Il est possible de définir des contraintes directement sur la ligne de définition de la colonne. C’est la cas pour la définition des clés primaire, secondaires et étrangères (à condition qu’elles ne soient pas composées de plusieurs colonnes). Par ailleurs, d’autres contraintes peuvent être spécifiées sur chaque ligne: il s’agit en particulier du caractère obligatoire de la donnée qui s’obtient en ajoutant la clause not null. Avec SQLite, on ajoutera sytématiquement cette clause à la colonne clé primaire car elle n’est pas implicite.

Il est également possible d’imposer une valeur par défaut à la colonne dans le cas où lors de la création de l’enregistrement la valeur de la colonne en question est omise. Cela s’obtient avec la clause en ligne default suivie de la valeur à assigner.

Ainsi l’exemple précédent pourrait être ré-écrit comme suit:

create table publication   (pubmed_id   INTEGER not null primary key,
                            title       TEXT not null,
                            journal     TEXT,
                            year        TEXT
                        );
create table authors       (first_name  TEXT not null,
                            last_name   TEXT not null,
                            institution TEXT not null default 'Liège University',
                            primary key (first_name, last_name, tution)
                        );

En SQLite, lorsqu’une colonne reçoit le type INTEGER primary key, elle pourra se comporter comme un identifiant numérique auto-incrémenté, c’est à dire que lors de l’introduction de données, si aucune valeur n’est imposée pour ce champ, il sera automatiquement rempli avec une valeur entière non encore utilisée dans la table, le plus souvent le nombre de lignes plus un.

Suppression d’une table

Pour supprimer une table et son contenu, il suffit d’utiliser la commande suivante:

drop table authors;

Cette commande différe de la suivante qui, elle, ne supprime que le contenu et qui peut être complétée par une clause WHERE :

delete from authors where institution = 'Liège Université';
delete from authors;