Création de tables

Le SQL, comportant un langage de définition de données (LDD), permet de créer des tables. Pour cela, il utilise le couple de mots clés CREATE TABLE.

La création de tables

Le création de tables se fait à l'aide du couple de mots-clés CREATE TABLE. La syntaxe de définition simplifiée d'une table est la suivante:

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

Les types de données

Pour chaque colonne que l'on crée, il faut préciser le type de données que le champ va contenir. Celui-ci peut être un des types suivants:

Type de donnée	Syntaxe	Description
Type alphanumérique	CHAR(n)	Chaîne de caractères de longueur fixe n (n<16383)
Type alphanumérique	VARCHAR(n)	Chaîne de caractères de n caractères maximum (n<16383)
Type numérique	NUMBER(n,[d])	Nombre de n chiffres [optionnellement d après la virgule]
Type numérique	SMALLINT	Entier signé de 16 bits (-32768 à 32757)
Type numérique	INTEGER	Entier signé de 32 bits (-2E31 à 2E31-1)
Type numérique	FLOAT	Nombre à virgule flottante
Type horaire	DATE	Date sous la forme 16/07/99
Type horaire	TIME	Heure sous la forme 12:54:24.85
Type horaire	TIMESTAMP	Date et Heure

L'option NOT NULL, placée immédiatement après le type de donnée permet de préciser au système que la saisie de ce champ est obligatoire.

Insertion de lignes à la création

Il est possible de créer une table en insérant directement des lignes lors de la création. Les lignes à insérer peuvent être alors récupérées d'une table existante grâce au prédicat AS SELECT. La syntaxe d'une telle expression est la suivante:

CREATE TABLE Nom_de_la_table (Nom_de_colonne1 Type_de_donnée,
			      Nom_de_colonne2 Type_de_donnée,
			      ...)
AS SELECT Nom_du_champ1,
	  Nom_du_champ2,
	  ...
FROM Nom_de_la_table2
WHERE Prédicat;

   Contraintes d'intégrité

Expression de contraintes d'intégrité

Une contrainte d'intégrité est une clause permettant de contraindre la modification de tables, faite par l'intermédiaire de requêtes d'utilisateurs, afin que les données saisies dans la base soient conformes aux données attendues. Ces contraintes doivent être exprimées dès la création de la table grâce aux mots clés suivants:

• CONSTRAINT
• DEFAULT
• NOT NULL
• UNIQUE
• CHECK

Définir une valeur par défaut

Le langage SQL permet de définir une valeur par défaut lorsqu'un champ de la base n'est pas renseigné grâce à la clause DEFAULT. Cela permet notamment de faciliter la création de tables, ainsi que de garantir qu'un champ ne sera pas vide.

La clause DEFAULT doit être suivie par la valeur à affecter. Cette valeur peut être un des types suivants:

