Liste des séquences

Introduction B.D.D.R Création de tables INSERTION SELECTION 1 SELECTION 2

Créer une table.

CREATE TABLE permet de créer une table dans une base de données. L'utilisation la plus simple de cette commande SQL est :

CREATE TABLE nom_de_la_table (
Nom_de_colonne1 Type_de_donnée,
Nom_de_colonne2 Type_de_donnée,
Nom_de_colonne3 Type_de_donnée, ...);

Les types de données sont généralement choisis parmi les suivants :

  • numériques entiers : TINYINT[(length)], SMALLINT[(length)], MEDIUMINT[(length)], INT[(length)], INTEGER[(length)], BIGINT[(length)]
  • numériques réels : REAL[(length,decimals)], DOUBLE[(length,decimals)], FLOAT[(length,decimals)], DECIMAL(length,decimals), NUMERIC(length,decimals)
  • dates, heures : DATE, TIME, TIMESTAMP, DATETIME
  • chaînes de caractères : CHAR(length), VARCHAR(length), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
  • binaire (images, pdf...) : TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
  • une valeur parmi un ensemble : ENUM(value1,value2,value3,...)
  • plusieurs valeurs parmi un ensemble : SET(value1,value2,value3,...)

L'option NOT NULL, placée après le type de données, permet de préciser au SGBDR qu'il est obligatoire de renseigner la valeur de ce champ :

  CREATE TABLE nom_de_la_table (
	   ...,
	   usr_login varchar(255) NOT NULL,
	   ...);

Les clés primaires.

