Data Definition Language (DDL)

Data Definition Language (DDL)

Data Definition Language (DDL) - Le langage de définition de données est le sous-langage responsable de la définition de la manière dont les données sont structurées dans une base de données. En SQL, cela correspond à la manipulation des tables via CREATE TABLE, ALTER TABLE et DROP TABLE

Remarque

On utilise souvent les interfaces graphiques pour générer le code SQL DDL.

Cependant, il est très important de comprendre le fonctionnement du code DDL, puisque les interfaces graphiques ne font qu’offrir une interface pour générer ce code.

Bien souvent, les interfaces graphiques ne produisent pas le code DDL souhaité… et il faudra être en mesure d’évaluer le code généré et le corriger si nécessaire.

Par ailleurs, le code DDL varie d’un SGBD à l’autre, et il est important de comprendre les spécificités de chaque SGBD pour écrire du code DDL compatible.

Que permet de faire le DDL ?

• Créer et supprimer des schémas relationnels (des bases de données)
• Créer, modifier et supprimer des tables et des colonnes
• Ajouter ou supprimer des contraintes statiques et dynamiques sur les colonnes et les tables

Création d’un schéma relationnel

Une base de données est définie par son schéma.

La création d’un schéma varie d’un SGBD à l’autre, mais la syntaxe de base est la suivante :

• Postgresql : CREATE SCHEMA clicom;
• MySQL, SQL Server, Oracle : CREATE DATABASE clicom;
• SQLite : il faut créer un fichier, puis en ligne de commande : sqlite3 mon_fichier.db

Cette instruction pourra être accompagnée de divers paramètres spécifiant notamment les conditions d’autorisation d’accès

Les schémas sont rassemblés dans un catalogue, qui représente un ensemble de bases de données

Création de tables

La syntaxe de base pour créer une table est la suivante :

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

• table_name : le nom de la table à créer
• column1, column2, column3, ... : les noms des colonnes de la table
• datatype : le type de données pour chaque colonne

Création de tables

Exemple :

CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100)
);

Types de données

Les types de données les plus courants pour les colonnes sont :

• INT : entier
• VARCHAR(n) : chaîne de caractères de longueur n
• TEXT : chaîne de caractères de longueur variable
• CHAR(n) : chaîne de caractères de longueur fixe n
• DATETIME : date et heure
• BOOLEAN : booléen
• DATE : date
• TIME : heure
• FLOAT : nombre à virgule flottante
• DECIMAL(p, s) : nombre décimal de précision p et de taille s
• DOUBLE : nombre à virgule flottante double précision
• BLOB : données binaires

Remarque

Les types de données varient d’un SGBD à l’autre, et il est important de consulter la documentation du SGBD utilisé pour connaître les types de données disponibles.

Types de données

Exemples

CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100)
);

CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  price DECIMAL(10, 2),
  description TEXT
);

CREATE TABLE orders (
  id INT,
  order_date DATE,
  total_amount DECIMAL(10, 2),
  is_paid BOOLEAN
);

Les clés primaires

PRIMARY KEY : définit une colonne ou plusieurs colonnes comme clé primaire

Déclaration sur une seule colonne :

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype,
    ...
);

Déclaration sur plusieurs colonnes :

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    PRIMARY KEY (column1, column2)
);

Les clés étrangères

FOREIGN KEY et REFERENCES : définit une colonne comme clé étrangère et la table à laquelle elle fait référence

Remarque

Une clé étrangère comporte autant de colonnes que l’identifiant cible, et ses colonnes sont de même type

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    FOREIGN KEY (column1) REFERENCES other_table(column_name)
);

Les clés étrangères

Si au moins une des colonnes est facultative, alors il est possible de préciser via la clause MATCH, la manière dont l’intégrité référentielle sera vérifiée en présence de valeurs NULL

1. MATCH SIMPLE : si toute les colonnes de la clé étrangère possèdent une valeur, la contrainte référentielle est évaluée, sinon elle est ignorée :

FOREIGN KEY (numClient, numFournisseur) references ACHAT(numClient, numFournisseur) MATCH SIMPLE