• constante numérique
• constante alphanumérique (chaîne de caractères)
• le mot clé USER (nom de l'utilisateur)
• le mot clé NULL
• le mot clé CURRENT_DATE (date de saisie)
• le mot clé CURRENT_TIME (heure de saisie)
• le mot clé CURRENT_TIMESTAMP (date et heure de saisie)

Forcer la saisie d'un champ

Le mot clé NOT NULL permet de spécifier qu'un champ doit être saisi, c'est-à-dire que le SGBD refusera d'insérer des tuples dont un champ comportant la clause NOT NULL n'est pas renseigné.

Emettre une condition sur un champ

Il est possible de faire un test sur un champ grâce à la clause CHECK() comportant une condition logique portant sur une valeur entre les parenthèses. Si la valeur saisie est différente de NULL, le SGBD va effectuer un test grâce à la condition logique. Celui-ci peut évenutellement être une condition avec des ordres SELECT...

Tester l'unicité d'une valeur

La clause UNIQUE permet de vérifier que la valeur saisie pour un champ n'existe pas déjà dans la table. Cela permet de garantir que toutes les valeurs d'une colonne d'une table seront différentes.

Nommer une contrainte

Il est possible de donner un nom à une contrainte grâce au mot clé CONSTRAINT suivi du nom que l'on donne à la contrainte, de telle manière à ce que le nom donné s'affiche en cas de non respect de l'intégrité, c'est-à-dire lorsque la clause que l'on a spécifiée n'est pas validée.

Si la clause CONSTRAINT n'est pas spécifiée, un nom sera donné arbitrairement par le SGBD. Toutefois, le nom donné par le SGBD risque fortement de ne pas être compréhensible, et ne sera vraisemblablement pas compris lorsqu'il y aura une erreur d'intégrité. La stipulation de cette clause est donc fortement conseillée.

Exemple de création de table avec contrainte

Voici un exemple permettant de voir la syntaxe d'une instruction de création de table avec contraintes:

CREATE TABLE clients(
Nom    char(30) NOT NULL,
Prenom char(30) NOT NULL,
Age    integer, check (age < 100),
Email  char(50) NOT NULL, check (Email LIKE "%@%")
)

Definition de clés

Grâce à SQL, il est possible de définir des clés, c'est-à-dire spécifier la (ou les) colonne(s) dont la connaissance permet de désigner précisément un et un seul tuple (une ligne).

• L'ensemble des colonnes faisant partie de la table en cours permettant de désigner de façon unique un tuple est appelé clé primaire et se définit grâce à la clause PRIMARY KEY suivie de la liste de colonnes, séparées par des virgules, entre parenthèses. Ces colonnes ne peuvent alors plus prendre la valeur NULL et doivent être telles que deux lignes ne puissent avoir simultanément la même combinaison de valeurs pour ces colonnes.

  PRIMARY KEY (colonne1, colonne2, ...)

• Lorsqu'une liste de colonnes de la table en cours de définition permet de définir la clé primaire d'une table étrangère, on parle alors de clé étrangère, et on utilise la clause FOREIGN KEY suivie de la liste de colonnes de la table en cours de définition, séparées par des virgules, entre parenthèses, puis de la clause REFERENCES suivie du nom de la table étrangère et de la liste de ses colonnes correspondantes, séparées par des virgules, entre parenthèses.

  FOREIGN KEY (colonne1, colonne2, ...) 
  REFERENCES Nom_de_la_table_etrangere(colonne1,colonne2,...)

Trigger (gâchette): garantie de l'intégrité référentielle

Les clés étrangères permettent de définir les colonnes d'une table garantissant la validité d'une autre table. Ainsi, il existe des éléments (appelés triggers, en français gâchettes) permettant de garantir ces contraintes appelée intégrité référentielle, c'est-à-dire notamment de garantir qu'un nuplet utilisé à partir d'une autre table existe réellement.
Ces triggers sont ON DELETE et ON UPDATE:

• ON DELETE est suivi d'arguments entre accolades permettant de spécifier l'action à réaliser en cas d'effacement d'une ligne de la table faisant partie de la clé étrangère:
  • CASCADE indique la suppression en cascade des lignes de la table étrangères dont les clés étrangères correspondent aux clés primaires des lignes effacées
  • RESTRICT indique une erreur en cas d'effacement d'une valeur correspondant à la clé
  • SET NULL place la valeur NULL dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé
  • SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé
• ON UPDATE est suivi d'arguments entre accolades permettant de spécifier l'action à réaliser en cas de modification d'une ligne de la table faisant partie de la clé étrangère:
  • CASCADE indique la modification en cascade des lignes de la table étrangères dont les clé primaires correspondent aux clés étrangères des lignes modifie;es
  • RESTRICT indique une erreur en cas de modification d'une valeur correspondant à la clé
  • SET NULL place la valeur NULL dans la ligne de la table étrangère en cas de modification d'une valeur correspondant à la clé
  • SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la table étrangère en cas de modification d'une valeur correspondant à la clé

Création d'assertions

Les assertions sont des expressions devant être satisfaites lors de la modifications de données pour que celles-ci puissent être réalisées. Ainsi, elles permettent de garantir l'intégrité des données. Leur syntaxe est la suivante:

CREATE ASSERTION Nom_de_la_contrainte CHECK (expression_conditionnelle)

Les assertions ne sont pas implémentées dans l'ensemble des SGBDR...

   Creations de vues

Qu'est-ce qu'une vue?

Une vue est une table virtuelle, c'est-à-dire dont les données ne sont pas stockées dans une table de la base de données, et dans laquelle il est possible de rassembler des informations provenant de plusieurs tables. On parle de "vue" car il s'agit simplement d'une représentation des données dans le but d'une exploitation visuelle. Les données présentes dans une vue sont définies grâce à une clause SELECT

Création d'une vue en SQL

La création d'une vue se fait grâce à la clause CREATE VIEW suivie du nom que l'on donne à la vue, puis du nom des colonnes dont on désire agrémenter cette vue (il faut autant de redéfinitions de colonne qu'il y en aura en sortie), puis enfin d'une clause AS précédant la sélection. La syntaxe d'une vue ressemble donc à ceci:

CREATE VIEW Nom_de_la_Vue
(colonnes)
AS SELECT ...

CREATE VIEW Vue
(colonneA,colonneB,colonneC,colonneD)
AS SELECT colonne1,colonne2,colonneI,colonneII
FROM Nom_table1 Alias1,Nom_tableII AliasII
WHERE Alias1.colonne1 = AliasII.colonneI
AND Alias1.colonne2 = AliasII.colonneII

Intérêts des vues

La vue représente de cette façon une sorte d'intermédiaire entre la base de données et l'utilisateur. Cela de nombreuses conséquences:

• une sélection des données à afficher
• une restriction d'accès à la table pour l'utilisateur, c'est-à-dire une sécurité des données accrue
• un regroupement d'informations au sein d'une entité

   Création d'un index

Qu'est-ce qu'un index?

Un index est un objet complémentaire (mais non indispensable) à la base de données permettant d'"indexer" certaines colonnes dans le but d'améliorer l'accès aux données par le SGBDR, au même titre qu'un index dans un livre ne vous est pas indispensable mais vous permet souvant d'économiser du temps lorsque vous recherchez une partie spécifique de ce dernier...

Toutefois la création d'index utilise de l'espace mémoire dans la base de données, et, étant donné qu'il est mis à jour à chaque modification de la table à laquelle il est rattaché, peut alourdir le temps de traitement du SGBDR lors de la saisie de données. Par conséquent il faut que la création d'index soit justifié et que les colonnes sur lesquelles il porte soient judicieusement choisies (de telle façon à minimiser les doublons). De cette façon certains SGBDR créent automatiquement un index lorsqu'une clé primaire est définie.

La création d'un index

La création d'index en SQL se fait grâce à la clause INDEX précédée de la clause SELECT. Elle permet de définir un index désigné par son nom, portant sur certains champ d'une table. La syntaxe est la suivante:

CREATE [UNIQUE] INDEX Nom_de_l_index
ON Nom_de_la_table
(Nom_de_champ [ASC/DESC], ...)

• L'option UNIQUE permet de définir la présence ou non de doublons pour les valeurs de la colonne
• Les options ASC/DESC permettent de définir un ordre de classement des valeurs présentes dans la colonne

   Modification de la table

• L'ajout de colonnes
• La modification de colonnes
• La suppression de colonnes

Enfin, il est possible d'ajouter des commentaires à une table grâce à la clause COMMENT.

La suppression d'éléments

La clause DROP permet d'éliminer des vues, des index et même des tables. Cette clause est toutefois à utiliser avec parcimonie dans la mesure où elle est irréversible.

La suppression d'une vue se fait avec la syntaxe suivante:

DROP VIEW Nom_de_la_vue

La suppression d'un index se fait avec la syntaxe suivante:

DROP INDEX Nom_de_l_index

La suppression d'une table se fait avec la syntaxe suivante:

DROP TABLE Nom_de_la_table

La suppression des données uniquement

La clause DROP lorsqu'elle est utilisée sur une table élimine les données ainsi que la structure de la table. Il est possible de supprimer uniquement les données en conservant la structure de la table grâce à la clause TRUNCATE.

La suppression des données d'une table se fait avec la syntaxe suivante:

TRUNCATE TABLE Nom_de_la_table

Renommer une table

Il peut parfois être intéressant de renommer une table, c'est la clause RENAME qui permet cette opération. La syntaxe de cette clause est:

RENAME Ancien_Nom TO Nouveau_Nom

La clause RENAME n'est pas implémentée dans tous les SGBDR, consultez la documentation de votre SGBD!

Suppression de colonnes

La clause ALTER permet la modification des colonnes d'un table. Associée avec la clause DROP COLUMN, elle permet de supprimer des colonnes. La syntaxe est la suivante:

ALTER TABLE Nom_de_la_table
DROP COLUMN Nom_de_la_colonne

Il faut noter que la suppression de colonnes n'est possible que dans le cas où:

• La colonne ne fait pas partie d'une vue
• La colonne ne fait pas partie d'un index
• La colonne n'est pas l'objet d'une contrainte d'intégrité

Ajout de colonnes

Associée avec la clause ADD, la clause ALTER permet l'ajout de colonnes à une table. La syntaxe est la suivante:

ALTER TABLE Nom_de_la_table
ADD Nom_de_la_colonne Type_de_donnees

Modification de colonnes

Associée avec la clause MODIFY, la clause ALTER permet la modification du type de données d'une colonne. La syntaxe est la suivante:

ALTER TABLE Nom_de_la_table
MODIFY Nom_de_la_colonne Type_de_donnees

Sous Access 2000, la syntaxe est la suivante : ALTER TABLE Nom_de_la_table ALTER COLUMN Nom_de_la_colonne Type_de_donnees

Ajouter des commentaires à une table

Grâce à la clause COMMENT, il est possible de documenter ses tables en leur ajoutant des commentaires, c'est-à-dire une description

• soit de la table elle-même

  COMMENT Nom_de_la_table IS 'Commentaires';

• soit de certaines colonnes en particulier

  COMMENT Nom_de_la_table.Nom_de_la_colonne IS 'Commentaires';

• soit d'une vue

  COMMENT Nom_de_la_vue IS 'Commentaires';