Le LANGAGE DE DEFINITION DE DONNEES SQL

(exemples depuis ORACLE)

Le langage de définition de données sert à la phase de création de la base de données. Il se compose de trois commandes: CREATE, ALTER et DROP (resp. créer, modifier  et supprimer). Ces commandes s'appliquent à une table, une vue ou un index (sauf pour ALTER).

Ici, nous allons montrer la forme de base de chaque commande, afin de porter l'attention surtout sur la fonctionnalité de la commande, plutôt que ses caractéristiques détaillées.

NB. Les mots clés SQL sont insensibles à majuscule/minuscule. Nous les écrirons en majuscule pour les distinguer.

Forme de base de CREATE TABLE :

CREATE TABLE nomDeTable(
    nomColonne type contrainteColonne,
         …
    nomColonne type contrainteColonne,
    contrainteTable,
      …
    ContrainteTable
)

Les contraintes permettent au SGBD de renforcer l'intégrité des données. Une contrainte de colonne porte sur une colonne (e.g. clé primaire ou unicité des valeurs), alors qu'une contrainte de table  peut porter sur une ou plusieurs colonnes, ou sur deux tables (e.g. contrainte référentielle).

Remarque : Les déclarations de contraintes sont facultatifs dans la déclaration de table. Elles pourront êtres déclarées ultérieurement de façon séparée.

Les différents types de contraintes sont:

• Not Null
• Default Value
• Unique
• Primary Key
• Foreign Key
• Check Condition

Exemple 1: Une contrainte simple de colonne aura la forme:

   [CONSTRAINT nom-contrainte] PRIMARY KEY | UNIQUE | NOT NULL

comme dans :

    matricule number(5) PRIMARY KEY,
    ename varchar(20) NOT NULL,
    dnum  varchar(5) CONSTRAINT pk PRIMARY KEY 

