Create table

Créer une table consiste de l’expression create table suivi par le nom de la table et ensuite, entre parenthèses, la liste d’attributs et leurs contraintes.

create table films (    
  film_id int,
  title varchar(255),
  rental_rate numeric(5,2),
  primary key (film_id)
);

En créant nos tables, on essaie au maximum d'assurer les domaines exprimés dans notre schéma avec un objectif ultime : assurer l'intégrité de nos données !

• On ne veut pas de valeurs non valides dans la base

• On veut forcer la présence de certaines valeurs, ou savoir explicitement quand il y a des valeurs manquantes

• On veut forcer l'unicité de certaines valeurs

• Quand il y a un lien entre deux tables, il faut que ce lien soit assuré et qu'il n'est pas cassable par erreur !

On assure l'intégrité de nos données via les contraintes.

Intégrité : Contrainte par type de données

Nous imposons une première contrainte sur le type de chaque attribut (ou colonne) de notre table. Cette contrainte est la première façon d'implémenter la contrainte sur le domaine des valeurs possibles pour un attribut.

Type	Description
char(n)	Une chaîne de caractères de longueur fixe. char(8) stockera toujours 8 caractères, même si la valeur qu’on passe en a moins (des espaces seront ajoutés à la fin).
varchar(n)	Une chaîne de caractères de longueur variable.
text	Une longue chaîne de caractères plus adaptés aux paragraphes etc.
int	Valeurs entières
numeric(p,d)	Valeurs avec un point décimal fixe : p digits en total, avec d digits après le point. Ex. numeric(3,2) peut stocker la valeur 2.45 mais pas 24.45
real	Valeurs décimales avec la précision dépendant de l’architecture de la machine
double	Valeurs décimales avec double précision dépendant de l’architecture de la machine
float(n)	Une valeur décimale avec une précision de n digits.
boolean	Une binaire
date	Une date
datetime	Une date et heure

Cette liste n’est pas exhaustive, et peut changer selon le type de SGBDR choisi.

En général, les implémentations de MySQL supporte une liste générique de types : https://www.w3schools.com/sql/sql_datatypes.asp

MariaDB publie une liste de types supportés explicitement par leur SGDBR : https://mariadb.com/kb/en/data-types/

Testons notre table, en ajoutant quelques lignes, avec la commande insert into :

insert into films (film_id, title, rental_rate)
values (45, "Gone with the wind", 5.99);

La commande insert into a la syntaxe suivant :

insert into ( liste d’attributs à insérer ) value (la liste de valeurs correspondantes)

On n’est pas obligé d’inclure tous les attributs d’une table :

insert into films (film_id, title)
values (66, "Les bronzés font du ski");

Soit une valeur default est inséré à la place de l'attribut, manquant, soit NULL :

film_id	title	rental_rate
45	Gone with the wind	5.99
66	Les bronzés font du ski	NULL

Exercice

Écrivez et testez la création d’une table pour votre entité Repas de l’exercice précédente.

• Vous utilisez quel type de donnée pour stocker les textes écrit par l’utilisateur ?

• Vous utilisez quel type de donnée pour stocker une image ?

Testez l’insertion de quelques données.

Intégrité : Contraintes sur le contenu des colonnes

Par défaut, chaque attribut autorise les valeurs nulles. On peut encore limite les domaines d'une colonne en enlevant la possibilité des nulles, préciser l’unicité des valeurs sur la table entière, ou bien contraindre les valeurs possibles via une condition.

Il y a plusieurs sortes de contraintes sur les colonnes :

• not null : les valeurs nulles ne sont pas autorisées

• unique : les valeurs doivent être uniques sur toutes les lignes de la table

• check : valider une donnée selon un critère

Il existe aussi un opérateur default qui permet de fluidifier la contrainte not null, en fournissant une valeur par défaut dans l'absence d'une vraie valeur.