2. MATCH FULL : si les colonnes sont toutes nulles, la contrainte est ignorée. Si elles sont toutes non nulles, elle est évaluée. Dans les autres cas, elle n’est pas satisfaite :

FOREIGN KEY (numClient, numFournisseur) references ACHAT(numClient, numFournisseur) MATCH FULL

3. MATCH PARTIAL : la contrainte est évaluée pour les colonnes non nulles. La table cible doit contenir au moins une ligne dont l’identifiant comporte les valeurs non nulles de la clé étrangère :

FOREIGN KEY (numClient, numFournisseur) references ACHAT(numClient, numFournisseur) MATCH PARTIAL

Autres contraintes

• UNIQUE : interdit les doublons. Permet de créer des identifiants secondaires.
• NOT NULL : interdit les valeurs nulles
• CHECK : vérifie une condition
• DEFAULT : définit une valeur par défaut
• AUTO_INCREMENT : incrémente automatiquement la valeur - En général utilisé pour les clés primaires numériques entières
• INDEX : crée un index sur une colonne. Les index permettent d’accélerer le résultat de requêtes sur des colonnes non identifiantes. Les clés primaires sont automatiquement indexées.

Contraintes statiques

Exemples

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE
);

La colonne id est définie comme clé primaire, et elle s’incrémentera automatiquement en démarrant à 1. Les colonnes first_name et last_name ne peuvent pas être nulles. La colonne email doit être unique.

Contraintes statiques

CREATE TABLE test (
  col1 INT,
  col2 VARCHAR(50),
  PRIMARY KEY (col1, col2)
);

La combinaison des colonnes col1 et col2 est définie comme clé primaire.

Contraintes statiques

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  pdescription TEXT
);

La colonne price doit contenir des valeurs décimales non nulles, avec une précision de 10 chiffres et 2 chiffres après la virgule.

Contraintes statiques

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  is_paid BOOLEAN DEFAULT FALSE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

La colonne is_paid est définie par défaut à FALSE. La colonne customer_id est une clé étrangère qui fait référence à la table customers.

Contraintes statiques

CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Les colonnes order_id et product_id sont des clés étrangères qui font référence aux tables orders et products.

Contraintes statiques

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  salary DECIMAL(10, 2) CHECK (salary > 0)
);

La colonne salary doit contenir des valeurs décimales non nulles et supérieures à 0.

Modification de tables

Il est possible de modifier une table existante en ajoutant, modifiant ou supprimant des colonnes.

La syntaxe de base pour ajouter une colonne à une table est la suivante :

ALTER TABLE table_name
ADD column_name datatype;

Modification de tables

Exemples :

Ajout d’une colonne

ALTER TABLE employees
ADD hire_date DATE;

Changement d’une colonne

ALTER TABLE employees
MODIFY COLUMN hire_date DATETIME;

Suppression d’une colonne

ALTER TABLE employees
DROP COLUMN hire_date;

Suppression de tables

La syntaxe pour supprimer une table est la suivante :

DROP TABLE table_name;

Attention

La suppression d’une table est une opération irréversible. Toutes les données stockées dans la table seront définitivement perdues.

Exercice

Proposez des requêtes SQL pour :

• Créer une table categories avec les colonnes numero et nom
  • Avec une contrainte PRIMARY KEY et AUTO_INCREMENT sur la colonne numero
  • Avec une contrainte NOT NULL sur la colonne nom
• Créer une table produits_categories avec les colonnes produit_numero et category_numero
  • Avec des contraintes FOREIGN KEY sur les colonnes produit_numero et category_numero

Solution

CREATE TABLE categories (
  numero INTEGER PRIMARY KEY AUTOINCREMENT,
  nom VARCHAR(50) NOT NULL
);

CREATE TABLE produits_categories (
  produit_numero INT,
  category_numero INT,
  FOREIGN KEY (produit_numero) REFERENCES produits(numero),
  FOREIGN KEY (category_numero) REFERENCES categories(numero),
  PRIMARY KEY (produit_numero, category_numero)
);