Nommer une contrainte sert à la désigner plus tard (e.g. message d'erreur du SGBD, en cas de violation).

Exemple 2: Des contraintes simples de table (Employee) pourront être:

   CONSTRAINT salary_OK  CHECK (Salary > SMIC)
   CONSTRAINT Employee_fk FOREIGN KEY (dept) REFERENCES Department(dnum)

Dans nos exemples, nous énoncerons les contraintes sans les nommer.

Les types de données SQL:

Les types de données de base sont :

• INTEGER pour les entiers (32bits), ou SMALLINT pour les entiers courts
• FLOAT (N) ou  REAL ou DECIMAL (M,N) pour les réels
• CHAR(N) pour les textes non modifiables de taille N (N <= 4000)
• VARCHAR2(N) pour les textes modifiables de taille N (N <= 2000)
• NUMBER (n, m) ou NUMBER (n) pour les numériques: entier de taille n ou réel de taille n avec m chiffres décimaux.
• DATE date jusqu'à la seconde
• TIMESTAMP temps jusqu'à la milliseconde
• LONG, CLOB, BLOB objets longs, textes ou binaires (jusqu'à 4 Go)
  

Le tableau suivant résume la plupart les types de données SQL ORACLE (d'après http://www.techonthenet.com/sql/datatypes.php).

Voir par exemple http://www.oracletutorial.com/oracle-basics/oracle-data-types/ pour plus de détails.

Exemples de CREATE TABLE.

CREATE TABLE Employee(
      SSN NUMBER       NOT NULL,
      Fname VARCHAR2(20),
      Lname VARCHAR2(20),
      Gender CHAR(1),
      Salary NUMBER(5) NOT NULL,
      Dept NUMBER
);

Si on tape ensuite DESCRIBE Employee, on obtient :

Name        Null?        Type
----------- ------------ ------------
SSN         NOT NULL     NUMBER
FNAME                    VARCHAR2(20)
LNAME                    VARCHAR2(20)
GENDER                   CHAR(1)
SALARY      NOT NULL     NUMBER(5)
DEPT                     NUMBER

Les mots clés du langages et les identificateurs sont indifférents aux majuscules.

Valeur par défaut :

CREATE TABLE Employee(
    SSN NUMBER NOT NULL,
    Fname VARCHAR2(20),
    Lname VARCHAR2(20),
    Gender CHAR(1) DEFAULT ('F'),
    Salary NUMBER(5) NOT NULL,
    Dept NUMBER
);

Contrainte d'unicité :

CREATE TABLE Employee(
    SSN NUMBER UNIQUE NOT NULL,
    Fname VARCHAR2(20),
    Lname VARCHAR2(20),
    Gender CHAR(1) DEFAULT ('F'),
    Salary NUMBER(5) NOT NULL,
    Dept NUMBER
)

ou bien

CREATE TABLE Employee(
   SSN NUMBER NOT NULL,
   Fname VARCHAR2(20),
   Lname VARCHAR2(20),
   Gender CHAR(1) DEFAULT ('F'),
   Salary NUMBER(5) NOT NULL,
   Dept NUMBER,
   UNIQUE(SSN)
);

Ecriture qui a l'avantage de permettre de déclarer l'unicité d'une combinaison de colonnes:

CREATE TABLE Employee(
   SSN NUMBER UNIQUE NOT NULL,
   Fname VARCHAR2(20),
   Lname VARCHAR2(20),
   Gender CHAR(1) DEFAULT ('F'),
   Salary NUMBER(5) NOT NULL,
   Dept NUMBER,
   UNIQUE(Fname, Lname)
);

Remarque UNIQUE(SSN) peut s'écrire aussi CONSTRAINT SSN_UN_CONS UNIQUE(SSN)

Clé primaire: Primary Key

CREATE TABLE Employee(
    SSN NUMBER PRIMARY KEY,
    Fname VARCHAR2(20),
    Lname VARCHAR2(20),
    Gender CHAR(1) DEFAULT ('F'),
    Salary NUMBER(5) NOT NULL,
    Dept NUMBER,
    UNIQUE(Fname, Lname)
);

ou bien

CREATE TABLE Employee(
    SSN NUMBER,
      Fname VARCHAR2(20),
      Lname VARCHAR2(20),
      Gender CHAR(1) DEFAULT ('F'),
      Salary NUMBER(5) NOT NULL,
      Dept NUMBER,
      UNIQUE(Fname, Lname),
      PRIMARY KEY (SSN)
);

Primary Key implique UNIQUE et NOT NULL

Une seule clé primaire possible (UNIQUE peut servir à spécifier d'autres clés candidates)

Exemple-2: une autre table

CREATE TABLE Department(
    Dept       NUMBER PRIMARY KEY,
    Name       VARCHAR2(20),
    ManagerId NUMBER
);

où ManagerId est un numéro employé.

Clé étrangère: Foreign Key

CREATE TABLE Employee(
    SSN NUMBER PRIMARY KEY,
    Fname VARCHAR2(20),
    Lname VARCHAR2(20),
    Gender CHAR(1) DEFAULT(‘F’),
    Salary NUMBER(5) NOT NULL,
    Dept NUMBER,
    UNIQUE(Fname, Lname),
    FOREIGN KEY (Dept) REFERENCES Department(Dept)
);

Cela signifie (intégrité référentielle). Chaque valeur non NULL de la colonne Dept de Employee doit apparaître dans la colonne Dept de Department (l'inverse n'étant pas forcément vrai). Dans ce cas le SGBD refuse de modifier une clé primaire (ou de supprimer sa ligne) dans la table Department, si sa valeur existe comme clé étrangère dans Employee.

NB: On peut écrire REFERENCES Department si les attributs clés étrangères et primaires ont le même nom comme c'est le cas ici.

Règles de mise à jours:

Si on modifie ou on supprime la clé primaire de la table référencée, on peut spécifier un autre comportement que le refus pour le SGBD.

 FOREIGN KEY (Dept) REFERENCES Department
 ON DELETE SET NULL

Si on supprime un département comme le 123, les employés qui y travaillent (colonne Dept = 123) auront cette colonne = NULL.

 FOREIGN KEY (Dept) REFERENCES Department ON DELETE SET NULL
 ON MODIFY CASCADE

Autre exemple :

CREATE TABLE Department(
    Dept NUMBER PRIMARY KEY,
    Name VARCHAR2(20),
    ManagerId NUMBER,
    FOREIGN KEY (ManagerId) REFERENCES Employee(SSN)
    ON MODIFY CASCADE
);

Ici, on spécifie que si on change le numéro employé SSN d'un employé qui est manager, on doit propager ce changement vers la table département où ce manager apparaît. Par contre, on n'a pas le droit de supprimer un employé qui est manager; on ne spécifie rien pour ON DELETE. Par défaut c'est un refus de supprimer cet employé.

CREATE INDEX

Les index sont des tables SGBD qui permettent un accès rapide aux lignes sachant la valeur d'une (ou combinaison de) colonnes. Par exemple, un index sur la colonne salary de la table Employee permet d'accélérer la recherche d'employés, connaisant leur salaire.

Les indexes sont créés à la demande de l'utilisateur.

Exemple-1:

 CREATE INDEX EmpInd ON Employee (Salary) ;

Exemple-2: On peut spécifier l'unicité de l'indexe

 CREATE UNIQUE INDEX DeptInd ON Department (name) ASC ;

ASC est là pour spécifier ascending (ordre croissant) ou descending (DESC, ordre décroissant). Par défaut ASC.

Exemple 3: Indexe sur une combinaison de colonnes

 CREATE UNIQUE INDEX EmpInd2 ON Employee (fname, Lname);

C'est aussi une autre façon de spécifier l'unicité (clé candidate).

DROP ET ALTER

DROP

Pour supprimer une table, on écrit simplement (sic)

 DROP TABLE Employee;

La suppression d'un index se fait par

 DROP INDEX index_name ON tbl_name;

ALTER

Exemple 1: La forme la plus simple est de rajouter une colonne.

ALTER TABLE Employee ADD Age INTEGER;

pour rajouter la colonne AGE à la table Employee.

Ou bien, pour supprimer une colonne.

ALTER TABLE Employee DROP COLUMN Age;

Pour modifier une colonne, on écrira

ALTER TABLE Employee ALTER COLUMN Age CHAR(2);

ou

      ALTER TABLE Employee MODIFY COLUMN Age CHAR(2);

le mot COLUMN est parfois obligatoire, parfois non!

CREATE VIEW

Une vue est une table définie par une requête SELECT. Elle peut être interrogée comme une table de base. Ses valeurs ne sont pas explicitement stockées, mais calculée à chaque demande. Une vue est créée par

CREATE VIEW nomDeVue AS
SELECT nomDeColonnes
FROM nomDeTable
WHERE condition

Exemple 1:

CREATE VIEW Veterans AS
SELECT * FROM Employee
WHERE AGE > 50;

On a une nouvelle table, Veterans, de même schéma que la table Employee et qui contient les employés d'âge supérieur à 50.

Exemple 2 : On peut spécifier les colonnes désirées

CREATE VIEW Veterans (VetFname, VetLname, VetDept) AS
SELECT Fname, Lname, Dept FROM Employee
WHERE AGE > 50;

Les colonnes dans SELECT doivent correspondre en nombre à ceux de la vue.

En réalité, les vues ne sont pas stockées physiquement comme les tables de base. ce sont des tables virtuelles. Elles sont matérialisée à la volée (en exécutant le SELECT de création) en cas de besoin.

De ce fait, les mises à jours sont très restreintes sur une vue car celle-ci est virtuelle justement. Voir le cours pour la discussion.

On supprime une vue avec

DROP VIEW Veterans;