L'exemple suivant démontre les différentes contraintes, ainsi que l'utilisation de default (ici, c'est une table pour la gestion des colis à livrer) :

create table package (
  package_id int NOT NULL UNIQUE,
  name varchar(256) NOT NULL default 'Unknown package',
  price decimal(8,2) NOT NULL default 0,
  article_count int default 1 CHECK (article_count > 0),
  date_of_creation date CHECK (date_of_creation like '__/__/____'),
  primary key (package_id)
);

null vs not null vs default

Attention au piège de default ! On risque de changer le sens d'un attribut en fournissant une valeur default :

• Est-ce qu'il faut être averti de l'absence d'une information pendant la saisie ? Si oui, il faut utiliser not null seul (sans default). Le SGBDR va lancer une exception, et aucune information inconsistante sera conservée.

• Est-ce qu'il faut savoir explicitement quand il a l'absence d'une information ? Si oui, il faut autoriser les null (sans default). On peut ensuite extraire des lignes avec des infos manquantes avec une phrase select et la condition is null, par exemple.

En revanche, si on précise default dans le deux cas dessus, on risque de ne pas savoir si une information a été manquante ou pas, ni extraire des lignes qui manquent une information. On ne peut pas distinguer entre une valeur précisée par default d'une valeur saisie par l'utilisateur.

Exercice

Copiez et exécutez la DDL de cette table « package »

• Essayez des instructions de type « INSERT »

• Sans « name », sans « price » ?

  • Avec article_count = 0, < 0 ?

  • Sans date_of_creation ? Est-ce que CHECK suffit à la place de NOT NULL ?

Intégrité : Contraintes par la clé primaire

Nous savons déjà qu'une clé primaire s'agit de notre choix de clé parmi les clés candidates de notre entité. Cette clé doit avoir la caractéristique de pouvoir identifier une ligne unique parmi toutes les lignes de la table.

Désignant une clé primaire, alors, impose implicitement plusieurs contraintes d'intégrité sur la ou les colonnes de la clé :

• unique : les valeurs dans les attributs de la clé primaire doivent être uniques sur toute la table

• not null : les valeurs ne peuvent pas être nulls

• indexation : un index est construit sur ces attributs (notamment pour garantir l'unicité)

Testons ces règles avec notre table film, en essayant d'ajouter une ligne sans préciser la clé primaire :

insert into films (title)
values ("Jurassic Park");
ERROR 1364 (HY000): Field 'film_id' doesn't have a default value

Nous avons imposé une contrainte type primary key sur la colonne film_id, sans spécifier de valeur default. Nous aurons donc l'erreur affichée.

Rappelez, une clé candidate peut se composer de plusieurs attributs, et donc en SQL, il est possible de créer une clé primaire composée :

create table film_actor (
  film_id int NOT NULL,
  actor_id int NOT NULL,
  primary key (film_id, actor_id)
);

Exercice

Essayez d'ajouter plusieurs combinaisons de lignes à film_actor

• Avec des valeurs null

• Avec 2 fois le même film_id, mais des actor_id différents

• Avec 2 fois le même film_id et actor_id

Intégrité référentielle

En normalisation notre schéma, nous avons dû couper nos entités en deux.

Les lignes avec un lien sémantique sont reliées par une ou plusieurs colonnes, qu'on appelle les clés étrangères.

Rappelez l'exemple de la table etudiant_cours et la table cours :

etudiant_id	cours_id
1	SGBDR
1	Backend
2	Angular
3	Backend
3	Angular

cours_id	prix
SGBDR	1000
Backend	1500
Angular	2000

Ici, l'attribut cours_id de la table etudiant_cours est une clé étrangère, car il pointe vers la ligne correspondante dans la table cours.

Ce lien est maintenu strictement par notre SGBDR :

• on ne peut pas supprimer le cours Angular, par exemple, car la suppression créera une incohérence dans nos données. Que deviennent des étudiants qui à priori sont inscrits du cours Angular, alors que le cours Angular n'existe plus ?

• on ne peut pas ajouter des étudiants qui prennent des cours qui ne se trouvent pas dans la table cours. Nous limitons le domaine possible via cette contrainte.

Cette protection implicite dans notre SGBDR s'appelle l'intégrité référentielle.

Mais, cela n'est pas automatique. Nous sommes obligés d'exprimer les clés étrangères manuellement :

create table cours (
  cours_id varchar(50) not null,
  prix decimal(8,2),
  primary key (cours_id)
);

create table etudiant_cours (
	etudiant_id int not null,
	cours_id varchar(50) not null,
	primary key(etudiant_id, cours_id),
	foreign key (cours_id) references cours(cours_id)
);

Nous avons ajouté la clause foreign key... :

foreign key (attribut_de_cette_table) references autre_table (attribut_de_l’autre_table)

Exercice

Insérez des données dans cours

• Essayer d’insérer des lignes dans etudiant_cours

  • Avec des noms des cours existants

  • Avec des noms des cours qui n’existent pas

• Essayez de supprimer une ligne de cours avec la ligne suivante :

/* Remplacez le ... par un cours_id d'un des cours existants dans cette table. */
delete from cours where cours_id = '...';

À noter : etudiant_cours est une entité faible, puisqu'une ligne ne peut pas être créée sans l’existence d’une valeur de cours. C’est grâce au not null sur cours_id dans la création de etudiant_cours.

Si on autorise les valeurs nulles, etudiant_cours peut exister dans la présence d'un cours, et donc devient une entité forte.

• Si pas déjà fait, insérer un cours avec un cours_id de "SGBDR". Ajouter un étudiant à ce cours, en créant une ligne dans etudiant_cours.

  • Essayez de modifier le nom du cours "SGBDR" à "DBMS". Qu'est-ce qu'il se passe ?

update cours 
set cours_id = "DBMS"
where cours_id = "SGBDR";

L'intégrité référentielle est toujours assurée par le SGBDR !

Nous n'avons pas à faire de pre-tests ou de pre-vérifications de la validité des données. Notre SGBDR s'en occupe tout seul !

Cascades

Il existe des scénarios dans lesquels la modification ou suppression d’une ligne d’une table parent devrait faire modifier/supprimer les lignes dans les tables enfants.

Pour continuer notre exemple, la personne qui a saisi le nouveau cours a fait une faute de frappe, et on a déjà des étudiants inscrits à ce cours. Comment corriger la faute si le SGBDR n'autorise pas la modification ?

Par défaut, notre SGBDR impose des contraintes les plus fortes sur nos références par clé étrangère :

• pas de suppression

• pas de mise à jour

Nous pouvons modifier ce comportement pour être plus souple, en précisant ce qu'il faut faire dans le cas d'une suppression ou modification d'une référence par clé étrangère.

create table etudiant_cours (
  ...
  foreign key (cours_id) references cours(cours_id) on delete cascade on update cascade
);

Ici, nous spécifions que faire dans le cas d'une suppression ou modification. Il y a trois possibilités :

• cascade : faire une sorte de propager nos modifications dans les autres tables liées par la clé-étrangère. Par exemple, toutes les lignes dans etudiant_cours avec "SGBDR" seront modifiés à "DBMS".

• set null : Nous cassons les liens existants entre les deux tables, en mettant null dans la clé étrangère (seulement si autorisé)

• set default : Affecter une valeur par défaut (en cassant aussi les liens)

Si on oublie de préciser les règles on delete ou on update à la création initiale, il est bien possible de les ajouter plus tard. Nous serions obligés de supprimer la clé étrangère, et ensuite la recréer.

Chaque clé (primaire ou étrangère) crée implicitement une contrainte dans notre SGBDR, qui lui dispose d'un identifiant. On peut récupérer toutes ses meta-données de nos tables avec la commande suivante :

show create table etudiant_cours;

Le résultat retourné est :

CREATE TABLE `etudiant_cours` (
  `etudiant_id` int(11) NOT NULL,
  `cours_id` varchar(50) NOT NULL,
  PRIMARY KEY (`etudiant_id`,`cours_id`),
  KEY `cours_id` (`cours_id`),
  CONSTRAINT `etudiant_cours_ibfk_1` FOREIGN KEY (`cours_id`) REFERENCES `cours` (`cours_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

On voit dans le résultat la ligne :

CONSTRAINT `etudiant_cours_ibfk_1` FOREIGN KEY (`cours_id`) REFERENCES `cours` (`cours_id`)

C'est la ligne qui met en place la contrainte par clé étrangère. Pour la modifier :

/* D'abord, supprimer la clé étrangère */
alter table etudiant_cours 
drop foreign key etudiant_cours_ibfk_1;

/* Ensuite la recréer */
alter table etudiant_cours 
add foreign key (cours_id) references cours(cours_id) 
on delete cascade on update cascade;

Exercice

Ajoutez les options de cascade sur la clé étrangère sur etudiant_cours.

• En utilisant update, mettez à jour le nom de SGBDR à DBMS. Qu’est-ce qui se passe ?

• En utilisant delete, essayez de supprimer la ligne SGBDR. Qu’est-ce qui se passe ?

Intégrité vs Cascades vs Null vs Default

Les cascades sont très pratiques pour assurer de l’intégrité automatique sur plusieurs tables. MAIS... le choix n’est pas facile !

S’il est important de garder un historique, les cascades ne sont pas nécessairement utiles :

• un étudiant s’inscrit à un cours et il y participe. On facture sa participation au cours. L’étudiant quitte l’école. Il faut quand même le facturer le temps passé au cours… si on avait fait un ON DELETE CASCADE on n’aurait plus de trace de sa participation, et donc une facturation erronée

Pour des données « temps réels » les cascades sont très utiles

• un étudiant s’inscrit à un cours et il y participe. Il faut générer la feuille d’émargement L’étudiant quitte l’école. On ne veut plus voir l’étudiant sur la feuille d’émargement, donc un ON DELETE CASCADE serait pratique dans ce cas.

Comment anticiper ces deux scenarii ? Une bonne analyse du domaine (il aura fallu identifier le cas de départ d'un étudiant, ainsi que la cas de facturation prorata), le suivi des procédures de normalisation, etc.