Définir une clé primaire simple (composée d'un seul champ).

Voici comment définir une clé primaire, grâce à la clause PRIMARY KEY placée juste après le champ concerné :

CREATE TABLE nom_table (
	champ1 type PRIMARY KEY, 
	champ2 type, 
	champ3 type, 
	...);

Voici un schéma relationnel qui permet de représenter des albums de musique et leurs pistes. On peut associer un artiste soit à l'album entier, soit à une piste précise. Le type de participation est par exemple "Chanteur", "Compositeur", "Bassiste", etc.

D'après ce schéma relationnel, donnez l'instruction SQL CREATE TABLE qui permet de créer la table Album. Le champ alb_titre ne peut pas être NULL : un album a forcément un titre.

Votre réponse n°1

Donnez l'instruction SQL CREATE TABLE qui permet de créer la table Artiste. Le champ art_nom ne peut pas être NULL : un artiste a forcément un nom.

Votre réponse n°2

Définir une clé primaire composée de plusieurs champs.

Pour définir une clé composée, on utilise la clause PRIMARY KEY placée après la définition de tous les champs :

CREATE TABLE nom_table (
	champ1 type, 
	champ2 type, 
	champ3 type, 
	PRIMARY KEY (champ1, champ2)
);

Donnez l'instruction SQL CREATE TABLE qui permet de créer la table Piste. A ce stade, ne tenez pas compte des contraintes de clés étrangères. Le champ pis_titre ne peut pas être NULL : une piste a forcément un titre.

Votre réponse n°3

Rappels sur... les erreurs de type les plus fréquentes.

Auto incrément : souvent, on peut laisser au SGBDR le soin de choisir arbitrairement une clé primaire (l'auto-incrément est fait pour ça). Mais ce n'est pas toujours possible : lorsqu'on est en train d'associer des données entre elles (par exemple on associe une facture à un client), on veut contrôler le numéro du client, donc il ne sera pas en auto-incrément dans la table facture.

L'auto incrément n'est pas adapté pour les numéros d'ordre : à priori, un numéro d'ordre peut ressembler à un numéro automatique (1ère facture du client 1, 2ème facture du client 1, 3ème facture du client 1, etc.). Mais non, car pour un autre client on va à nouveau numéroter à partir de 1 (1ère facture du client 2, 2ème facture du client 2, etc.). Les auto-incréments ne savent pas gérer ça, il faudra donc trouver une autre technique et ne pas mettre les numéros d'ordre en auto-incrément.

Le type BIGINT n'est pas adapté pour les numéros d'ordre : à moins que vous ne connaissiez un album de musique qui ait plus de 32767 pistes, ou une entreprise qui facture plus de 32767 fois le même client : en général les numéros d'ordre ne sont pas très grands et un entier simple (sur deux octets) suffit, voire sur 1 octet (TINYINT).

Les clés étrangères.

Définir une clé étrangère simple.

Contrairement à la clé primaire qui peut être définie soit directement avec le champ, soit à la fin (après tous les champs), la clé étrangère est forcément définie à la fin.

CREATE TABLE nom_table (
	champ1 type,
	FOREIGN KEY (champ1) REFERENCES nom_tableX (champX) 
);

Donnez l'instruction SQL CREATE TABLE qui permet de créer la table Participation_Album.

Votre réponse n°4

Donnez l'instruction SQL CREATE TABLE qui permet de créer la table Piste, en intégrant cette fois-ci les contraintes liées aux clés étrangères.

Votre réponse n°5

Définir une clé étrangère composée.

Précision sur... quand faut-il déclarer une clé étrangère composée ?

Lorsqu'on a une clé primaire composée (c'est à dire que plusieurs champs forment la clé primaire), alors les clés étrangères pointant vers cette clé primaire seront composées.

Imaginons que j'oublie de déclarer ma clé étrangère composée et qu'à la place je déclare deux clés étrangères simples. Si j'ai dans mes tables la facture n°17 du client n°1, et la facture n°6 du client n°2, alors je serai autorisé à saisir, en clé étrangère, la facture n°17 du client n°2, alors que celle-ci n'existe pas !

C'est pourquoi il faut vérifier non pas que d'une part le client n°2 existe et que d'autre part la facture n°17 existe, mais il faut vérifier le couple (facture n°17 du client n°2), ce qui nous renverra une erreur d'intégrité référentielle, normal puisque celle-ci n'existe pas.

Cette vérification se fait en déclarant une clé étrangère composée. Soyez vigilant dans les exercices qui suivent.

Puisque la clé étrangère est définie à la fin, la syntaxe est exactement la même lorsque la clé étrangère est composée.

CREATE TABLE nom_table (
	champ1 type, 
	champ2 type, 
	FOREIGN KEY (champ1, champ2) REFERENCES nom_tableX (champX, champY)
);

Donnez l'instruction SQL CREATE TABLE qui permet de créer la table Participation_Piste.

Votre réponse n°6

Le point sur... une erreur fréquente.

Quand on déclare une clé étrangère, il ne faut pas se tromper de sens ! Par exemple quand on associe des clients et des factures, la clé étrangère se déclare depuis la table facture, qui pointe vers la table client.

Ceci implique que lorsqu'on crée une nouvelle facture, alors le client associé doit déjà exister dans la table client, ce qui est logique.

Imaginons qu'on déclare la clé dans le mauvais sens (déclarée dans la table client et pointant vers la table facture) : pour éviter une erreur d'intégrité référentielle, il faudra d'abord créer la facture, lui associer un numéro de client qui n'existe pas encore, et ensuite on sera autorisé à créer le client dans la table client. C'est illogique et contraignant.

Nommer une contrainte.

Dans le but de pouvoir modifier ou supprimer une contrainte ultérieurement (c'est à dire après avoir effectué le CREATE TABLE), on peut nommer une contrainte. Cela se fait en ajoutant CONSTRAINT nom_de_la_contrainte au début de la contrainte. Sur l'exemple précédent, si je veux nommer ma contrainte fk1, cela donne :

CREATE TABLE nom_table (
	champ1 type, 
	champ2 type, 
	CONSTRAINT fk1 FOREIGN KEY (champ1, champ2) REFERENCES nom_tableX (champX, champY)
);

Pour supprimer ultérieurement la contrainte fk1, on écrira :

		ALTER TABLE nom_table DROP FOREIGN KEY fk1;

Lien vers les études de cas.

Etude de cas SLAM, 2013, Nouvelle-calédonie : cas Gest-PC, document 4 (go) : le modèle relationnel est donné sous forme de CREATE TABLE.

Etude de cas ARLE, 2010, métropole : Cas SCCP, dossier 4, question 1 (go).

Etude de cas DA, 2010, Nouvelle-Calédonie : Cas ASDOMI, dossier 2, question 1 (go).

Pour en savoir plus : lien vers la doc MySQL.

La documentation de CREATE TABLE et précisions sur les types de données, pour MySQL, en français :
http://dev.mysql.com/doc/refman/5.0/fr/create-table.html

Autres sites
44