SQL : Chapitre 5

Exercices pratiques : corrections

Learning by Doing.
Le monde du SQL.
Chapitre 5.

3 Création de tables

Si vous executer le code du livret ci-dessous pour créer les tables, vous devez obtenir l'erreur suivante.

CREATE TABLE student(

std_id int NOT NULL PRIMARY KEY,

unv_name varchar(50),

std_name varchar(30),

std_surname varchar(30),

std_nationality varchar(20) NOT NULL,

FOREIGN KEY (unv_name)

REFERENCES university(unv_name)

);

CREATE TABLE university(

unv_name varchar(50) NOT NULL PRIMARY KEY,

unv_country varchar(20) NOT NULL,

unv_address varchar(90) NOT NULL

);

#1215 - Cannot add foreign key constraint

#1215 - Impossible d'ajouter des contraintes d'index externe

Notez que les requête SQL sont exécutée dans l'ordre. Le code ci-dessus doit donc créer la table "student" et ensuite créer la table "university". 

 

L'erreur qui apparait fair reference à la clé étrangère qui ne peut pas être défini car cette clé étrangère fait reference à une table inexistante. Effectivement, la table "student" contient une clé étrangère (FOREIGN KEY (unv_name) REFERENCES university(unv_name)) qui fait reference à la table "university" qui n'existe pas encore dans la base de donnée. Il est donc impératif de créer la table "university" avant la table "student" pour pouvoir définir cette clé étrangère. Vous ne pouvez pas faire reference à une table qui n'existe pas encore dans la base de donnée. Pour que la creation de table fonctionne, il suffit d'inverser l'ordre de requîtes à executer pour créer la table "university" en premier.

CREATE TABLE university(

unv_name varchar(50) NOT NULL PRIMARY KEY,

unv_country varchar(20) NOT NULL,

unv_address varchar(90) NOT NULL

);

CREATE TABLE student(

std_id int NOT NULL PRIMARY KEY,

unv_name varchar(50),

std_name varchar(30),

std_surname varchar(30),

std_nationality varchar(20) NOT NULL,

FOREIGN KEY (unv_name)

REFERENCES university(unv_name)

);

4 Insertion des valeurs

L'insertion de valeurs génère l'erreur semblable liée à la clé étrangère. 
 

INSERT INTO student

VALUES

(123, 'UTT', 'Alexander', 'Bell', 'American' ),

(345, 'PSB', 'Marie', 'Curie', 'French' );

 

INSERT INTO university

VALUES

('UTT', 'France', 'Troyes'),

('PSB', 'France', 'Paris');

#1452 - Cannot add or update a child row: a foreign key constraint fails (`education2`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`unv_name`) REFERENCES `university` (`unv_name`))

Une clé étrangère peut contenir uniquement des valeurs qui existent déjà dans la base de données en temps que clé primaire. Vous ne pouvez pas insérer un étudiant lié à l'université identifié par le nom de "UTT" si celui-ci n'existe pas encore dans la table "université". Il faut donc insérer les universités en premier pour pouvoir ensuite les référencer dans la table "student".

INSERT INTO university

VALUES

('UTT', 'France', 'Troyes'),

('PSB', 'France', 'Paris');

INSERT INTO student

VALUES

(123, 'UTT', 'Alexander', 'Bell', 'American' ),

(345, 'PSB', 'Marie', 'Curie', 'French' );

Il est également impossible d'ajouter la même clé primaire plusieurs fois. Si un attribut est défini comme clé primaire, les valeurs doivent être uniques pour chaque entrée dans la base de données. C'et pour cette raison que vous ne pouvez pas exécuter la requête suivante; PSB existe déjà dans la base de données.

INSERT INTO university

VALUES ('PSB', 'France', 'Paris');

#1062 - Duplicate entry 'PSB' for key 'PRIMARY'

#1062 - Duplicata du champ 'PSB' pour la clef 'PRIMARY'

Le mot clé NULL permet d'ajouter une valeur vide dans l'attribut ce qui n'est pas toujours autorisé. Par exemple, l'exécution de la requête suivante est impossible. Quand nous avons créé la table "university", nous avons défini que les valeurs des attributs ne peuvent pas être vides (NULL) ce qui est indiqué dans l'erreur.

INSERT INTO university
VALUES ('Stanford', 'USA', NULL);

#1048 - Column 'unv_address' cannot be null

#1048 - Le champ 'unv_address' ne peut être vide (null)

CREATE TABLE university(

unv_name varchar(50) NOT NULL PRIMARY KEY,

unv_country varchar(20) NOT NULL,

unv_address varchar(90) NOT NULL

);

Notez que NULL ne doit pas être entre les apostrophes, car sinon il sera considéré non comme le mot-clé NULL qui signifie que la valeur est vide, mais comme une chaine de caractères 'NULL'. La valeur ne sera donc pas vide, elle va contenir une chaine de caractères (un mot). L'ajout de l'importe quelle chaine de caractères va fonctionner, car la base de données atteint une valeur d'un type varchar, donc une chaine de caractères. La base de données ne fera pas de vérification si le contenu de la chaine est cohérent par rapport à l’attribut. La vérification est faite pour s’assurer que le type de donnée inséré est correct et que la valeur respecte des contraintes (e.g. la chaine de caractères ne dépasse pas une certaine longueur autorisée). Il est possible d’ajouter une chaine de caractère sans caractères aussi. La requête suivante va donc insérer une université sans l'adresse ce qui contourne la contrainte fait à la creation. Notez qu’utiliser '' au lieu de NULL peut impacter les résultats de vos sélections par la suite.

INSERT INTO university

VALUES ('Stanford', 'USA', '');

5 Faire des modifications

Pour modifier l'adresse de Stanford, vous pouvez utiliser la requête suivante

UPDATE university

SET unv_address = 'San Francisco'

WHERE unv_name = 'Stanford';

UPDATE university signifie qu'il faut apporter une modification à la table "university".

SET unv_address = 'San Francisco' signifie qu'il faut attribuer la valeur 'San Francisco' à l'attribut unv_address.

WHERE unv_name = 'Stanford' signifie que l'attribution doit être faite seulement pour les entrées qui ont unv_name egale à la valeur 'Stanford'. Sans cette ligne, unv_address sera mises à jour vous toutes les universités.

Il est aussi possible de modifier une valeur directement dans l'interface en cliquant sur la table qui vous intéresse et en cliquant sur la valeur. Néanmoins, cela n'est pas forcement très pratique surtout si vous avez beaucoup de valeurs.

phpMyAdmin modify value

Pour ajouter un troisième étudiant « Larry Page » inscrit à Stanford, vous pouvez utiliser la requête suivante. Il faut simplement l'attribuer l'identifiant unique (premier attribut) puis remplir les autres attributs comme d'habitude.

INSERT INTO student

VALUES

(958, 'Stanford', 'Larry', 'Page', 'American' );

Notez qu'il est possible d'ajouter un étudiant avec un nom NULL car la contrainte NOT NULL n'était pas défini à la creation de la table.

CREATE TABLE student(

std_id int NOT NULL PRIMARY KEY,

unv_name varchar(50),

std_name varchar(30),

std_surname varchar(30),

std_nationality varchar(20) NOT NULL,

FOREIGN KEY (unv_name)

REFERENCES university(unv_name)

);

Vous pouvez donc, par exemple, executer la requête suivante:

INSERT INTO student

VALUES

(906, 'Stanford', NULL, NULL, 'American' );

Modifications de la structure

Notez que quand vous voulez supprimer les tables avec DROP TABLE, vous ne pouvez pas laisser les tables avec une référence vers des tables et des valeurs qui n'existent pas. Il faut donc supprimer la table "student" avant de supprimer la table "university".

DROP TABLE student;
DROP TABLE university;

Pour ajouter l'attribut supplémentaire qui servira à sauvegarder l’année de la fondation de l’établissement (variable numérique de type ‘integer’), il suffit de l'ajouter dans la structure de la requête CREATE. Le table 'student' reste sans modifications.

CREATE TABLE university(

unv_name varchar(50) NOT NULL PRIMARY KEY,

unv_country varchar(20) NOT NULL,

unv_address varchar(90) NOT NULL,

unv_year int

);

Vous pouvez ajouter les mêmes valeurs d'avant avec le code suivant. Notez que vous avez désormais 4 valeurs à ajouter pour chaque université et non les trois. Il faut donc ajouter une valeur en plus pour l'année. Sinon, l'erreur suivante va apparaitre.

#1136 - Column count doesn't match value count at row 1

INSERT INTO university

VALUES

('UTT', 'France', 'Troyes', 1994),

('PSB', 'France', 'Paris', 1974),

('Stanford', 'USA', 'San Francisco', 1891);

INSERT INTO student

VALUES

(123, 'UTT', 'Alexander', 'Bell', 'American' ),

(345, 'PSB', 'Marie', 'Curie', 'French' ),

(958, 'Stanford', 'Larry', 'Page', 'American' );

Pour ajouter des colonnes supplementaire dans la table "student", vous pouvez utiliser le code suivant.

ALTER TABLE student
ADD std_age integer;

ALTER TABLE student
ADD std_phone varchar(15);

Vous pouvez y ajouter des valeurs avec UPDATE.

UPDATE student

SET std_age = 22;

UPDATE student

SET std_phone = "06.XX.XX.XX.XX";

new attribut
UPDATE.png