LE SQL de A à Z : 7e partie - Création et manipulation des schémas : CREATE, ALTER, DROP

SQLPro

Nous allons maintenant nous intéresser à ce que la norme SQL 2 appelle la création des schémas, c'est-à-dire les objets qui composent une base de données. Tout le monde connait la notion de Table, mais connaissez-vous les « DOMAIN », les « ASSERTION », les « COLLATION » et les « TRANSLATION » ???
Si ces noms ne vous sont pas familiers, nul doute qu'ils vous deviendront connus à la lecture de ce papier !
Au fait, cette partie du SQL s'appelle le DDL pour Data Definition Language. Et saviez-vous qu'aucun de ces ordres ne peut être transactionné ? ♪

- Titre : LE SQL de A à Z : 7e partie - Création et manipulation des schémas : CREATE, ALTER, DROP

- Auteur : SQLPro

- Parution : 16 octobre 2003

- Licence : Copyright ® 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Préambule

- 1. Règles de nommage

- 2. Au début était le néant…

- 2-1. La connexion

- 2-2. La session

- 2-3. Catalogues et shémas

- 3. Créer une nouvelle base de données

- 4. Une question de caractères…

- 4-1. Jeu de caractères

- 4-2. Collation et « translation »

- 4-3. Résumons…

- 5. Types de données et domaines

- 5-1. Les types SQL 2

- 5-1-1. Détails des différents types de données…

- 5-1-2. Typage rapide avec des préfixes

- 5-2. Les nouveaux types SQL 3

- 5-3. Types communs présents dans certains SGBDR des différents éditeurs

- 5-4. Définir des domaines et les utiliser

- 6. Les assertions

- 7. Les tables

- 7-1. Les contraintes de colonnes (verticales)

- 7-1-1. Obligatoire ([NOT] NULL)

- 7-1-2. Valeur par défaut (DEFAULT)

- 7-1-3. Séquence de collation (COLLATE)

- 7-1-4. Clé (PRIMARY KEY)

- 7-1-5. Unicité (UNIQUE)

- 7-1-6. Validation (CHECK)

- 7-1-7. Intégrité référentielle (FOREIGN KEY / REFERENCES)

- 7-2. Les contraintes de table

- 7-2-1. Clé multicolonne (PRIMARY KEY)

- 7-2-2. Unicité globale (UNIQUE)

- 7-2-3. Validation de ligne (CHECK)

- 7-2-4. Integrité référentielle de table (FOREIGN KEY / REFERENCES)

- 7-3. La gestion de l'intégrité référentielle

- 7-3-1. Mode de gestion de la référence, clause MATCH

- 7-3-2. Mode de gestion de l'intégrité clauses ON UPDATE / ON DELETE

- 7-4. Mode de gestion de la déférabilité

- 7-5. Contraintes horizontales ou verticales ?

- 7-6. Alter et Drop

- 7-6-1. Changer le nom ou le type d'une colonne

- 7-6-2. Ajouter ou supprimer la contrainte NULL ou NOT NULL

- 8. Les vues

- 9. Les informations de schéma

- 10. Les index

- 11. Résumé

Préambule

NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page « La base de données exemple ».

Sans doute, allez-vous immédiatement vous intéresser à la syntaxe du CREATE TABLE. C'est pour cela, sans doute, que vous êtes tombé sur cette page… Je vous plains, car vous allez tout droit créer des bases de données boiteuses et rapidement impossibles à maintenir. Si j'avais un conseil à vous donner ce serait le suivant : lisez les paragraphes 2 à 4 avant même de vous plonger, tête baissée dans la création des tables. Il y a tant de choses qui vous faciliteront la vie que je gage que vous m'en serez éternellement reconnaissant…

1 - Règles de nommage

La norme SQL 2 impose un certain nombre de règles concernant les noms des objets d'une base de données.

Un nom d'objet (table, colonne, contrainte, vue…) doit avoir les caractéristiques suivantes :

- ne pas dépasser 128 caractères ;

- commencer par une lettre ;

- comprendre uniquement les caractères suivants [ 'A' .. 'Z'] U ['a' .. 'z'] U [ '0' .. '1'] U [ '_' ] ;

- un nom d'objet ne peut pas être un mot réservé de SQL sauf à être utilisé avec des guillemets ;

- être insensible à la casse.

Voici quelques identifiants normatifs et non…

Exemple 1

VALABLE

INTERDIT

T_CLIENT
xyz
SELECTION
"SELECT"
CLI_NUM
NUM_CLI

01_INFORMATIQUE
_XyZ_
SELECT
MOTDEPLUSDE128CARACTERES...
CLI#
#CLI

ATTENTION : un nom d'objet doit être unique au sein de l'objet qui le contient. Par exemple un nom de table ou de vue doit être unique au sein de la base, un nom de colonne doit être unique au sein de la table ou la vue, etc.

2 - Au début était le néant…

C'est toujours la même histoire… Comment créer une base de données ? En se connectant au serveur de bases de données ! Mais comment se connecter au serveur de base de données ? En créant une base de données dotée au moins d'une connexion et d'un utilisateur avec les privilèges adéquats… Bref, nous entrons de plein front dans le célèbre problème de l'œuf et de la poule…

Heureusement la plupart du temps, les fournisseurs de SGBDR pourvoient leur engin avec un utilisateur, son mot de passe et un certain nombre de bases de données précréés afin de nous faciliter la vie.
Par exemple sur InterBase le nom d'utilisateur et son mot de passe sont « SYSDBA/masterkey ». Sur MS SQL Server, le nom d'utilisateur est « sa » sans mot de passe (et je vous conseille vivement d'en mettre un immédiatement). Sur Oracle, c'est « System/manager » ou bien « Sys/change_on_install ». Sur DB2 c'est « db2admin ». Sur Informix un compte « informix » est créé dans le groupe « informix-admin ». Sous mySQL c'est « mysqladm » qu'il faut utiliser… Ingres est plus restrictif puisqu'il impose à l'utilisateur de donner expressément un nom lors de l'installation. Pour Sybase et ses deux bases, des différences : ASE veut « sa » sans mot de passe, tandis que ASA impose « DBA/SQL » !

En fait tous ces « comptes » sont des connexions au serveur et la plupart du temps correspondent à la fois à la notion de connexion et d'utilisateur. Mais la norme SQL fait une différence entre le concept de connexion et celui d'utilisateur.

2-1 - La connexion

Pour SQL 2, la connexion à un SGBDR prend la syntaxe suivante :

CONNECT TO {DEFAULT | nom_serveur [AS surnom_serveur ]
[USER nom_utilisateur] }

Par exemple l'ordre :

Exemple 2

CONNECT TO DEFAULT

Se connecte au serveur de base de données défini par défaut. La plupart du temps un serveur de bases de données est installé sur une machine dédiée, ce qui fait qu'il n'y a pas d'ambiguïté. En revanche, si plusieurs serveurs sont installés sur la même machine, il faut nommer le serveur.

Exemple 3

CONNECT TO MON_SERVEUR

Se connecte au serveur identifié « MON_SERVEUR ».

Il est même possible de renommer ce serveur :

Exemple 4

CONNECT TO MON_SERVEUR AS SRV1

Qui se connecte à « MON_SERVEUR », mais le surnomme SRV1. Dès lors ce nouveau nom pourra être utilisé dans divers ordres SQL en lieu et place du nom authentique du serveur.

Enfin, il est possible de préciser le nom de l'utilisateur (qui doit exister dans le SGBDR) qui sera associé à la connexion. Sans cette précision, le SGBDR emprunte le nom par défaut implanté par le constructeur.

Exemple 5

CONNECT TO MON_SERVEUR USER FRED_BROUARD

Se connecte au serveur « MON_SERVEUR » en empruntant l'identité FRED_BROUARD comme utilisateur.

Vous l'aurez compris, à une connexion est toujours associée un nom d'utilisateur. Petite précision, un utilisateur SQL est un objet du serveur (ou de la base de données dans certains cas) et se définit aussi par un ordre SQL…

NOTA : la plupart du temps, ce mécanisme est masqué par une interface graphique, ou encore par l'imbrication du serveur de bases de données et de son OS. Par exemple pour SQL Server de Microsoft, cet ordre de connexion s'effectue derrière la boîte de dialogue suivante :

Pour InterBase, c'est la boîte de dialogue suivante qui fait office :

Mais si vous désirez passer des ordres SQL InterBase en vous connectant à une base spécifique et ceci depuis une commande du shell, vous pouvez lancer l'ordre suivant :

Exemple 6 (Interbase)

CONNECT "chemin_vers_fichier_base"
USER "nom_utilisateur" PASSWORD "mot_de_passe"

La norme SQL propose en outre la possibilité de basculer d'une connexion à l'autre (à condition que l'autre existe et soit dormante à l'aide de l'ordre :

SET CONNECTION { DEFAULT | nom_session }

Où nom_session représente une connexion déjà établie et dormante (c'est-à-dire une connexion ouverte, mais qui n'est pas activée par le passage d'ordre SQL…).

Bien entendu il est possible de fermer une connexion en utilisant l'ordre SQL DISCONNECT :

DISCONNECT { DEFAULT | CURRENT | ALL | nom_session}

La lecture de cette syntaxe laisse bien peu d'interprétation et je laisse donc à votre sagacité le soin de comprendre à quoi peuvent bien faire penser de tels mots clefs !

2-2 - La session

Là nous entrons déjà dans une notion plus complète et donc, forcément plus complexe…

Une session au sens de la norme SQL est une connexion activée et possède certains attributs particuliers. Les attributs d'une session sont :

- un identifiant d'autorisation (AUTHORIZATION) ;

- un nom de catalogue (CATALOG) ;

- un nom de schéma (SCHEMA) ;

- un fuseau horaire (TIME ZONE) ;

- un jeu de caractères (CHARACTER SET).

L'identifiant d'autorisation doit impérativement être choisi parmi les mots clefs suivants USER, CURRENT_USER, SESSION_USER et SYSTEM_USER ou bien en donnant un nom d'utilisateur spécifique.
Un catalogue (CATALOG est le mot clef SQL) est une collection de bases de données et peut soit prendre la forme d'un serveur, d'un groupe de serveurs ou bien de « répertoires » de bases de données.
Un schéma n'est autre qu'une base de données, c'est-à-dire son nom. La norme SQL les appelle SCHEMA parce qu'une base de données doit être décrite pour être utilisée, et son architecture (tables, colonnes, vues…) correspond à un modèle de données physique.
Un fuseau horaire (TIME ZONE) n'est autre que l'indication du décalage de l'heure locale par rapport au temps universel (ou UTC Unified Time Coordination). C'est ainsi qu'il permet de gérer les décalages horaires de toutes les zones locales de la planète ce qui est bien pratique lorsque l'on veut développer une application internationale, notamment pour les sites web !
Enfin, le jeu de caractères (CHARACTER SET) permet de définir quel sous-ensemble de symboles est utilisé pour les 256 combinaisons de deux octets qui correspondent à la frappe des caractères. N'oublions pas que même en Europe les jeux de caractères ne sont pas les mêmes d'une nation à l'autre. Par exemple l'alphabet finois ou le tchèque sont dotés de curieux petits accents en forme de cercle ou de croissant, tandis que dans le cyrillique c'est la forme même des lettres qui change… et tout de même, ces gens-là doivent pouvoir s'exprimer ! (notons que le français possède un certain nombre de caractères particuliers comme le « c » avec ça cédille ou l'« e » dans l'« o », présent par exemple dans le mot cœur).

2-3 - Catalogues et shémas

Comme nous venons de le définir, le terme CATALOG permet de définir une collection de bases de données. Il est très différemment implémenté dans les divers SGBDR que proposent les éditeurs. Pour SQL Server la notion de CATALOG se confond avec celle de base de données, tandis que la notion de schéma se confond avec celle de propriétaire. Par exemple en demandant à la machine de nous fournir la liste des bases de données avec la requête suivante, voici ce que le serveur nous propose :

Exemple 7 (SQL Server)

SELECT *
FROM
INFORMATION_SCHEMA.SCHEMATA

CATALOG   SCHEMA SCHEMA DEFAULT_CHARAC  DEFAULT_CHARAC DEFAULT_CHARA
_NAME     _NAME  _OWNER TER_SET_CATALOG TER_SET_SCHEMA CTER_SET_NAME
--------- ------ ------ --------------- -------------- -------------
master    dbo    dbo    master          dbo            iso_1
tempdb    dbo    dbo    master          dbo            iso_1
model     dbo    dbo    master          dbo            iso_1
msdb      dbo    dbo    master          dbo            iso_1
pubs      dbo    dbo    master          dbo            iso_1
Northwind dbo    dbo    master          dbo            iso_1

3 - Créer une nouvelle base de données

Vous trouverez des compléments d'information sur le sujet aux pages 190 à 192 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

Évidemment ce qui vous intéresse le plus c'est d'abord de créer une base de données. C'est-à-dire un SCHEMA !

La norme SQL propose l'ordre de création d'une base de données (pardon, d'un schéma) suivant :

CREATE SCHEMA [ nom_schema ]
[ AUTHORIZATION utilisateur ]
[ DEFAULT CHARACTER SET jeu_de_caractères ]
[ liste_des_objets_du_schéma ]

La liste des objets du schéma n'étant autre que la création des éléments de la base.

Je ne m'étendrai pas sur cette syntaxe ni sur l'ordre SQL 2 de suppression des schémas :

DROP SCHEMA [ nom_schema ] { RESTRICT | CASCADE }

car ces ordres sont très rarement implémentés tels quels dans les SGBDR. En revanche on trouve la plupart du temps un pseudo-ordre SQL « CREATE DATABASE » fort pratique, mais qui n'existe nulle part dans la norme !

Mais si j'ai tenu à vous montrer ceci, c'est que, par défaut, une connexion ou un serveur possède des attributs. Les méconnaitre et notamment en méconnaitre le paramétrage peut vous causer les pires ennuis. C'est pourquoi je vous propose de réfléchir sur trois questions fondamentales :

- Quel est l'ordre de tri de mes données littérales ?

- Comment s'effectue la comparaison entre colonnes contenant des chaînes de caractères notamment si ces dernières sont de types différents (VARCHAR et CHAR en particulier) ?

- Quelle est à tout moment l'heure et la date procurée par le SGBDR et cette heure est-elle modifiée en fonction des saisons ???

Si vous n'avez pas réfléchi à ces problèmes fondamentaux, vous risquez quelques problèmes difficilement surmontables…
Par exemple :

- Comment faire correspondre l'ordre de mes données entre un tableau trié par une routine en C++, Java ou Delphi et le serveur de base de données ?

- Comment faire en sorte de distinguer les deux références suivantes « GFY-12-aj » et « gfy-12-AJ » dans un SELECT si le serveur a été paramétré pour être rendu insensible à la casse ??

- Comment compter exactement la durée d'une intervention technique en heures si entre la déclaration de la panne et la résolution du problème est intervenu un changement d'horaire du fait de l'heure d'été ???

Je ne m'étendrai donc pas plus sur le sujet, car il est assez spécifique aux différents SGBDR de chaque éditeur. Pensez simplement que l'installation par défaut du serveur prôné avec un gadget qui le rend insensible à la casse est une aberration. En effet, autant il est facile de formater des données pour que la comparaison et même la saisie se fassent au bon format, autant lorsque cette insensibilité est activée, le coût de retour en arrière est exorbitant !

Faites donc le test suivant. Créez dans votre base de données la table et les données suivantes :

Exemple 8

CREATE TABLE TEST
(MOT VARCHAR(16))

INSERT INTO TEST VALUES ('Électricité')
INSERT INTO TEST VALUES ('électricité')
INSERT INTO TEST VALUES ('ELECTRICITE')
INSERT INTO TEST VALUES ('electricite')
INSERT INTO TEST VALUES ('electron')
INSERT INTO TEST VALUES ('electeur')
INSERT INTO TEST VALUES ('électeur')

Exécutez donc les requêtes de test suivantes :

Exemple 9

SELECT MOT
FROM   TEST
WHERE  MOT = 'electricite'

En principe vous devriez n'avoir qu'une seule occurrence…

Exemple 10

SELECT MOT
FROM   TEST
ORDER  BY MOT

Là, le folklore est en place !

Puis créez dans votre langage favori un tableau de chaînes de caractères et avec une routine de tri de votre cru, triez-le et comparez les résultats…

Vous êtes horrifié ? Bien fait !… Vous auriez dû me lire avant de vous lancer dans la conception des bases de données relationnelles !!!

Voici ce test effectué sous différentes bases de données :

SELECT MOT
FROM   TEST
WHERE  MOT
= 'electricite'

SELECT MOT
FROM   TEST
ORDER  BY MOT

Sous MS SQL Server 7, paramétrage par défaut

MOT
-----------
ELECTRICITE
electricite

MOT
-----------
electeur
électeur
ELECTRICITE
Électricité
électricité
electricite
electron

Sous PostGreSQL 7.1.2, paramétrage par défaut

mot
-----------
electricite

mot
-------------
electeur
électeur
electricite
ELECTRICITE
électricité
Électricité
electron

Sous InterBase 5.5, paramétrage par défaut

MOT
===========
electricite

MOT
===========
electricite

Sous paradox 9, paramétrage par défaut

MOT
***********
electricite

MOT
***********
electricite

MySQL 3.23.37, paramétrage par défaut

+-------------+
| MOT         |
+-------------+
| Électricité |
| électricité |
| ELECTRICITE |
| electricite |
+-------------+

+-------------+
| MOT         |
+-------------+
| electeur    |
| électeur    |
| Électricité |
| électricité |
| ELECTRICITE |
| electricite |
| electron    |
+-------------+

4 - Une question de caractères…

4-1 - Jeu de caractères

Je vais encore vous embêter avec un concept qui, la plupart du temps est ignoré ou passé sous silence, mais qui permet de se sauver de situations inextricables portant sur la comparaison des littéraux. La norme SQL a prévu, outre le jeu de caractères, l'utilisation de séquences de collation. Même si elles ne sont pas encore parfaitement implantées telles que la norme SQL 2 l'a prévu, il est rare qu'un mécanisme similaire ne soit pas fourni dans votre SGBDR.

Le jeu de caractères consiste en une correspondance des 256 ou 65536 caractères par rapport aux symboles graphiques représentés.
256 correspond au type CHAR et VARCHAR et 65536 aux types NATIONAL CHAR ou NATIONAL VARCHAR. La plupart du temps nous utilisons sans le savoir un jeu de caractères basique établi par défaut dans la version locale de l'OS.

Voici par exemple les jeux de caractères disponibles dans les créations de tables de Paradox :

Exemple 11

ANSI 1250

tchèque, hongrois polonais, slovène

ANSI 1251

cyrillique et bulgare

ANSI 1252

nordique, espagnol, suédois, finlandais

ANSI 1253

grec

ANSI 1254

turc

ANSI 1255

binaire

DOS 437

Europe de l'Ouest, suédois, finlandais, espagnol

DOS 737

grec

DOS 850

brésilien, portugais, canadien, français

DOS 852

tchèque, hongrois polonais, slovène

DOS 857

turc

DOS 861

islandais

DOS 862

binaire

DOS 865

norvégien, danois

DOS 866

cyrillique

DOS 867

tchèque

DOS 868

bulgare

DOS 874

thaïlandais

DOS 932

japonais

DOS 936

chinois

DOS 949

coréen

DOS 950

tawainais

Notez qu'ils sont décomposés en deux familles, celle du DOS (la table des caractères de base n'étant pas normalisée, mais propre à l'inventeur du système d'exploitation DOS…) et celle de l'ANSI (relatif à Windows qui se base sur la norme ANSI - American National Standard Institute).

On parle souvent de table ASCII. L'ASCII a été créé par BEMMER en 1965 , produit par le groupe de travail X3.4 de l'ANSI, certifié en 1977 et adopté par l'ISO en 1968 sous le n° 646 [f2s]. Ce ne sont en fait que les 128 premiers caractères du fait du codage à l'origine sur 7 bits. Lorsque le codage des caractères a été étendu par nécessité à 8 bits, des déclinaisons de cette table ont été possibles pour régler le problème des caractères diacritiques (accents, cédilles, caractères spéciaux tels que le double ss allemand).

Rassurez-vous l'ISO (International Standard Organisation) s'en est mêlé et propose des jeux de caractères plus cohérents. Voici par exemple la table iso8859-1, actuellement la plus répandue et la plus utilisée notamment sur le NET :

Exemple 12

iso8859-1 character table and corresponding HTML code

Description                               Code            Charactèr name
===================================       ============    ==============
guillemet                                 &#34;  --> "    &quot;   --> "
et commercial                             &#38;  --> &    &amp;    --> &
signe plus petit que                      &#60;  --> <    &lt;     --> <
greater-than sign                         &#62;  --> >    &gt;     --> >

Description                          Char Code            Entity name
===================================  ==== ============    ==============
espace non sécable                        &#160; -->      &nbsp;   -->
exclamation inverse                  ¡    &#161; --> ¡    &iexcl;  --> ¡
centimes                             ¢    &#162; --> ¢    &cent;   --> ¢
livre sterling                       £    &#163; --> £    &pound;  --> £
symbole monétère général             ¤    &#164; --> ¤    &curren; --> ¤
symbole du yen                       ¥    &#165; --> ¥    &yen;    --> ¥
barre verticale brisée               ¦    &#166; --> ¦    &brvbar; --> ¦
&brkbar; --> &brkbar;
paragraphe                           §    &#167; --> §    &sect;   --> §
umlaut (dieresis)                    ¨    &#168; --> ¨    &uml;    --> ¨
&die;    --> &die;
copyright                            ©    &#169; --> ©    &copy;   --> ©
feminine ordinal                     ª    &#170; --> ª    &ordf;   --> ª
guillement gauche                    «    &#171; --> «    &laquo;  --> «
not sign                             ¬    &#172; --> ¬    &not;    --> ¬
soft hyphen                              &#173; -->    &shy;    -->
registered trademark                 ®    &#174; --> ®    &reg;    --> ®
macron accent                        ¯    &#175; --> ¯    &macr;   --> ¯
&hibar;  --> &hibar;
signe des degrés                     °    &#176; --> °    &deg;    --> °
plus ou moins                        ±    &#177; --> ±    &plusmn; --> ±
puissance carrée                     ²    &#178; --> ²    &sup2;   --> ²
puissance cubique                    ³    &#179; --> ³    &sup3;   --> ³
accent aigu                          ´    &#180; --> ´    &acute;  --> ´
symbole micro                        µ    &#181; --> µ    &micro;  --> µ
symbole de paragraphe                ¶    &#182; --> ¶    &para;   --> ¶
point central                        ·    &#183; --> ·    &middot; --> ·
cédille                              ¸    &#184; --> ¸    &cedil;  --> ¸
exposant un                          ¹    &#185; --> ¹    &sup1;   --> ¹
masculine ordinal                    º    &#186; --> º    &ordm;   --> º
guillement droit                     »    &#187; --> »    &raquo;  --> »
un quart (fraction)                  ¼    &#188; --> ¼    &frac14; --> ¼
un demi (fraction)                   ½    &#189; --> ½    &frac12; --> ½
trois quart (fraction)               ¾    &#190; --> ¾    &frac34; --> ¾
point d'interrogation inversé        ¿    &#191; --> ¿    &iquest; --> ¿
A accent grave (majuscule)           À    &#192; --> À    &Agrave; --> À
A accent aigu (majuscule)            Á    &#193; --> Á    &Aacute; --> Á
A accent circonflexe (majuscule)     Â    &#194; --> Â    &Acirc;  --> Â
A tilde (majuscule)                  Ã    &#195; --> Ã    &Atilde; --> Ã
capital A, dieresis or umlaut mark   Ä    &#196; --> Ä    &Auml;   --> Ä
capital A, ring                      Å    &#197; --> Å    &Aring;  --> Å
capital AE diphthong (ligature)      Æ    &#198; --> Æ    &AElig;  --> Æ
capital C, cedilla                   Ç    &#199; --> Ç    &Ccedil; --> Ç
capital E, grave accent              È    &#200; --> È    &Egrave; --> È
capital E, acute accent              É    &#201; --> É    &Eacute; --> É
capital E, circumflex accent         Ê    &#202; --> Ê    &Ecirc;  --> Ê
capital E, dieresis or umlaut mark   Ë    &#203; --> Ë    &Euml;   --> Ë
capital I, grave accent              Ì    &#204; --> Ì    &Igrave; --> Ì
capital I, acute accent              Í    &#205; --> Í    &Iacute; --> Í
capital I, circumflex accent         Î    &#206; --> Î    &Icirc;  --> Î
capital I, dieresis or umlaut mark   Ï    &#207; --> Ï    &Iuml;   --> Ï
capital Eth, Icelandic               Ð    &#208; --> Ð    &ETH;    --> Ð
&Dstrok; --> &Dstrok;
capital N, tilde                     Ñ    &#209; --> Ñ    &Ntilde; --> Ñ
capital O, grave accent              Ò    &#210; --> Ò    &Ograve; --> Ò
capital O, acute accent              Ó    &#211; --> Ó    &Oacute; --> Ó
capital O, circumflex accent         Ô    &#212; --> Ô    &Ocirc;  --> Ô
capital O, tilde                     Õ    &#213; --> Õ    &Otilde; --> Õ
capital O, dieresis or umlaut mark   Ö    &#214; --> Ö    &Ouml;   --> Ö
multiply sign                        ×    &#215; --> ×    &times;  --> ×
capital O, slash                     Ø    &#216; --> Ø    &Oslash; --> Ø
capital U, grave accent              Ù    &#217; --> Ù    &Ugrave; --> Ù
capital U, acute accent              Ú    &#218; --> Ú    &Uacute; --> Ú
capital U, circumflex accent         Û    &#219; --> Û    &Ucirc;  --> Û
capital U, dieresis or umlaut mark   Ü    &#220; --> Ü    &Uuml;   --> Ü
capital Y, acute accent              Ý    &#221; --> Ý    &Yacute; --> Ý
capital THORN, Icelandic             Þ    &#222; --> Þ    &THORN;  --> Þ
small sharp s, German (sz ligature)  ß    &#223; --> ß    &szlig;  --> ß
small a, grave accent                à    &#224; --> à    &agrave; --> à
small a, acute accent                á    &#225; --> á    &aacute; --> á
small a, circumflex accent           â    &#226; --> â    &acirc;  --> â
small a, tilde                       ã    &#227; --> ã    &atilde; --> ã
small a, dieresis or umlaut mark     ä    &#228; --> ä    &auml;   --> ä
small a, ring                        å    &#229; --> å    &aring;  --> å
small ae diphthong (ligature)        æ    &#230; --> æ    &aelig;  --> æ
small c, cedilla                     ç    &#231; --> ç    &ccedil; --> ç
small e, grave accent                è    &#232; --> è    &egrave; --> è
small e, acute accent                é    &#233; --> é    &eacute; --> é
small e, circumflex accent           ê    &#234; --> ê    &ecirc;  --> ê
small e, dieresis or umlaut mark     ë    &#235; --> ë    &euml;   --> ë
small i, grave accent                ì    &#236; --> ì    &igrave; --> ì
small i, acute accent                í    &#237; --> í    &iacute; --> í
small i, circumflex accent           î    &#238; --> î    &icirc;  --> î
small i, dieresis or umlaut mark     ï    &#239; --> ï    &iuml;   --> ï
small eth, Icelandic                 ð    &#240; --> ð    &eth;    --> ð
small n, tilde                       ñ    &#241; --> ñ    &ntilde; --> ñ
small o, grave accent                ò    &#242; --> ò    &ograve; --> ò
small o, acute accent                ó    &#243; --> ó    &oacute; --> ó
small o, circumflex accent           ô    &#244; --> ô    &ocirc;  --> ô
small o, tilde                       õ    &#245; --> õ    &otilde; --> õ
small o, dieresis or umlaut mark     ö    &#246; --> ö    &ouml;   --> ö
division sign                        ÷    &#247; --> ÷    &divide; --> ÷
small o, slash                       ø    &#248; --> ø    &oslash; --> ø
small u, grave accent                ù    &#249; --> ù    &ugrave; --> ù
small u, acute accent                ú    &#250; --> ú    &uacute; --> ú
small u, circumflex accent           û    &#251; --> û    &ucirc;  --> û
small u, dieresis or umlaut mark     ü    &#252; --> ü    &uuml;   --> ü
small y, acute accent                ý    &#253; --> ý    &yacute; --> ý
small thorn, Icelandic               þ    &#254; --> þ    &thorn;  --> þ
small y, dieresis or umlaut mark     ÿ    &#255; --> ÿ    &yuml;   --> ÿ

On trouvera diverses informations sur le sujet dans les pages web suivantes :
http://alis.isoc.org/codage/iso8859/jeuxiso.htm
http://www.ping.be/~ping9985/html/iso8859-.htm
http://www.w3.org/MarkUp/html-spec/html-spec_9.html#SEC9.7.2

Autrement dit, le jeu de caractères restreint à 256 symboles ASCII les possibilités de combiner les lettres, signes de ponctuation et autres caractères particuliers imprimables.

4-2 - Collation et « translation »

La séquence de collation est un complément indispensable du jeu de caractères et permet de définir non seulement la position ordinale de chaque caractère, mais leur éventuelle « confusion » ! Hé oui, comment pensez-vous que la machine puisse devenir insensible à la casse et aux accents sans un tel mécanisme ?
C'est ainsi que différentes séquences de collation sont possibles sur un même jeu de caractères.

Voici par exemple les séquences de collation des différents jeux de caractères de Paradox :

Exemple 12

Jeu de caractères

Classement des caractères

1250 (ANSI)

Compatible avec Paradox « czech » (niveau 2)
Compatible avec Paradox « hung » (niveau 2)
Paradox Czech (niveau 2)
Compatible avec Paradox « polish » (niveau 2)
Compatible avec Paradox « slovene » (niveau 2)

1251 (ANSI)

Compatible avec Paradox « cyrr » (2-level)
Bulgarian (2-level)

1252 (ANSI)

Compatible avec Paradox « intl » (niveau 3)
Compatible avec Paradox « nordan40 » (niveau 2)
Compatible avec Paradox « SPANISH » (niveau 3)
Compatible avec Paradox « swedfin » (niveau 3)

1253 (ANSI)

Compatible avec Paradox « greek » (niveau 2)

1254 (ANSI)

Compatible avec Paradox « turk » (niveau 2)

1255 (ANSI)

Binaire

DOS CODE PAGE 437

Binaire
Multilingual Western Europe
Swedish/Finnish
Spanish (niveau 3)

DOS CODE PAGE 737

Greek (niveau 2)

DOS CODE PAGE 850

Brazilian Portuguese, French Canadian
Compatible avec Paradox « intl850 » (niveau 3)

DOS CODE PAGE 852

Czech852 (niveau 2)
Hungarian (niveau 2)
Polish (niveau 2)
Slovene (niveau 2)

DOS CODE PAGE 857

Turkish (niveau 2)

DOS CODE PAGE 861

Icelandic (niveau 2)

DOS CODE PAGE 862

Binaire

DOS CODE PAGE 865

Norwegian/Danish (Paradox 3.5)
Norwegian/Danish (Paradox 4.0, 5.0, 5.5, 7.0)

DOS CODE PAGE 866

Cyrillic

DOS CODE PAGE 867

Czech867 (niveau 2)

DOS CODE PAGE 868

Bulgaria (niveau 2)

DOS CODE PAGE 874

Thai (niveau 3)

DOS CODE PAGE 932

Japanese

DOS CODE PAGE 936

China (niveau 1)

DOS CODE PAGE 949

Korea (niveau 1)

DOS CODE PAGE 950

Taiwan (niveau 1)

Par exemple dans la séquence de collation ISO 1252 spanish, le « ñ » (« n » avec tilde) sera confondu avec le caractère « n » lors des comparaisons partielles…

Pour InterBase par exemple, les jeux de caractères disponibles sont les suivants :

Exemple 14

SELECT     RDB$CHARACTER_SET_NAME,
RDB$CHARACTER_SET_ID,
RDB$DEFAULT_COLLATE_NAME
FROM       RDB$CHARACTER_SETS

RDB$CHARACTER_SET_NAME RDB$CHARACTER_SET_ID RDB$DEFAULT_COLLATE_NAME
====================== ==================== ========================

NONE                                      0 NONE
OCTETS                                    1 OCTETS
ASCII                                     2 ASCII
UNICODE_FSS                               3 UNICODE_FSS
SJIS_0208                                 5 SJIS_0208
EUCJ_0208                                 6 EUCJ_0208
DOS437                                   10 DOS437
DOS850                                   11 DOS850
DOS865                                   12 DOS865
ISO8859_1                                21 ISO8859_1
DOS852                                   45 DOS852
DOS857                                   46 DOS857
DOS860                                   13 DOS860
DOS861                                   47 DOS861
DOS863                                   14 DOS863
CYRL                                     50 CYRL
WIN1250                                  51 WIN1250
WIN1251                                  52 WIN1251
WIN1252                                  53 WIN1252
WIN1253                                  54 WIN1253
WIN1254                                  55 WIN1254
NEXT                                     19 NEXT
KSC_5601                                 44 KSC_5601
BIG_5                                    56 BIG_5
GB_2312                                  57 GB_2312

Auxquels sont associées les séquences de collations suivantes :

Exemple 15

SELECT     RDB$COLLATION_NAME,
RDB$COLLATION_ID,
RDB$CHARACTER_SET_ID
FROM       RDB$COLLATIONS C
JOIN RDB$CHARACTER_SETS S
ON C.RDB$CHARACTER_SET_ID =
S.RDB$CHARACTER_SET_ID

RDB$COLLATION_NAME    RDB$COLLATION_ID  RDB$CHARACTER_SET_ID
==================== ================ ====================

NONE                                0                    0
OCTETS                              0                    1
ASCII                               0                    2
UNICODE_FSS                         0                    3
SJIS_0208                           0                    5
EUCJ_0208                           0                    6
DOS437                              0                   10
PDOX_ASCII                          1                   10
PDOX_INTL                           2                   10
PDOX_SWEDFIN                        3                   10
DB_DEU437                           4                   10
DB_ESP437                           5                   10
DB_FIN437                           6                   10
DB_FRA437                           7                   10
DB_ITA437                           8                   10
DB_NLD437                           9                   10
DB_SVE437                          10                   10
DB_UK437                           11                   10
DB_US437                           12                   10
DOS850                              0                   11
DB_FRC850                           1                   11
DB_DEU850                           2                   11
DB_ESP850                           3                   11
DB_FRA850                           4                   11
DB_ITA850                           5                   11
DB_NLD850                           6                   11
DB_PTB850                           7                   11
DB_SVE850                           8                   11
DB_UK850                            9                   11
DB_US850                           10                   11
DOS865                              0                   12
PDOX_NORDAN4                        1                   12
DB_DAN865                           2                   12
DB_NOR865                           3                   12
ISO8859_1                           0                   21
DA_DA                               1                   21
DU_NL                               2                   21
FI_FI                               3                   21
FR_FR                               4                   21
FR_CA                               5                   21
DE_DE                               6                   21
IS_IS                               7                   21
IT_IT                               8                   21
NO_NO                               9                   21
ES_ES                              10                   21
SV_SV                              11                   21
EN_UK                              12                   21
EN_US                              14                   21
PT_PT                              15                   21
DOS852                              0                   45
DB_CSY                              1                   45
DB_PLK                              2                   45
DB_SLO                              4                   45
PDOX_CSY                            5                   45
PDOX_PLK                            6                   45
PDOX_HUN                            7                   45
PDOX_SLO                            8                   45
DOS857                              0                   46
DB_TRK                              1                   46
DOS860                              0                   13
DB_PTG860                           1                   13
DOS861                              0                   47
PDOX_ISL                            1                   47
DOS863                              0                   14
DB_FRC863                           1                   14
CYRL                                0                   50
DB_RUS                              1                   50
PDOX_CYRL                           2                   50
WIN1250                             0                   51
PXW_CSY                             1                   51
PXW_HUNDC                           2                   51
PXW_PLK                             3                   51
PXW_SLOV                            4                   51
WIN1251                             0                   52
PXW_CYRL                            1                   52
WIN1252                             0                   53
PXW_INTL                            1                   53
PXW_INTL850                         2                   53
PXW_NORDAN4                         3                   53
PXW_SPAN                            4                   53
PXW_SWEDFIN                         5                   53
WIN1253                             0                   54
PXW_GREEK                           1                   54
WIN1254                             0                   55
PXW_TURK                            1                   55
NEXT                                0                   19
NXT_US                              1                   19
NXT_DEU                             2                   19
NXT_FRA                             3                   19
NXT_ITA                             4                   19
NXT_ESP                             5                   19
KSC_5601                            0                   44
KSC_DICTIONARY                      1                   44
BIG_5                               0                   56
GB_2312                             0                   57

Comment cela se passe à l'intérieur et à quoi cela correspond-il ? Jetons un coup d'œil à la procédure stockée sp_helpsort de MS SQL Server et tout va devenir plus lumineux…

Exemple 15

sp_helpsort

Unicode data sorting
----------------------
Locale ID = 1033
case insensitive, kana type insensitive, width insensitive

Sort Order Description
------------------------------------------------------------------
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several
Western-European languages including English, French, and German.
Uses the ISO 8859-1 character set.

Characters, in Order
-------------------------------------------------------------------
! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }
~   ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾
¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å Æ=æ B=b C
=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í Î=î Ï=ï J
=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø P=p Q=q R=r S
=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z Ð=ð Þ=þ

Comme on le voit, l'ordre de chacun des caractères est établi par la séquence, mais comme cette séquence de collation est rendue insensible à la casse, il y a confusion entre les majuscules et les minuscules…

Autrement dit, l'ordre de tri, comme la correspondance des recherches que l'on effectuera dans des requêtes SQL sera prédéfini par la séquence de collation. La méconnaitre et méconnaitre les possibilités de paramétrage de son SGBDR à ce niveau, c'est laisser faire Microsoft, Oracle ou Borland, comme il l'entend et non comme vous voulez que ce soit et rejaillira tôt ou tard comme problématique dans la mise au point de vos applications…

Sachez qu'en général, une séquence de collation par défaut est proposée lors de l'installation de votre serveur, et que sur certains SGBDR on peut modifier la séquence de collation pour la table entière ou encore pour une colonne seulement.
Bien entendu la norme SQL 2 a codifié et pensé cela depuis fort longtemps et propose même des mécanismes de traduction afin de faire correspondre des données venant de jeux et collations différentes, mais ces possibilités sont encore trop rares à être implémentées au sein des SGBDR des différents éditeurs. Ainsi SQL 2 a défini les CREATE CHARACTER SET et CREATE COLLATION afin de répondre à toutes les attentes. Malheureusement la plupart du temps il n'est possible d'utiliser que les jeux et collations prédéfinis par l'éditeur du SGBDR.
Par exemple Microsoft dans la version 7 de sa base de données SQL Server était très en retard sur le sujet. Un seul jeu de caractères et une seule séquence de collation pouvaient être utilisés au sein du serveur ! Comme le serveur était mono-instance, il fallait autant de serveurs (machines) que d'applications pour les différents pays d'exploitation de la base de données, ce qui rendait SQL Server difficile comme choix pour des applications Internet internationales, sauf à utiliser quelques « trucs » et « bouts de ficelles » à base d'UNICODE… En revanche, de ce côté, un modeste serveur comme InterBase était fort bien pensé !

Par exemple pour InterBase, lors de la création d'une base de données, on peut préciser le jeu de caractères :

Exemple 17

CREATE DATABASE 'production.gdb'
DEFAULT CHARACTER SET 'ISO8859_1'

On peut aussi le faire pour chaque colonne dans la création de table :

Exemple 18

CREATE TABLE TEST_CHAR_SET
(COL1 CHAR(32) CHARACTER SET ASCII,
COL2 CHAR(32) CHARACTER SET DOS437,
COL3 CHAR(32) CHARACTER SET DOS850)

Et préciser en outre la collation utilisée :

Exemple 19

CREATE TABLE TEST_COLLATION
(COL1 CHAR(32) CHARACTER SET ISO8859_1 COLLATE FR_FR,
COL2 CHAR(32) CHARACTER SET ISO8859_1 COLLATE ISO8859_1)

Mais attention, pas question de rentrer n'importe quoi dans la table, le serveur veille en général à la correspondance des jeux dans les entrées/sortie de flux d'informations (requêtes et ensemble de résultats).

Ainsi en alimentant la table TEST_COLLATION avec les mêmes données pour les deux colonnes…

Exemple 20

INSERT INTO TEST_COLLATION VALUES ('Électricité', 'Électricité')
INSERT INTO TEST_COLLATION VALUES ('électricité', 'électricité')
INSERT INTO TEST_COLLATION VALUES ('ELECTRICITE', 'ELECTRICITE')
INSERT INTO TEST_COLLATION VALUES ('electricite', 'electricite')
INSERT INTO TEST_COLLATION VALUES ('electron', 'electron')
INSERT INTO TEST_COLLATION VALUES ('electeur', 'electeur')
INSERT INTO TEST_COLLATION VALUES ('électeur', 'électeur')

…nous n'obtiendrons pas le même ordre de tri :

Exemples 21 et 22

SELECT COL1
FROM TEST_COLLATION
ORDER BY COL1

COL1
===========
electeur
électeur
electricite
ELECTRICITE
électricité
Électricité
electron

SELECT COL2
FROM TEST_COLLATION
ORDER BY COL2

COL2
===========
ELECTRICITE
electeur
electricite
electron
Électricité
électeur
électricité

NOTA : pour pouvoir utiliser ce jeu d'essais dans votre interface de requêtage vous devez préalablement indiquer au serveur quand vous utilisez le jeu de caractères ISO8859_1 en utilisant la commande SET NAMES d'InterBase ou le paramétrage avancé.

Bien entendu, on peut forcer une séquence de collations dans un select à des fins de comparaison ou de tri. Pour cela il faut utiliser le mot clef COLLATE et donner le nom de la collation de travail :

Exemples 23 et 24

SELECT COL1
FROM   TEST_COLLATION
WHERE  COL1 COLLATE EN_US = 'électricité'

COL1
===========
électricité

SELECT COL1
FROM   TEST_COLLATION
ORDER  BY COL1 COLLATE EN_US

COL1
===========
electeur
electricite
ELECTRICITE
electron
Électricité
électeur
électricité

C'est pourquoi on ne le répétera jamais assez, ce paramétrage entre jeu de caractères et séquence de collation est délicat et obéit souvent à des mécanismes souvent peu normatifs.

NOTA : Peter GULUTZAN a écrit deux bons articles sur la problématique des séquences de collation que l'on trouvera sur le site de dbazine.com à l'url :
http://dbazine.com/gulutzen1.html
http://dbazine.com/gulutzen1a.html

Enfin, je ne saurais trop vous recommander, dans la mesure où vous le pouvez, de paramétrer votre base de données de la manière suivante :

- jeu de caractères ISO8859-1 ;

- sensible à la casse (donc différences entre majuscules et minuscules) ;

- sensible aux accents et autres caractères diacritiques.

En effet, on peut toujours jouer sur les séquences de collation pour redéfinir l'ordre de tri et utiliser des fonctions de conversion telles que UPPER ou LOWER pour insensibiliser la casse, ou encore des fonctions avancées telles que la fonction TRANSLATION proposée par ORACLE pour s'affranchir des accents et autres caractères diacritiques. N'oubliez pas qu'une séquence de collation complexe (par exemple assimilant les majuscules, minuscules et accents comme équivalents) est forcément couteuse en temps d'exécution et cela rejaillira sur les performances d'accès et de traitement de vos données…

La translation, rarement implémentée consiste à définir un ensemble de correspondances entre caractères. Elle permet de remplacer des caractères par d'autres. Par exemple si nous avons créé une translation nommée REMPLACE_PONCTUATION_ESPACE qui définit que les caractères . , ; : ! ? ' " sont remplacés par un caractère espace, alors l'application de la fonction TRANSLATE avec comme paramètre cet identifiant de translation nous obtiendrons une chaîne de caractères dans laquelle tous les signes de ponctuation mentionnés seront remplacés par des espaces.

Exemples 25

TRANSLATE ('A, B; C>D?' USING REMPLACE_PONCTUATION_ESPACE)

'A  B  C>D '

4-3 - Résumons…

La norme prévoit la spécification d'un jeu de caractères à l'aide des ordres :

CREATE CHARACTER SET nom_jeu_caractère
[AS] GET nom_jeu_caractère_existant
[COLLATE nom_collation | COLLATION FROM ressource_collation]

DROP CHARACTER SET nom_jeu_caractère

La norme prévoit la spécification d'une collation à l'aide des ordres :

CREATE COLLATION nom_collation
FOR nom_jeu_caractère_existant
FROM  ressource_collation
[ PAD SPACE | NO PAD ]

DROP COLLATION nom_collation

Dans les deux cas (CREATE CHARCATER SET et CREATE COLATION), une ressource de collation pouvant elle-même être définie comme :

ressource_collation ::
EXTERNAL ( 'collation_externe' )
nom_collation
DESC (nom_collation)
DEFAULT
TRANSLATION nom_traduction [THEN COLLATION nom_colation]

La norme prévoit la spécification d'une translation à l'aide des ordres :

CREATE TRANSLATION nom_translation
FOR jeu_caractere_source
TO jeu_caractère_cible
FROM { EXTERNAL ( 'translation_externe' ) | IDENTITY | nom_translation }

DROP TRANSLATION nom_translation

Une collation peut être spécifiée, caractère par caractère à l'aide d'opérateurs de comparaison.

Exemple 26

a=A a=à a=â a=ä b=B c=C c=ç d=D e=E e=é e=è ...

Dans ce cas, les caractères a, A, à auront la position ordinale 1, b ou B la position ordinale 2, etc., et cette séquence de collation sera insensible à la casse et aux caractères diacritiques…

Le jeu de caractères peut se spécifier dans la création de la base de données :

CREATE SCHEMA nom_base
...
[DEFAULT CHARACTER SET nom_jeu_caractère]

Enfin, une collation peut être utilisée selon la norme SQL 2 dans une comparaison comme dans la clause de tri d'un ordre SELECT.

NOTA : l'implémentation de ces différents éléments varie fortement d'un éditeur de SGBDR à l'autre. C'est pourquoi je vous invite fortement à lire la documentation de votre SGBDR avant de définir votre base et vos tables.

5 - Types de données et domaines

Vous trouverez des compléments d'information sur le sujet aux pages 36 à 46 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

Nous avons déjà parlé de ces éléments si vous avez lu le tout premier papier consacré à SQL. Mais comme rien ne vaut un bon rappel, voici ce que sont les domaines et les données de SQL.

5-1 - Les types SQL 2

La norme SQL 2 propose quatre familles de types de données : les littéraux, les numériques, les binaires et les temporelles, elles-mêmes parfois subdivisées.
Le tableau suivant résume les différents types de données. En vert figurent les types SQL 2 et en blanc les familles et sous-familles :

n précise la longeur associée au type, p la précision (nombre de chiffres significatifs), s les décimales et i1 et i2 la précision de durée parmi YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

5-1-1 - Détails des différents types de données…

CHARACTER (n) permet de préciser une donnée littérale (chaîne de caractères) dont les caractères sont codés sur un jeu de type ASCI, EBCDIC ou DOS (2 octets par caractères) de longueur fixe n. Si la donnée à insérer est de taille inférieure, elle est complétée par des blancs significatifs.

Exemple 27

CHARACTER (4)

définit une colonne comportant exactement 4 caractères.
Le synonyme CHAR peut être utilisé.

CHARACTER VARYING (n) permet de préciser une donnée littérale (chaîne de caractères) dont les caractères sont codés sur un jeu de type ASCI, EBCDIC ou DOS de longueur maximale n. Si la donnée à insérer est de taille inférieure elle est compactée et aucun caractère blanc significatif ne la complète.

Exemple 28

CHARACTER VARYING (32)

définit une colonne comportant au maximum 32 caractères.
Le synonyme VARCHAR peut être utilisé

NATIONAL CHARACTER et NATIONALCHARACTERVARYING sont des déclinaisons des deux exemples précédents, mais codés sur le jeu UNICODE, ce qui suppose 4 octets par caractère.
Les synonymes NATIONAL CHAR, NCHAR, NATIONAL CHAR VARYING et NATIONAL VARCHAR peuvent être utilisés.

DECIMAL est un réel représentant un nombre en base 10 sans altération de la précision des chiffres situés après la virgule. La précision p peut aller au-delà du maximum prévu pour le traitement des données numériques du processeur.

Exemple 29

DECIMAL (60, 16)

définit une colonne comportant un réel acceptant jusqu'à 60 chiffres significatifs avec 16 chiffres après la virgule. Aucune erreur d'arrondi ne doit apparaître dans le stockage ni les calculs décimaux.

NUMERIC est un réel représentant un nombre en base 10 sans altération de la précision des chiffres situés après la virgule. La précision p ne peut dépasser le maximum prévu pour le traitement des données numérique du processeur.

Exemple 30

DECIMAL (16, 2)

définit une colonne comportant un réel acceptant jusqu'à 16 chiffres significatifs avec 2 chiffres après la virgule. Aucune erreur d'arrondi ne doit apparaître dans le stockage ni les calculs décimaux.

FLOAT est un réel représentant un nombre en base 10 stocké en binaire. La précision p peut aller au-delà du maximum prévu pour le traitement des données numériques du processeur.

Exemple 31

FLOAT (64, 16)

définit une colonne comportant un réel codé en binaire acceptant jusqu'à 64 chiffres significatifs avec 16 chiffres après la virgule. Des erreurs d'arrondi peuvent apparaître dans le stockage ou les calculs décimaux.

REAL est un réel représentant un nombre en base 10 stocké en binaire. La précision p ne peut dépasser le maximum prévu pour le traitement des données numériques du processeur sur un nombre de bits déterminé par le système.

Exemple 32

REAL (12, 4)

définit une colonne comportant un réel codé en binaire acceptant jusqu'à 12 chiffres significatifs avec 4 chiffres après la virgule. Des erreurs d'arrondi peuvent apparaître dans le stockage ou les calculs décimaux.

DOUBLE PRECISION est un réel représentant un nombre en base 10 stocké en binaire. La précision p ne peut dépasser le maximum prévu pour le traitement des données numérique du processeur sur un nombre de bits déterminé par le système et plus largement que le REAL.

Exemple 33

DOUBLE PRECISION (18, 6)

définit une colonne comportant un réel codé en binaire acceptant jusqu'à 18 chiffres significatifs avec 6 chiffres après la virgule. Des erreurs d'arrondi peuvent apparaître dans le stockage ou les calculs décimaux.

SMALLINT est un entier représentant un nombre en base 10 stocké en binaire. La précision est fixe et déterminée par le processeur pour un demi-mot.

Exemple 34

SMALLINT

définit une colonne comportant un entier codé en binaire dont la plage de valeurs est fixée dans l'étendue des capacités du demi-mot du processeur. Les calculs et le stockage sont toujours exacts.

INTEGER est un entier représentant un nombre en base 10 stocké en binaire. La précision est fixe et déterminée par le processeur pour un mot entier.

Exemple 35

INTEGER

définit une colonne comportant un entier codé en binaire dont la plage de valeurs est fixée dans l'étendue des capacités du mot entier du processeur. Les calculs et le stockage sont toujours exacts.

BIT définit une chaîne de bits dont la longueur est fixe.

Exemple 36

BIT(2)

définit une colonne comportant deux bits pouvant avoir les valeurs 00 01 10 ou 11.

BIT VARYING définit une chaîne de bits dont la longueur est variable.

Exemple 37

BIT(8)

définit une colonne comportant au maximum 8 bits pouvant prendre la plage de valeurs allant de 0 à 11111111.

TIMESTAMP combine date et heure avec une précision du millième de seconde. On peut préciser le fuseau horaire avec l'option TIME ZONE. La norme SQL 2 précise que l'étendue de ce type de données doit aller du premier janvier de l'an 1 à zéro heure jusqu'au 31 décembre de l'an 9999 à 23h59m59s999.

Exemple 38

TIMESTAMP

L'option TIME ZONE permet de définir le décalage horaire par rapport à l'heure universelle (UTC) :

Exemple 39

TIMESTAMP WITH TIME ZONE

Par exemple en été en France, où le décalage horaire est de +2 heures par rapport au temps universel, nous pourrions stocker des données sous la forme :

Exemple 40

CAST('1999-11-20 16:28:33-02:00' AS TIMESTAMP WITH TIME ZONE)

DATE permet de stoker des dates dans l'étendue premier janvier de l'an 1 jusqu'au 31 décembre de l'an 9999.

Exemple 41

DATE

TIME permet de stocker des heures dans l'étendue de 00:00:00.000 h à 23:59:59.999 h. Comme dans le cas du TIMESTAMP, l'option TIME ZONE permet de définir le décalage horaire par rapport à l'heure universelle (UTC) :

Exemple 42

TIME WITH TIME ZONE

NOTA : pour les types DATE, TIME et TIMESTAMP, le format de saisie est calé sur la norme ISO qui précise que les données temporelles doivent être écrites de la manière suivante : AAAA-MM-JJ et les heures : HH:MM:ss.xxx

ATTENTION : d'après la norme, on peut préciser le nombre de chiffres significatifs après la seconde des types TIME et TIMESTAMP. Ainsi, la déclaration d'un TIMESTAMP(6) permettrait de stocker des informations horaires avec une précision du milliardième de seconde. Compte tenu de la difficulté d'implémentation, de stockage et de calculs de telles données, ce dispositif est rarement implémenté sur les SGBDR des différents éditeurs.

INTERVAL est un type de données permettant de stocker des durées. Le stockage peut se faire sur une unité de temps ou sur une étendue d'unité continuelle de temps à l'aide des mots clefs YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
La syntaxe de déclaration d'un tel type est la suivante :

INTERVAL debut [TO fin]

debut, fin :: YEAR, MONTH,DAY,HOUR,MINUTE,SECOND

Avec les règles suivantes :
1) fin doit être plus précis que debut
2) debut peut dépasser la capacité maximale ordinaire habituellement attribuée à la mesure temporelle

Exemple 43

INTERVAL MINUTE
INTERVAL YEAR TO DAY
INTERVAL HOUR TO SECOND

Si vous désirez mesurer des durées en heures, vous pouvez par exemple stocker 178 heures 23 minutes et 16 secondes sous la forme suivante :

Exemple 44

CAST('178:23:16' AS INTERVAL HOUR TO SECOND)

NOTA : Ce type INTERVAL est malheureusement rarement implémenté. La plupart du temps les éditeurs recommandent d'utiliser un FLOAT pour stocker des durées en fraction de jour.

REMARQUE : on peut être étonné de l'absence de type « booléen » dans la norme. Il est facile de pallier en utilisant un type BIT(1). En l'absence du type BIT, on peut utiliser un SMALLINT associé à une règle de validation ou bien un littéral de type CHAR(1) avec une plage de valeurs.

ATTENTION : lorsqu'une ligne est ajoutée à une table, les colonnes non renseignées possèdent le marqueur NULL.

5-1-2 - Typage rapide avec des préfixes

Comment préciser le type cible d'une chaîne de caractère ? Là encore la norme vient à notre rescousse. En principe une chaîne de caractères est par défaut du type CHAR ou VARCHAR. Pour la convertir en NATIONAL (CHAR ou VARCHAR) il suffit de la préfixer avec la lettre d'attribut N. De même pour une chaîne représentant une valeur binaire, il faut utiliser la lettre B et pour une chaîne de valeur hexadécimale la lettre X. Il n'y a pas de lettres préfixes pour les autres types. Ainsi pour les chaînes de caractères CHAR / VARCHAR il suffit d'utiliser l'apostrophe comme délimiteur de la chaîne en dédoublant toute apostrophe se trouvant au sein de la chaîne. Enfin pour les données temporelles on peut les préfixer du nom de type.

Exemple 45

'toto'

chaîne CHAR ou VARCHAR

N'toto'

chaîne NATIONAL CHAR ou NATIONAL VARCHAR

X'AF16'

chaîne hexadécimale

B'010011'

chaîne binaire

DATE '2002-11-18'

date

TIME '11:16'

heure

TIMESTAMP '2002-11-18 11:16:22 + 01:00'

Combiné date/heure avec décalage de fuseau horaire

INTERVAL '11:16' HOUR TO MINUTE

Intervalle de temps borné par heures et minutes

5-2 - Les nouveaux types SQL 3

La norme SQL 3 (1999) a rajouté 3 types fondamentaux : booléen, CLOB et BLOB.

BOOLEAN est un type de données valant vrai ou faux (ou possédant le marqueur NULL si non renseigné).

Exemple 46

BOOLEAN

NOTA : du fait de la présence du marqueur NULL, le type booléen obéit à une logique ternaire.

CHARACTER LARGE OBJECT [(n [m])] est un type litteral permettant de stocker des chaînes de caractères de grande dimension dans un jeu à deux octets par caractères (ASCII). Le paramètre n permet de préciser la longeur et le paramètre m la mesure de cette longueur parmi les lettres K, M ou G.

Exemple 47

CHARACTER LARGE OBJECT (64 K)

Est un type littéral acceptant 64 kilos binaires de caractères, soit 64 * 1024 = 65 536 caractères (donc 131 072 octets).
Le synonyme CLOB peut être utilisé.

NATIONAL CHARACTER LARGE OBJECT [(n [m])] est une déclinaison de CHARACTER LARGE OBJECT opérant sur un jeu à quatre octets par caractères (UNICODE).
Le synonyme NCLOB peut être utilisé.

BINARY LARGE OBJECT [(n [m])] définit une chaîne de bits de grande dimension dont la longueur est fixe. Le paramètre n permet de préciser la longeur et le paramètre m la mesure de cette longueur parmi les lettres K, M ou G.

Exemple 48

BINARY LARGE OBJECT (2 G)

Est un type chaîne de bits acceptant 2 gigaoctets de données, soit 2 097 152 octets.
Le synonyme BLOB peut être utilisé.

La norme SQL 3 a de plus défini les types structurés ARRAY, ROW, REF ainsi que des types utilisateurs appelés UDT.

Syntaxe :

type_sql ARRAY [ dimension1 [, dimension2 ... ] ]

ROW ( colonne1 [, colonne2 ... ] )

REF ( udt ) [SCOPE nom_table [mode_reference]]

mode_reference ::
REFERENCES ARE [NOT] CHECKED
[ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }]

Exemple 49

MA_TABLEAU INTEGER ARRAY [12, 31]

MON_ADRESSE ROW (ADRESSE1 VARCHAR(32), ADRESSE2 VARCHAR(32), ADRESSE3 VARCHAR(32),
CODE_POSTAL CHAR(5), VILLE CHAR(32))

MON_CODE_POSTAL REF(UDT_CP) SCOPE TR_CODES_POSTAUX REFERENCES ARE CHECKED ON DELETE SET NULL

Ces trois exemples montrent :

- un tableau d'entiers à deux dimensions doté de 12 lignes et 31 colonnes ;

- une ligne dotée de 5 éléments permettant de stocker des adresses ;

- une donnée référentielle basée sur un type utilisateur permettant de restreindre les valeurs aux données situées dans une autre table.

Bien entendu ces types peuvent être combinés :

Exemple 50

MON_ADRESSE ROW (VARCHAR(32) ARRAY [3],
CODE_POSTAL REF(UDT_CP) SCOPE TR_CODES_POSTAUX REFERENCES ARE CHECKED ON DELETE SET NULL,
VILLE CHAR(32))

REMARQUE : il n'entre pas dans ce papier d'expliquer les UDT dont l'intérêt et la complexité mérite un papier à part entière. Néanmoins les UDT permettent de définir de nouveaux types de données, par exemple pour stocker des coordonnées planaires ou spatiales (polaire, cartésiennes…), des adresses IP, etc.

5-3 - Types communs présents dans certains SGBDR des différents éditeurs

On trouve couramment les types suivants dans certains SGBDR :

SMALLDATETIME

un format raccourcit pour des dates dans une plage de valeurs restreinte (à éviter)

MONEY, SMALLMONEY

un format spécifique aux valeurs monétaires (à éviter)

TINYINT

un entier avec une plage de valeurs très restreinte (par exemple 0 à 255)

BIGINT

un entier avec une plage de valeurs très étendue

LONGTEXT, TEXT, NTEXT

autres noms des CLOB et NCLOB

IMAGE

un BLOB spécialisé pour stocker des images

LONGBLOB

autre nom d'un BLOB

BFILE

fichier externe de BLOB (Oracle)

RAW, LONG RAW

données « brutes »

ROWVERSION, UNIQUEIDENTIFIER, ROWID

des identifiants spécifiques autogénérés

ENUM

des valeurs dans un ensemble prédéfini

Ces types ne sont pas normatifs, il convient donc de les éviter pour des raisons de portabilité.

5-4 - Définir des domaines et les utiliser

Un domaine au sens de la norme SQL est la définition d'un type associé à un certain nombre de règles de validité.

Imaginons que nous voulons modéliser le nombre de pages d'un livre. Il est à priori absurde d'accepter un nombre de pages négatif ou nul. Dans ce cas, il faudrait créer un nouveau type dont la plage des valeurs serait restreinte. D'où l'idée de domaine qui rassemble à la fois le type et l'étendue des valeurs possibles, c'est-à-dire le domaine des possibilités d'exploitation.

La définition d'un domaine s'opère avec la syntaxe suivante :

CREATE DOMAIN nom_domaine
[AS] type_donnée
[DEFAULT valeur_défaut]
[contrainte_de_domaine1 [, contrainte_de_domaine2 ... ] ]
[COLLATE nom_collation]

contrainte_de_domaine ::
CONSTRAINT nom_contrainte CHECK (regle_validation)

NOTA : une règle de validation doit utiliser le mot clef VALUE pour faire référence à la donnée.

Exemple 51

CREATE DOMAIN NOMBRE_DE_PAGE
AS INTEGER
CONSTRAINT CTR_SUP_ZERO CHECK (VALUE > 0)

La clause DEFAULT d'un ordre de création de domaine peut contenir une valeur explicite, ou bien l'une des valeurs retournées par les fonctions suivantes :

CURRENT_DATE

date courante

CURRENT_TIME[(p)]

heure courante

CURRENT_TIMESTAMP[(p)]

date et heure courante

LOCALTIME[(p)]

heure courante sans fuseau

LOCALTIMESTAMP[(p)]

heure et date courante sans fuseau

USER

utilisateur par défaut

CURRENT_USER

utilisateur courant

SESSION_USER

utilisateur de la session

SYSTEM_USER

utilisateur système

NULL

non renseigné

Exemple 52

CREATE DOMAIN OUI_NON
AS NCHAR(3)
DEFAULT 'NON'
CONSTRAINT CTR_LISTE_VALEUR CHECK (UPPER(VALUE) IN ('OUI', 'NON'))
COLLATE UNICODE

Cet exemple crée un domaine de type 3 caractères UNICODE sur la séquence de collation UNICODE (4 octets par caractères, ordre binaire) avec la valeur par défaut 'NON' et accepte toutes les combinaisons de valeurs suivantes :
'oui', Oui', 'OUi', 'OUI', 'oUI', 'OuI', 'oUi', 'ouI', 'non', 'Non', 'nOn', 'noN', 'NOn', 'nON', 'NoN', 'NON'

Exemple 53

CREATE DOMAIN IMMATRICULATION
AS CHAR(10)
CONSTRAINT CTR_CARACTERE_1 CHECK (SUBSTRING(VALUE, 1, 1) BETWEEN '0' AND '9')
CONSTRAINT CTR_CARACTERE_2 CHECK (SUBSTRING(VALUE, 2, 1) BETWEEN '0' AND '9')
CONSTRAINT CTR_CARACTERE_3 CHECK (SUBSTRING(VALUE, 3, 1) BETWEEN '0' AND '9')
CONSTRAINT CTR_CARACTERE_4 CHECK (SUBSTRING(VALUE, 4, 1) = ' ')
CONSTRAINT CTR_CARACTERE_5 CHECK (SUBSTRING(VALUE, 5, 1) BETWEEN 'A' AND 'Z')
CONSTRAINT CTR_CARACTERE_5_BIS CHECK (SUBSTRING(VALUE, 5, 1) NOT IN ('I', 'O'))
CONSTRAINT CTR_CARACTERE_6 CHECK (SUBSTRING(VALUE, 6, 1) BETWEEN 'A' AND 'Z')
CONSTRAINT CTR_CARACTERE_6_BIS CHECK (SUBSTRING(VALUE, 6, 1) NOT IN ('I', 'O'))
CONSTRAINT CTR_CARACTERE_7 CHECK (SUBSTRING(VALUE, 7, 1) BETWEEN 'A' AND 'Z')
CONSTRAINT CTR_CARACTERE_7_BIS CHECK (SUBSTRING(VALUE, 7, 1) NOT IN ('I', 'O'))
CONSTRAINT CTR_CARACTERE_8 CHECK (SUBSTRING(VALUE, 8, 1) = ' ')
CONSTRAINT CTR_CARACTERE_9 CHECK ((SUBSTRING(VALUE, 9, 2) BETWEEN '01' AND '95' OR SUBSTRING(VALUE, 9, 2)
IN ('2A', '2B')) AND NOT (SUBSTRING(VALUE, 9, 2) = '20' ))

Cet exemple permet de modéliser des immatriculations au format : « NNN AAA MM » ou NNN sont des chiffres de 0 à 9, AAA des lettres majuscules de 'A' à 'Z' excepté 'I' et 'O', et un code minéralogique correspondant au numéro des départements de 01 à 95 excepté 20, mais acceptant 2A et 2B pour la Corse !

Bien entendu la norme SQL 2 prévoit la modification d'un domaine en permettant de rajouter ou retirer une clause défaut ou une contrainte :

ALTER DOMAIN nom_domaine
{ SET DEFAULT valeur_défaut
| DROP DEFAULT
| ADD contrainte_de_domaine
| DROP CONSTRAINT nom_contrainte }

Exemple 54

ALTER DOMAIN IMMATRICULATION
DROP CONSTRAINT CTR_CARACTERE_1

ALTER DOMAIN IMMATRICULATION
DROP CONSTRAINT CTR_CARACTERE_2

ALTER DOMAIN IMMATRICULATION
DROP CONSTRAINT CTR_CARACTERE_3

ALTER DOMAIN
ADD CONSTRAINT CTR_CARACTERE_1_A_3 CHECK (CAST(SUBSTRING(VALUE, 1, 3) AS INTEGER) BETWEEN 1 AND 999)

NOTA : il n'est pas possible de changer le type de données de la définition d'un domaine.

La suppression d'un domaine s'effectue avec un ordre SQL DROP :

DROP DOMAIN nom_domaine [ CASCADE | RESTRICT ]

Lorsque l'on précise CASCADE, le domaine pourra être supprimé sans que les colonnes afférentes à ce domaine en soit affectées. Dans ce cas, le type du domaine comme les contraintes y afférent sont transférés à toutes les colonnes utilisant ce domaine. Si la clause RESTRICT est utilisée, alors une erreur sera levée si des colonnes de table dans la base utilisent le domaine à supprimer. Sinon, la suppression sera effective.

REMARQUE : certains SGBDR n'utilisent pas des domaines, mais proposent des mécanismes similaires.

En particulier c'est le cas de MS SQL Server qui utilise une combinaison de règles et de type utilisateur.

Exemple 55

CREATE RULE RLE_POURCENT
AS
@VALUE BETWEEN 0.0 AND 100.0

sp_addtype TYP_POURCENT, 'FLOAT', 'NOT NULL'

sp_bindrule 'RLE_POURCENT', 'TYP_POURCENT'

Création de l'équivalent d'un domaine dans MS SQL Server pour représenter un pourcentage.

NOTA : un domaine est bien entendu utilisable à la place d'un type SQL pour définir une colonne de table.

6 - Les assertions

Les assertions sont des contraintes dont l'étendue dépasse les types de données, les colonnes et la table pour permettre des règles de validation entre différentes colonnes de différentes tables. Les assertions au sens de la norme SQL sont donc des objets de la base de données.

La syntaxe de création d'une assertion est la suivante :

CREATE ASSERTION nom_assertion
CHECK (predicat)
[attribut_assertion]

attribut_assertion ::
{INITIALLY DEFERRED | INITIALLY ILMMEDIATE} [ [ NOT ] DEFERRABLE ]
| [NOT] DEFERRABLE [INITIALLY DEFERRED | INITIALLY ILMMEDIATE]

NOTA : les règles de déférabilité seront discutées lors de la partie consacrée aux contraintes de table.

Par exemple vous pouvez définir une règle de gestion qui indique que le montant des commandes non réglées ne doit pas dépasser 20 % du montant du chiffre d'affaires déjà réalisé par le client.
Pour réaliser notre exemple, nous avons besoin des tables T_CLIENT, T_FACTURE et T_COMPTE, définies de manière simpliste comme suit :

CREATE TABLE T_CLIENT
(CLI_ID        INTEGER        NOT NULL PRIMARY KEY,
CLI_NOM       CHAR(32)       NOT_NULL)

CREATE TABLE T_FACTURE
(FCT_ID        INTEGER        NOT NULL PRIMARY KEY,
CLI_ID        INTEGER        NOT NULL REFERENCES T_CLIENT (CLI_ID),
FCT_DATE      DATE           NOT NULL DEFAULT CURRENT_DATE,
FCT_MONTANT   DECIMAL (16,2) NOT NULL,
FCT_PAYE      BIT(1)         NOT NULL DEFAULT 0)

CREATE TABLE T_COMPTE
(CPT_ID        INTEGER        NOT NULL PRIMARY KEY,
CLI_ID        INTEGER        NOT NULL REFERENCES T_CLIENT (CLI_ID),
CPT_CREDIT    DECIMAL (16,2)
CPT_DEBIT     DECIMAL (16,2)
CPT_DATE      DATE           NOT NULL DEFAULT CURRENT_DATE)

Dans ce cas, l'assertion prendra la forme :

Exemple 56

CREATE ASSERTION AST_VERIFACTURE
CHECK (SELECT SUM(FCT_MONTANT)
FROM   T_FACTURE F
WHERE  FCT_PAYE = 0
GROUP  BY CLI_ID, FCT_PAYE) < (SELECT 0.2 * (SUM(CPT_DEBIT) - SUM(CPT_CREDIT))
FROM   T_COMPTE
WHERE  CLI_ID = F.CLI_ID)

Autre exemple, considérons que l'unicité d'une clé doit porter sur deux tables. Par exemple que la clé identifiant un client ou un prospect doit être unique au sein des deux tables afin qu'un prospect puisse devenir un client sans changement de clé. Dans ce cas l'assertion suivante peut être mise en place :

Exemple 57

CREATE TABLE T_PROSPECT
(PRT_ID    INTEGER   NOT NULL PRIMARY KEY,
PRT_NOM   CHAR(32)  NOT_NULL)

CREATE ASSERTION AST_UNIQUE_ID_CLI_PRP
CHECK (NOT EXISTS (SELECT *
FROM   T_CLIENT
WHERE  CLI_ID IN (SELECT PRP_ID
FROM   T_PROSPECT))
AND
NOT EXISTS (SELECT *
FROM   T_PROSPECT
WHERE  PRP_ID IN (SELECT CLI_ID
FROM   T_CLIENT)))

REMARQUE : certains SGBDR n'utilisent pas les assertions, mais proposent des mécanismes similaires généralement nommés RULE (règle)…

7 - Les tables

La voilà la grosse partie du DDL qui vous passionne. Alors ôtons-nous tout de suite une épine du pied en définissant la syntaxe de la création de table :

CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE nom_table
( colonne | contrainte_de_table [ { , colonne | contrainte_de_table }... ] )

colonne ::
nom_colonne { type | domaine }
[ DEFAULT valeur_default ]
[ contrainte_de_colonne... ]
[ COLLATE collation ]

contrainte_de_colonne ::
[CONSTRAINT nom_contrainte]
[NOT] NULL
| UNIQUE | PRIMARY KEY
| CHECK ( prédicat_de_colonne )
| FOREIGN KEY [colone] REFERENCES table (colonne) spécification_référence

contrainte_de_table ::
CONSTRAINT nom_contrainte
{ UNIQUE | PRIMARY KEY ( liste_colonne )
| CHECK ( prédicat_de_table )
| FOREIGN KEY liste colonne REFERENCES nom_table (liste_colonne) spécification_référence }

C'est une des syntaxes les plus simples que j'ai pu trouver pour vous montrer l'ensemble des possibilités qu'offre la norme SQL pour créer une table.

En gros, disons que :

- une table peut être créée de manière durable (par défaut) ou temporaire et dans ce dernier cas uniquement pour l'utilisateur et la connexion qu'il a créée ou bien pour l'ensemble des utilisateurs de la base ;

- une table comporte des colonnes et des contraintes de table ;

- une colonne peut être spécifiée d'après un type SQL ou un domaine créé par l'utilisateur ;

- une colonne définie peut être dotée de contraintes de colonnes telles que : obligatoire, clé, unicité, intégrité référentielle et validation ;

- une contrainte de table porte sur une ou plusieurs colonnes et permet l'unicité, la validation et l'intégrité référentielle.

RAPPEL : un nom de colonne doit être unique au sein de la table

Voici quelques exemples de création de tables utilisant tantôt des types SQL tantôt des domaines.

Exemple 58

CREATE TABLE T_CLIENT
(CLI_NOM    CHAR(32),
CLI_PRENOM VARCHAR(32))

Une table de clients dotée de deux colonnes avec les nom et prénom des clients.

Exemple 59

CREATE TABLE T_CLIENT
(CLI_ID     INTEGER      NOT NULL PRIMARY KEY,
CLI_NOM    CHAR(32)     NOT NULL,
CLI_PRENOM VARCHAR(32))

Une table de clients dotée de trois colonnes avec la clé (numéro du client) les nom et prénom des clients.

Exemple 60

CREATE DOMAIN D_NUM_ID INTEGER
CONSTRAINT C_CLEF CHECK (VALUE > 0)

CREATE DOMAIN D_ALFA_FIX_32 CHAR(32)

CREATE DOMAIN D_ALFA_VAR_32 VARCHAR(32)

CREATE TABLE T_CLIENT
(CLI_ID     D_NUM_ID      NOT NULL PRIMARY KEY,
CLI_NOM    D_ALFA_FIX_32 NOT NULL,
CLI_PRENOM D_ALFA_VAR_32)

Une table de clients similaire à l'exemple 58 à base de domaines, mais la clé ne peut être négative.

Exemple 61

CREATE TABLE T_CLIENT
(CLI_ID     INTEGER       NOT NULL PRIMARY KEY,
CLI_NOM    CHAR(32)      NOT NULL CHECK (SUBSTRING(VALUE, 1, 1) <> ' ' AND UPPER(VALUE) = VALUE),
CLI_PRENOM VARCHAR(32)   REFERENCES TR_PRENOM (PRN_PRENOM))

Une table de clients similaire à l'exemple 58 dont le nom ne peut commencer par un blanc, doit être en majuscules et dont le prénom doit figurer dans la table de référence TR_PRENOM à la colonne PRN_PRENOM.

Exemple 62

CREATE TABLE T_VOITURE
(VTR_ID                INTEGER       NOT NULL PRIMARY KEY,
VTR_MARQUE            CHAR(32)      NOT NULL,
VTR_MODELE            VARCHAR(16),
VTR_IMMATRICULATION   CHAR(10)      NOT NULL UNIQUE,
VTR_COULEUR           CHAR(16)      CHECK (VALUE IN ('BLANC', 'NOIR', 'ROUGE', 'VERT', 'BLEU')))

Une table de voiture avec immatriculation unique et couleur limitée à 'BLANC', 'NOIR', 'ROUGE', 'VERT', 'BLEU'.

Exemple 63

CREATE TABLE T_CLIENT
(CLI_NOM    CHAR(32)      NOT NULL,
CLI_PRENOM VARCHAR(32)   NOT NULL,
CONSTRAINT PK_CLIENT PRIMARY KEY (CLI_NOM, CLI_PRENOM))

Une table de clients dont la clé est le couple de colonnes NOM/PRENOM.

Nous allons maintenant détailler les différentes contraintes dites verticales ou horizontales suivant qu'il s'agit de contrainte de colonne ou de contrainte de ligne. Cette notion de vertical et horizontal fait référence à la visualisation des données de la table :

Une contrainte de colonne est dite verticale parce qu'elle porte sur une seule colonne. Dans la figure ci-dessus la contrainte de colonne est une clé (PRIMARY KEY).
Une contrainte de ligne est dite horizontale parce qu'elle porte sur plusieurs colonnes et se valide pour chaque ligne insérée.

La différence entre contrainte de ligne (horizontale) et contrainte de colonne (verticale) est purement terminologique puisque certaines contraintes peuvent être définies horizontalement comme verticalement :

contrainte

[NOT] NULL

DEFAULT

COLLATE

PRIMARY KEY

UNIQUE

CHECK

FOREIGN KEY

colonne (verticale)

OUI

OUI

OUI

OUI

OUI

OUI

OUI

ligne (horizontale)

NON

NON

NON

OUI

OUI

OUI

OUI

REMARQUE : notons que toute contrainte peut être déferrée, c'est-à-dire que ses effets peuvent être suspendus pour ne jouer qu'à la fin d'une transaction plutôt qu'à chaque ordre SQL censé la solliciter.

7-1 - Les contraintes de colonnes (verticales)

Une colonne peut donc recevoir les contraintes suivantes :

- NULL / NOT NULL : précise si une valeur doit obligatoirement être saisie dans la colonne ou non ;

- DEFAULT : valeur par défaut qui est placée dans la colonne lors des insertions et de certaines opérations particulières, lorsque l'on n’a pas donné de valeur explicite à la colonne ;

- COLLATE : précise la séquence de collation, c'est-à-dire l'ordre des caractères pour le tri et les éventuelles confusions possibles (minuscules/majuscules, caractères diacritiques distincts ou non). Voir paragraphe 4 à ce sujet ;

- PRIMARY KEY : précise si la colonne est la clé de la table. ATTENTION : nécessite que la colonne soit NOT NULL ;

- UNIQUE : les valeurs de la colonne doivent être unique ou NULL, c'est-à-dire qu'à l'exception du marqueur NULL, il ne doit jamais y avoir plus d'une fois la même valeur (pas de doublon) ;

- CHECK : permet de préciser un prédicat qui acceptera la valeur s'il est évalué à vrai ;

- FOREIGN KEY : permet, pour les valeurs de la colonne, de faire référence à des valeurs préexistantes dans une colonne d'une autre table. Ce mécanisme s'appelle intégrité référentielle.

NOTA : toutes ces contraintes peuvent être placées dans plusieurs colonnes, à l'exception de la contrainte de clé PRIMARY KEY qui ne peut être placée que sur une seule colonne. Pour faire de plusieurs colonnes une clé, il faut utiliser une contrainte de ligne (horizontale).

Lorsqu'au cours d'un ordre SQL d'insertion, de modification ou de suppression, une contrainte n'est pas vérifiée, on dit qu'il y a « violation » de la contrainte et les effets de l'ordre SQL sont totalement annulés (ROLLBACK).

REMARQUE : le mot clef CONSTRAINT comme le nom de la contrainte n'est pas obligatoire dans le cas de contraintes de colonnes.

7-1-1 - Obligatoire ([NOT] NULL)

On peut rendre la saisie d'une colonne obligatoire en apposant le mot clef NOT NULL. Dans ce cas, il ne sera jamais possible de faire en sorte que la colonne soit vide. Autrement dit, la colonne devra toujours être renseignée lors des ordres d'insertion INSERT et de modification UPDATE.
Si l'on désire que la colonne puisse ne pas être renseignée (donc accepter les marqueurs NULL), il n'est pas nécessaire de préciser le mot clef NULL, mais il est courant qu'on le fasse par facilité de lecture.

Exemple 64

CREATE TABLE T_PERSONNE1
(PRS_ID             INTEGER     NOT NULL
PRS_NOM            VARCHAR(32) NOT NULL,
PRS_PRENOM         VARCHAR(32) NULL,
PRS_DATE_NAISSANCE DATE)

Crée une table dont les colonnes PRS_ID et PRS_NOM doivent obligatoirement être renseignées.

Exemple 65 - insertion et modification acceptées :

INSERT INTO T_PERSONNE1 VALUES (1, 'DUPONT', NULL, NULL)
INSERT INTO T_PERSONNE1 (PRS_ID, PRS_NOM) VALUES (2, 'DURAND')

Exemple 66 - insertion et modification refusées :

INSERT INTO T_PERSONNE1 VALUES (3, NULL, 'Marcel', NULL)
INSERT INTO T_PERSONNE1 (PRS_ID, PRS_PRENOM) VALUES (4, 'Jean')

NOTA : les colonnes concourant à la définition d'une clé de table doivent impérativement posséder une contrainte NOT NULL.

7-1-2 - Valeur par défaut (DEFAULT)

La contrainte DEFAULT permet de préciser une valeur qui sera automatiquement insérée en l'absence de précision d'une valeur explicite dans un ordre d'insertion. Certains autres ordres SQL, comme la gestion de l'intégrité référentielle peuvent faire référence à cette valeur par défaut. Seuls une valeur explicite, un marqueur NULL ou la valeur retournée par les fonctions suivantes sont acceptées : CURRENT_DATE, CURRENT_TIME[(p)], CURRENT_TIMESTAMP[(p)], LOCALTIME[(p)], LOCALTIMESTAMP[(p)], USER, CURRENT_USER, SESSION_USER, SYSTEM_USER.

Exemple 67

CREATE TABLE T_PERSONNE2
(PRS_ID             INTEGER,
PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32),
PRS_SEXE           CHAR(1)     DEFAULT 'M',
PRS_DATE_NAISSANCE DATE        DEFAULT CURRENT_DATE)

NOTA : il n'est pas possible de préciser une valeur par défaut qui soit le résultat d'une expression de requête.

Exemple 68

CREATE TABLE T_PERSONNE3
(PRS_ID             INTEGER DEFAULT (SELECT MAX(PRS_ID) + 1
FROM T_PERSONNE3),
PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32))

Qui pourrait s'avérer bien utile pour générer de nouvelles valeurs de clés auto-incrémentées !

7-1-3 - Séquence de collation (COLLATE)

La séquence de collation permet de préciser l'ordre positionnel des caractères et leur éventuelle confusion, par exemple pour s'affranchir de la différence entre majuscules et minuscules ou encore entre caractères simples et caractères diacritiques (accent, cédille et autre…). La séquence de collation opère sur le tri et la comparaison des valeurs littérales.

Exemple 69

CREATE TABLE T_PERSONNE4
(PRS_ID             INTEGER
PRS_NOM            VARCHAR(32) COLLATE SQL_CHARACTER,
PRS_PRENOM         VARCHAR(32) COLLATE LATIN1)

Rappelons qu'une séquence de collation est attachée à un jeu de caractères. Pour de plus amples informations, voir « Jeu de caractères et séquence de collation ».

7-1-4 - Clé (PRIMARY KEY)

Selon le docteur Codd, toute table doit être munie d'une clé (souvent appelée à tort clé primaire en opposition à clé étrangère…). Et toujours selon le docteur Codd et la théorie des bases de données, une clé doit impérativement toujours être pourvue d'une valeur ! (sinon à quoi servirait une clé en l'absence de serrure ?).
Lorsque la clé porte sur une seule colonne, il est possible de donner à cette colonne la contrainte PRIMARY KEY.

Nous avons vu que la contrainte PRIMARY KEY peut être posée sur une colonne (contrainte verticale) ou sur plusieurs colonnes en contrainte de ligne (horizontale). Si nous choisissons de la poser en contrainte de colonne, alors une seule colonne de la table peut en bénéficier.

Exemple 70

CREATE TABLE T_PERSONNE5
(PRS_ID             INTEGER     NOT NULL PRIMARY KEY,
PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32))

La contrainte PRIMARY KEY assure qu'il n'y aura aucune valeur redondante (doublon) dans la colonne. La contrainte complémentaire NOT NULL assure qu'il y aura toujours une valeur. Toute tentative d'insérer une valeur préexistante de la colonne se soldera par une violation de contrainte de clé. Voici par exemple le message généré par SQL Server dans ce cas :

Violation de la contrainte PRIMARY KEY 'PK__T_PERSONNE5__45F365D3'.
Impossible d'insérer une clé en double dans l'objet 'T_PERSONNE5'.
L'instruction a été arrêtée.

NOTA : il est d'usage de placer la colonne clé en tête de la description de la table pour des fins de lisibilité.

Exemple 71 - clé primaire multicolonne impossible en contrainte verticale :

CREATE TABLE T_PERSONNE5
(PRS_ID             INTEGER     NOT NULL PRIMARY KEY,
PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32))

Impossible d'ajouter plusieurs contraintes PRIMARY KEY à la table 'T_PERSONNE6'.

7-1-5 - Unicité (UNIQUE)

La contrainte d'unicité exige que toutes les valeurs explicites contenues dans la colonne soient uniques au sein de la table. En revanche, la colonne peut ne pas être renseignée. En effet, souvenez vous que les marqueurs NULL se propagent dans les calculs et donc comparaison d'un marqueur NULL à un ensemble de valeurs est impossible et se solde par le renvoi d'un marqueur UNKNOW à la place des valeurs TRUE ou FALSE attendue.

Exemple 72

CREATE TABLE T_PERSONNE7
(PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32),
PRS_TELEPHONE      CHAR(14)     UNIQUE)

INSERT INTO T_PERSONNE7 VALUES ('Dupont', 'Marcel',  '01 44 21 57 18')
INSERT INTO T_PERSONNE7 VALUES ('Duval',  'André',    NULL)
INSERT INTO T_PERSONNE7 VALUES ('Durand', 'Jean',    '06 11 86 46 69')
INSERT INTO T_PERSONNE7 (PRS_NOM, PRS_PRENOM) VALUES ('Dubois', 'Claude')
INSERT INTO T_PERSONNE7 VALUES ('Dugland', 'Alfred', '06 11 86 46 69')

Violation de la contrainte UNIQUE KEY 'UQ__T_PERSONNE7__47DBAE45'.
Impossible d'insérer une clé en double dans l'objet 'T_PERSONNE7'.
L'instruction a été arrêtée.

SELECT *
FROM T_PERSONNE7

PRS_NOM                          PRS_PRENOM                       PRS_TELEPHONE
-------------------------------- -------------------------------- --------------
Dupont                           Marcel                           01 44 21 57 18
Duval                            André                            NULL
Durand                           Jean                             06 11 86 46 69
Dubois                           Claude                           NULL

Dans cet exemple Dugland n'a pas été inséré, car son numéro de téléphone est identique à Durand.

REMARQUE : certains SGBDR comme MS SQL Server refusent de voir la présence de plusieurs marqueurs NULL dans le cas d'une contrainte d'unicité. D'autres comme InterBase refusent une contrainte d'unicité dépourvue d'une contrainte NOT NULL…

ATTENTION : vous ne pouvez pas définir une contrainte d'unicité sur des colonnes de type BLOB.

7-1-6 - Validation (CHECK)

La contrainte CHECK de validation est celle qui offre le plus de possibilités. En contrepartie son exécution est très couteuse. Elle permet de définir un prédicat complexe, basé sur une comparaison pouvant contenir une requête de type SELECT. Pour valider la contrainte, le prédicat doit être évalué à TRUE ou UNKNOWN (présence de NULL).

Sa syntaxe est :

CHECK ( prédicat )

où prédicat peut contenir le mot clef VALUE pour faire référence à la colonne pour laquelle la contrainte est définie.

Exemple 73

CREATE TABLE T_PERSONNE8
(PRS_ID             INTEGER     CHECK (VALUE > 0),
PRS_NOM            VARCHAR(32) CHECK (CHARACTER_LENGTH(VALUE) > 2),
PRS_PRENOM         VARCHAR(32) CHECK (COALESCE(SUBSTRING(VALUE, 1, 1), 'X') BETWEEN 'A' AND 'Z'),
PRS_SEXE           CHAR(1)     CHECK (VALUE IN ('M', 'F')),
PRS_TELEPHONE      CHAR(14)    CHECK (SUBSTRING(VALUE, 1, 3) IN (SELECT PREFIXE FROM T_NUM_TEL) OR IS NULL))

La colonne PRS_ID ne peut avoir de valeurs inférieures à 0.
La colonne PRS_NOM doit avoir des valeurs contenant au moins 2 caractères.
Le premier caractère de la colonne PRS_PRENOM, si elle est renseignée, doit être compris entre A et Z.
La colonne PRS_SEXE peut avoir exclusivement les valeurs M ou F.
Les trois premiers caractères de la colonne PRS_TELEPHONE si elle est renseignée doit correspondre à une valeur se trouvant dans la colonne PREFIXE de la table T_NUM_TEL.

ATTENTION : la longueur du prédicat d'une contrainte CHECK (en nombre de caractères) peut être limité. Il faut en effet pouvoir stocker cette contrainte dans le dictionnaire des informations de la base et ce dernier n'est pas illimité.

7-1-7 - Intégrité référentielle (FOREIGN KEY / REFERENCES)

La contrainte de type FOREIGN KEY permet de mettre en place une intégrité référentielle entre une (ou plusieurs) colonne d'une table et la (ou les) colonne composant la clé d'une autre table afin d'assurer les relations existantes et joindre les tables dans la requête selon le modèle relationnel que l'on a défini.
Le but de l'intégrité référentielle est de maintenir les liens entre les tables quelles que soient les modifications engendrées sur les données dans l'une ou l'autre table.

Cette contrainte dans sa syntaxe complète est assez complexe et c'est pourquoi nous allons dans ce paragraphe donner une syntaxe très simplifiée à des fins didactiques :

FOREIGN KEY REFERENCES table (colonne)

La syntaxe complète de la clause FOREIGN KEY sera vue au paragraphe 7.3.

ATTENTION : la colonne spécifiée comme référence doit être une colonne clé.

Exemple 74

CREATE TABLE T_FACTURE1
(FTC_ID             INTEGER,
PRS_ID             INTEGER      FOREIGN KEY REFERENCES T_PERSONNE5 (PRS_ID) ,
FCT_DATE           DATE,
FCT_MONTANT        DECIMAL(16,2))

La table T_FACTURE1 est liée à la table T_PERSONNE5 et ce lien se fait entre la clé étrangère PRS_ID de la table T_FACTURE1 et la clé de la table T_PERSONNE5 qui s'intitule aussi PRS_ID.

NOTA : il est très important que les noms des colonnes de jointure soient les mêmes dans les différentes tables (notamment à cause du NATURAL JOIN), mais cela n'est pas obligatoire.

Dès lors toute tentative d'insertion d'une facture dont la référence de client est inexistante se soldera par un échec. De même toute tentative de supprimer un client pour lequel les données d'une ou de plusieurs factures sont présentes se soldera par un arrêt sans effet de l'ordre SQL.

Examinons maintenant comment le SGBDR réagit pour assurer la cohérence de la base lors d'opérations tenant de briser les liens d'intégrité référentielle :

Exemple 75

INSERT INTO T_PERSONNE5 VALUES (1, 'Dupont', 'Marcel')
INSERT INTO T_PERSONNE5 VALUES (2, 'Duval', 'André')

INSERT INTO T_FACTURE1 VALUES (1, 1, '2002-03-15', 1256.45)
INSERT INTO T_FACTURE1 VALUES (1, 2, '2002-04-22', 7452.89)

Tentative d'insertion d'une facture dont la personne n'est
pas référencée dans la table T_PERSONNE5 :
INSERT INTO T_FACTURE1 VALUES (1, 3, '2002-03-15', 1256.45)

Conflit entre l'instruction INSERT et
la contrainte COLUMN FOREIGN KEY
'FK__T_FACTURE__PRS_I__5165187F'.
Le conflit est survenu dans la base
de données 'DB_HOTEL',
table 'T_PERSONNE5', column 'PRS_ID'.
L'instruction a été arrêtée.

Tentative de suppression d'une personne possédant
encore des factures
DELETE FROM T_PERSONNE5 WHERE PRS_NOM = 'Dupont'

Conflit entre l'instruction DELETE et
la contrainte COLUMN REFERENCE
'FK__T_FACTURE__PRS_I__5165187F'.
Le conflit est survenu dans la base
de données 'DB_HOTEL',
table 'T_FACTURE1', column 'PRS_ID'.
L'instruction a été arrêtée.

REMARQUE : Comme on le voit, le mécanisme d'intégrité référentielle est un élément indispensable au maintien des relations entre tables. Un SGBD qui en est dépourvu ne peut pas prétendre à gérer le relationnel. En particulier MySQL ne peut en aucun cas prétendre être une base de données relationnelle !

7-2 - Les contraintes de table

Une table peut être pourvue des contraintes de ligne suivante :

- PRIMARY KEY : précise que la ou les colonnes composent la clé de la table. ATTENTION : nécessite que chaque colonne concourant à la clé soit NOT NULL.

- UNIQUE : les valeurs de la ou les colonnes doivent être unique ou NULL, c'est-à-dire qu'à l'exception du marqueur NULL, il ne doit jamais y avoir plus d'une fois la même valeur (pas de doublon) au sein de l'ensemble de données formé par les valeurs des différentes colonnes composant la contrainte.

- CHECK : permet de préciser un prédicat validant différentes colonnes de la table et qui accepteront les valeurs s'il est évalué à vrai.

- FOREIGN KEY : permet, pour les valeurs de la ou les colonnes, de faire référence à des valeurs préexistantes dans une ou plusieurs colonnes d'une autre table. Ce mécanisme s'appelle intégrité référentielle.

Comme dans le cas des contraintes de colonne, lorsqu'au cours d'un ordre SQL d'insertion, de modification ou de suppression, une contrainte n'est pas vérifiée on dit qu'il y a « violation » de la contrainte et les effets de l'ordre SQL sont totalement annulés (ROLLBACK).

7-2-1 - Clé multicolonne (PRIMARY KEY)

La clé d'une table peut être composée de plusieurs colonnes. Dans ce cas la syntaxe est :

CONSTRAINT nom_contrainte PRIMARY KEY (liste_colonne)

Exemple 76 - clé primaire sur PRS_NOM / PRS_PRENOM

CREATE TABLE T_PERSONNE9
(PRS_NOM            VARCHAR(32) NOT NULL,
PRS_PRENOM         VARCHAR(32) NOT NULL,
PRS_TELEPHONE      CHAR(14),
CONSTRAINT PK_PRS PRIMARY KEY (PRS_NOM, PRS_PRENOM))

7-2-2 - Unicité globale (UNIQUE)

Un contrainte d'unicité peut être portée sur plusieurs colonnes. Dans ce cas chaque n-uplets de valeurs explicite doit être différent.
Dans ce cas la syntaxe est :

CONSTRAINT nom_contrainte UNIQUE (liste_colonne)

Exemple 77 - définition d'une clé unique sur PRS_NOM / PRS_PRENOM

CREATE TABLE T_PERSONNE10
(PRS_ID             INTEGER,
PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32),
CONSTRAINT UNI_NOM_PRENOM UNIQUE (PRS_NOM, PRS_PRENOM))

INSERT INTO T_PERSONNE10 VALUES (1, 'Dupont', 'Marcel')
INSERT INTO T_PERSONNE10 VALUES (2, 'Duval',  'André')
INSERT INTO T_PERSONNE10 VALUES (3, 'Dupond',  NULL)
INSERT INTO T_PERSONNE10 VALUES (4, NULL, NULL)
INSERT INTO T_PERSONNE10 VALUES (5, NULL, 'Alfred')
INSERT INTO T_PERSONNE10 VALUES (6, 'Duval',  'André')

Violation de la contrainte UNIQUE KEY 'UNI_NOM_PRENOM'.
Impossible d'insérer une clé en double dans l'objet 'T_PERSONNE10'.
L'instruction a été arrêtée.

SELECT *
FROM T_PERSONNE10

PRS_ID      PRS_NOM                          PRS_PRENOM
----------- -------------------------------- ----------
1           Dupont                           Marcel
2           Duval                            André
3           Dupond                           NULL
4           NULL                             NULL
5           NULL                             Alfred

REMARQUE : certains SGBDR comme MS SQL Server refusent de voir la présence de plusieurs marqueurs NULL dans le cas d'une contrainte d'unicité. D'autres comme InterBase refusent une contrainte d'unicité dépourvue d'une contrainte NOT NULL…

ATTENTION : vous ne pouvez pas définir une contrainte d'unicité sur des colonnes de type BLOB.

7-2-3 - Validation de ligne (CHECK)

La contrainte CHECK permet d'effectuer un contrôle de validation multicolonne au sein de la table.

Sa syntaxe est :

CONSTRAINT nom_contrainte CHECK ( prédicat )

Exemple 78 - vérification de présence d'information dans au moins une colonne crédit ou débit de la table compte :

CREATE TABLE T_COMPTE
(CPT_ID             INTEGER,
CPT_DATE           DATE,
CPT_CREDIT         DECIMAL (16,2),
CPT_DEBIT          DECIMAL (16,2),
CLI_ID             INTEGER,
CONSTRAINT CHK_OPERATION CHECK((CPT_CREDIT >= 0 AND CPT_DEBIT IS NULL) OR (CPT_DEBIT >= 0 AND CPT_CREDITIS NULL)))

Toute tentative d'insérer une ligne avec des valeurs non renseignées pour les colonnes debit et credit, ou bien avec des valeurs négative se soldera par un refus.

7-2-4 - Integrité référentielle de table (FOREIGN KEY / REFERENCES)

Comme dans le cas d'une contrainte référentielle de colonne, il est possible de placer une contrainte d'intégrité portant sur plusieurs colonnes. Ceci est d'autant plus important qu'il n'est pas rare de trouver des tables dont la clé est composée de plusieurs colonnes. La syntaxe est la suivante :

CONSTRAINT nom_contrainte FOREIGN KEY (liste_colonne) REFERENCES nom_table_ref (liste_colonne_ref)

Exemple 79

CREATE TABLE T_FACTURE2
(FTC_ID             INTEGER,
PRS_NOM            VARCHAR(32),
PRS_PRENOM         VARCHAR(32),
FCT_DATE           DATE,
FCT_MONTANT        DECIMAL(16,2),
CONSTRAINT FK_FCT_PRS FOREIGN KEY (PRS_NOM, PRS_PRENOM) REFERENCES T_PERSONNE9 (PRS_NOM, PRS_PRENOM))

La table T_FACTURE2 est liée à la table T_PERSONNE9 et ce lien se fait entre la clé étrangère composite PRS_NOM / PRS_PRENOM de la table T_FACTURE2 et la clé de la table T_PERSONNE9 elle-même composée des colonnes PRS_NOM / PRS_PRENOM.

Examinons maintenant comment le SGBDR réagit pour assurer la cohérence de la base lors d'opérations tenant de briser les liens d'intégrité référentielle :

Exemple 80

INSERT INTO T_PERSONNE9 VALUES ('Dupont', 'Marcel', '01 45 78 74 25')
INSERT INTO T_PERSONNE9 VALUES ('Duval', 'André', NULL)

INSERT INTO T_FACTURE2 VALUES (1, 'Dupont', 'Marcel', '2002-03-15', 1256.45)
INSERT INTO T_FACTURE2 VALUES (1, 'Duval', 'André', '2002-04-22', 7452.89)

Tentative d'insertion d'une facture dont la personne n'est pas référencée dans la table T_PERSONNE5 :
INSERT INTO T_FACTURE1
VALUES (1, 'Dubois', 'Maurice', '2002-03-15', 1256.45)

Conflit entre l'instruction INSERT et la contrainte
TABLE FOREIGN KEY 'FK_FCT_PRS'. Le conflit est survenu
dans la base de données 'DB_HOTEL', table 'T_PERSONNE9'.
L'instruction a été arrêtée.

Tentative de suppression d'une personne possédant
encore des factures
DELETE FROM T_PERSONNE5
WHERE PRS_NOM = 'Dupont' AND PRS_PRENOM = 'Marcel'

Conflit entre l'instruction DELETE et la contrainte
COLUMN REFERENCE 'FK__T_FACTURE__PRS_I__5165187F'.
Le conflit est survenu dans la base de données
'DB_HOTEL', table 'T_FACTURE1', column 'PRS_ID'.
L'instruction a été arrêtée.

7-3 - La gestion de l'intégrité référentielle

Comme nous l'avions annoncé, la syntaxe de la pose de contraintes d'intégrité est plus complexe que ce qui vient d'être évoqué. Voici la syntaxe complète de cette structure :

CONSTRAINT nom_contrainte
FOREIGN KEY (liste_colonne_table)
REFERENCES table_référencée (liste_colonne_référencées)
[ MATCH { FULL | PARTIAL | SIMPLE } ]
[ { ON UPDATE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT } ]
[ { ON DELETE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT } ]
[ {  INITIALLY DEFERRED  | INITIALLY IMMEDIATE } [ [ NOT ] DEFERRABLE ]
| [ NOT ] DEFERRABLE [ {  INITIALLY DEFERRED  | INITIALLY IMMEDIATE } ] ]

Clause MATCH de gestion de la référence
Clause ON UPDATE de mise à jour
Clause ON DELETE de suppression
Clause de déférabilité

Nous allons maintenant détailler les clauses MATCH, ON UPDATE, ON DELETE et la déferabilité.

7-3-1 - Mode de gestion de la référence, clause MATCH

Pour mieux comprendre le fonctionnement de cette clause, voici le modèle utilisé :

Exemple 81

CREATE TABLE T_FOURNISSEUR
(FRN_NOM     CHAR(16) NOT NULL,
FRN_PRENOM  CHAR(16) NOT NULL,
CONSTRAINT PK_FRN PRIMARY KEY (FRN_NOM, FRN_PRENOM))

INSERT INTO T_FOURNISSEUR VALUES ('DUBOIS', 'Alain')
INSERT INTO T_FOURNISSEUR VALUES ('DURAND', 'Paula')

MATCH SIMPLE implique que :

- si toutes les colonnes contraintes sont renseignées, la contrainte s'applique ;

- si une colonne au moins possède un marqueur NULL, la contrainte ne s'applique pas.

Exemple 82

CREATE TABLE T_COMMANDE1
(CMD_ID      INTEGER NOT NULL PRIMARY KEY,
FRN_NOM     CHAR(16),
FRN_PRENOM  CHAR(16),
CONSTRAINT  FK_CMD_FRN_MATCH_SIMPLE
FOREIGN KEY (FRN_NOM, FRN_PRENOM)
REFERENCES T_FOURNISSEUR (FRN_NOM, FRN_PRENOM)
MATCH SIMPLE)

INSERT INTO T_COMMANDE1 VALUES (1, 'DUBOIS', 'Alain')
INSERT INTO T_COMMANDE1 VALUES (2, 'DUBOIS', NULL)
INSERT INTO T_COMMANDE1 VALUES (3, 'DUHAMEL', NULL)
INSERT INTO T_COMMANDE1 VALUES (4, NULL, 'Paula')
INSERT INTO T_COMMANDE1 VALUES (5, NULL, NULL)

Insertion réussie

INSERT INTO T_COMMANDE1 VALUES (6, 'DUHAMEL', 'Marcel')

Conflit entre l'instruction INSERT et
la contrainte TABLE FOREIGN KEY
'FK_CMD_FRN_MATCH_SIMPLE'.
Le conflit est survenu dans la base
de données 'DB_HOTEL',
table 'T_FOURNISSEUR'.
L'instruction a été arrêtée.

MATCH FULL implique que :

- la contrainte s'applique toujours sauf si toutes les colonnes sont pourvues d'un marqueur NULL.

Par conséquent, il ne peut y avoir une colonne renseignée et l'autre pas.

Exemple 83

CREATE TABLE T_COMMANDE2
(CMD_ID      INTEGER NOT NULL PRIMARY KEY,
FRN_NOM     CHAR(16),
FRN_PRENOM  CHAR(16),
CONSTRAINT  FK_CMD_FRN_MATCH_FULL
FOREIGN KEY (FRN_NOM, FRN_PRENOM)
REFERENCES T_FOURNISSEUR (FRN_NOM, FRN_PRENOM)
MATCH FULL)

INSERT INTO T_COMMANDE1 VALUES (1, 'DUBOIS', 'Alain')
INSERT INTO T_COMMANDE1 VALUES (2, NULL, NULL)

Insertion réussie

INSERT INTO T_COMMANDE1 VALUES (3, 'DUHAMEL', NULL)
INSERT INTO T_COMMANDE1 VALUES (4, NULL, 'Paula')
INSERT INTO T_COMMANDE1 VALUES (5, 'DUBOIS', NULL)
INSERT INTO T_COMMANDE1 VALUES (6, 'DUHAMEL', 'Marcel')

Conflit entre l'instruction INSERT et
la contrainte TABLE FOREIGN KEY
'FK_CMD_FRN_MATCH_FULL'.
Le conflit est survenu dans la base
de données 'DB_HOTEL',
table 'T_FOURNISSEUR'.
L'instruction a été arrêtée.

MATCH PARTIAL implique que :

- La contrainte s'applique pour toutes les colonnes renseignées

Exemple 84

CREATE TABLE T_COMMANDE2
(CMD_ID      INTEGER NOT NULL PRIMARY KEY,
FRN_NOM     CHAR(16),
FRN_PRENOM  CHAR(16),
CONSTRAINT  FK_CMD_FRN_MATCH_PARTIAL
FOREIGN KEY (FRN_NOM, FRN_PRENOM)
REFERENCES T_FOURNISSEUR (FRN_NOM, FRN_PRENOM)
MATCH PARTIAL)

INSERT INTO T_COMMANDE1 VALUES (1, 'DUBOIS', 'Alain')
INSERT INTO T_COMMANDE1 VALUES (2, NULL, NULL)
INSERT INTO T_COMMANDE1 VALUES (4, NULL, 'Paula')
INSERT INTO T_COMMANDE1 VALUES (5, 'DUBOIS', NULL)

Insertion réussie

INSERT INTO T_COMMANDE1 VALUES (3, 'DUHAMEL', NULL)
INSERT INTO T_COMMANDE1 VALUES (6, 'DUHAMEL', 'Marcel')

Conflit entre l'instruction INSERT et
la contrainte TABLE FOREIGN KEY
'FK_CMD_FRN_MATCH_PARTIAL'.
Le conflit est survenu dans la base
de données 'DB_HOTEL',
table 'T_FOURNISSEUR'.
L'instruction a été arrêtée.

NOTA : certains SGBDR n'ont pas implémenté le mode de gestion de la référence. C'est le cas en particulier de MS SQL Server et d'InterBase.

7-3-2 - Mode de gestion de l'intégrité clauses ON UPDATE / ON DELETE

Le mode de gestion de l'intégrité consiste à se poser la question de ce que la machine doit faire dans le cas ou l'on tente de briser une intégrité référentielle. Nous avons vu que par défaut il n'est pas possible de supprimer une personne ayant encore des données dans la table des factures et qu'il n'est pas possible d'insérer une facture pour une personne non référencée. Ce mode est dit en SQL : ON UPDATE NO ACTION, ON DELETE NO ACTION ce qui signifie qu'aucune action particulière n'est entreprise en cas de mise à jour ou suppression.
Nous allons maintenant voir quels sont les autres modes de gestion de l'intégrité référentielle.

ATTENTION : ce mode n'a aucun effet sur le comportement de la contrainte qui s'exerce de toute façon en fonction de la clause MATCH.

ON DELETE NO ACTION / ON UPDATE NO ACTION : aucun traitement particulier n'est entrepris en cas de mise à jour ou suppression d'informations référencées. Autrement dit, il y a blocage du traitement, car le lien d'intégrité ne doit pas être brisé. Mêmes effets que RESTRICT, mais post opératoire.

ON DELETE CASCADE / ON UPDATE CASCADE : en cas de suppression d'un élément, les éléments qui le référence sont eux aussi supprimés. En cas de modification de la valeur de la clé, les valeurs des clés étrangères qui le référencent sont elles aussi modifiées afin de maintenir l'intégrité. Par exemple en cas de suppression d'un client les factures et commandes sont elles aussi supprimées.

NOTA : ce mode est très tentant, mais son coût de traitement est très élevé et les performances peuvent très rapidement se dégrader fortement.

ON DELETE SET NULL / ON UPDATE SET NULL : en cas de suppression d'un élément, les éléments qui le référencent voient leur clé étrangère posséder le marqueur NULL . De même en cas de modification de la valeur de la clé. Le lien d'intégrité est alors brisé.
L'intérêt d'une telle manœuvre est de permettre la suppression des lignes devenues orphelines de manière différée, par exemple dans un batch de nuit.

ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT : en cas de suppression comme en cas de mise à jour de la clé référencée, la référence passe à la valeur par défaut définie lors de la création de la table. Ce mode permet l'insertion d'un client générique, possédant un identifiant particulier (par exemple 0 ou -1) afin de ne jamais briser le lien d'intégrité référentielle. Bien entendu on veillera ensuite à rectifier la vraie valeur du lien au moment opportun si besoin est.

ON DELETE RESTRICT / ON UPDATE RESTRICT : mêmes effets que NO ACTION, mais pré opératoire.

NOTA : certains SGBDR n'ont pas implémenté le mode de gestion de l'intégrité. C'est le cas en particulier de MS SQL Server. En revanche, il est courant de trouver dans les SGBDR des options plus limitées que celles fournies par la norme.

7-4 - Mode de gestion de la déférabilité

La déférabilité d'une contrainte est une opération nécessaire dès que différentes contraintes interagissent créant ainsi ce que l'on appelle une « référence circulaire »…
Ainsi lorsqu'une table T1 fait référence à une table T2 par une intégrité référentielle, se pose le problème de la mise en place d'une intégrité référentielle inverse de T2 vers T1…
Encore une fois nous voici confrontés au problème de l'œuf et de la poule… C'est pour trancher ce dilemme que la déférabilité d'une contrainte a été définie par la norme SQL 2. Les SGBDR mettant en œuvre cette gestion, comme ORACLE, ne courent pas les rues !
Tentons cependant d'y voir clair, à l'aide d'un exemple…

Imaginons que nous voulons modéliser un client et ses commandes et placer dans la table du client la dernière commande servie… Le problème se pose ainsi : comment insérer un nouveau client qui, par définition, n'a pas encore de commande, alors que l'on exige dans la table client de faire référence à la dernière commande ?

Voici un premier jet du script de création de nos deux tables :

Exemple 85

CREATE TABLE T_CLIENT
(CLI_ID      INTEGER NOT NULL PRIMARY KEY,
CLI_NOM     CHAR(32),
CDE_ID      INTEGER NOT NULL,
CONSTRAINT  FK_CDE FOREIGN KEY REFERENCES T_COMMANDE (CDE_ID))

CREATE TABLE T_COMMANDE
(CDE_ID      INTEGER NOT NULL PRIMARY KEY,
CDE_DATE    DATE,
CLI_ID      INTEGER NOT NULL,
CONSTRAINT  FK_CLI FOREIGN KEY REFERENCES T_CLIENT (CLIK_ID))

Il y a fort à parier que ce script ne puisse être joué sur la plupart des SGBDR…
On peut néanmoins l'amender de la manière suivante :

Exemple 86

CREATE TABLE T_CLIENT
(CLI_ID      INTEGER NOT NULL PRIMARY KEY,
CLI_NOM     CHAR(32),
CDE_ID      INTEGER NOT NULL)

CREATE TABLE T_COMMANDE
(CDE_ID      INTEGER NOT NULL PRIMARY KEY,
CDE_DATE    DATE,
CLI_ID      INTEGER NOT NULL,
CONSTRAINT  FK_CLI FOREIGN KEY REFERENCES T_CLIENT (CLIK_ID))

ALTER TABLE  T_CLIENT
ADD CONSTRAINT FK_CDE FOREIGN KEY REFERENCES T_COMMANDE (CDE_ID)

Cependant je vous mets au défi de pouvoir insérer quoi que ce soit dans l'une quelconque des tables, puisque l'une a besoin des informations de l'autre et vice versa…

Pour répondre à ce cas de figure, la norme SQL 2 a défini la « déférabilité » d'une contrainte… Au fait savez-vous ce qu'est la déférabilité ? Déférer quelqu'un c'est transféré la responsabilité de cette personne à un moment donné, à une autre instance. Ainsi un gangster déféré au parquet voit la responsabilité de son arrestation, passer des mains des policiers aux mains des juges. La déférabilité est donc la possibilité de « déférer ».

Pour la norme SQL 2, la déférabilité se précise :

- lors de la création du schéma ;

- lors de l'exécution de la contrainte.

Elle permet de transférer le moment ou la validation de la contrainte va s'effectuer…

Reprenons la syntaxe de la clause de déférabilité d'une contrainte :

[ {  INITIALLY DEFERRED  | INITIALLY IMMEDIATE } [ [ NOT ] DEFERRABLE ]
| [ NOT ] DEFERRABLE [ {  INITIALLY DEFERRED  | INITIALLY IMMEDIATE } ] ]

Une contrainte peut donc être définie comme NOT DEFERRABLE (c'est l'option par défaut) dans ce cas elle s'applique immédiatement (INITIALLY IMMEDIATE).
Si elle est définie comme DEFERRABLE, alors il convient de préciser quand :

- INITIALLY DEFERRED signifie qu'elle prendra ses effets en fin de transaction ;

- INITIALLY IMMEDIATE signifie qu'elle appliquera la contrainte dans l'ordre de mise à jour (INSERT, UPDATE DELETE) sans attendre la fin de la transaction.

Pour modifier la déférabilité d'une contrainte, SQL 2 a prévu l'ordre SET CONSTRAINTS :

SET CONSTRAINT { ALL | nom_contrainte1 [, nom_contrainte2 [...] ] } { IMMEDIATE | DEFFERED }

Cet ordre permet de changer la déférabilité d'une contrainte à la volée.

IMPORTANT :

- la déférabilité d'une contrainte est le seul élément du langage capable de créer un « auto rollback » ;

- certains SGBDR valident les contraintes pour chaque ligne ce qui n'est pas conforme à la norme SQL 2 pour laquelle toute requête est une transaction…

Pour résoudre notre problème, nous pouvons gérer la déférabilité dans la construction de la table :

Exemple 87

CREATE TABLE T_CLIENT
(CLI_ID      INTEGER NOT NULL PRIMARY KEY,
CLI_NOM     CHAR(32),
CDE_ID      INTEGER NOT NULL)

CREATE TABLE T_COMMANDE
(CDE_ID      INTEGER NOT NULL PRIMARY KEY,
CDE_DATE    DATE,
CLI_ID      INTEGER NOT NULL,
CONSTRAINT  FK_CLI FOREIGN KEY REFERENCES T_CLIENT (CLIK_ID))

ALTER TABLE  T_CLIENT
ADD CONSTRAINT FK_CDE FOREIGN KEY REFERENCES T_COMMANDE (CDE_ID)
DEFERRABLE   INITIALLY DEFERRED

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM)
VALUES (118,    'Dupont')

INSERT INTO T_COMMANDE (CDE_ID, CDE_DATE,     CLI_ID)
VALUES (587,    '2002-11-03', 118)

UPDATE T_CLIENT
SET CDE_ID = 587
WHERE CLI_ID = 118

COMMIT

Mais nous pouvons aussi piloter cette déférabilité dans un script transactionné :

Exemple 88

CREATE TABLE T_CLIENT
(CLI_ID      INTEGER NOT NULL PRIMARY KEY,
CLI_NOM     CHAR(32),
CDE_ID      INTEGER NOT NULL)

CREATE TABLE T_COMMANDE
(CDE_ID      INTEGER NOT NULL PRIMARY KEY,
CDE_DATE    DATE,
CLI_ID      INTEGER NOT NULL,
CONSTRAINT  FK_CLI FOREIGN KEY REFERENCES T_CLIENT (CLIK_ID))

ALTER TABLE  T_CLIENT
ADD CONSTRAINT FK_CDE FOREIGN KEY REFERENCES T_COMMANDE (CDE_ID)
DEFERRABLE   INITIALLY IMMEDIATE

SET CONSTRAINTS FK_CDE DEFERRED

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM)
VALUES (118,    'Dupont')

INSERT INTO T_COMMANDE (CDE_ID, CDE_DATE,     CLI_ID)
VALUES (587,    '2002-11-03', 118)

UPDATE T_CLIENT
SET CDE_ID = 587
WHERE CLI_ID = 118

COMMIT

Au fait, dans le principe n'importe quelle contrainte (de colonne ou de table) peut disposer d'une clause de déférabilité. Pas seulement les intégrités référentielles !

7-5 - Contraintes horizontales ou verticales ?

Comme nous l'avons vu, les contraintes peuvent être définie PRIMARY KEY, UNIQUE, CHECK et FOREIGN KEY peuvent être définies indifféremment en contraintes de colonnes comme en contrainte de ligne. Ainsi une clé portant sur une seule colonne peut parfaitement être définie en tant que contrainte de table.

Ainsi les deux ordres suivants :

Exemple 89

CREATE TABLE T_CLIENT
(CLI_ID      INTEGER NOT NULL PRIMARY KEY,
CLI_NOM     CHAR(32))

CREATE TABLE T_CLIENT
(CLI_ID      INTEGER NOT NULL,
CLI_NOM     CHAR(32),
CONSTRAINT  PK_CLI PRIMARY KEY (CLI_ID))

Sont strictement équivalent, même si l'un est plus verbeux.

Mais il y a un net avantage à utiliser systématiquement des contraintes horizontales.
Simplement parce que :

- elles sont plus lisibles ;

- elles sont nommées ;

- elles peuvent facilement être supprimées et réinsérées.

Essayez donc dans le premier cas de l'exemple 89 de faire porter la clé primaire sur la colonne CLI_NOM plutôt que CLI_ID…

Pour le deuxième cas, c'est bien plus simple :

Exemple 90

ALTER TABLE T_CLIENT DROP CONSTRAINT PK_CLI
ALTER TABLE T_CLIENT ADD CONSTRAINT PK_CLI PRIMARY KEY (CLI_NOM)

7-6 - Alter et Drop

Les ordres ALTER et DROP sont les ordres de modification (ALTER pour altération) et suppression (DROP).

L'ordre ALTER peut porter sur un domaine, une assertion, une table, une vue, etc.

L'ordre ALTER sur une table permet de :

- supprimer une colonne ;

- supprimer une contrainte ;

- ajouter une colonne ;

- ajouter une contrainte ;

- ajouter une contrainte de ligne DEFAULT.

Il ne permet pas de :

- changer le nom d'une colonne ;

- changer le type d'une colonne ;

- ajouter une contrainte de ligne NULL / NOT NULL.

Syntaxe de l'ordre ALTER sur table :

ALTER TABLE nom_table
{  ADD   definition_colonne
| ALTER nom_colonne { SET DEFAULT valeur_défaut | DROP DEFAULT }
| DROP  nom_colonne [ CASCADE | RESTRICT ]
| ADD   définition_contrainte_ligne
| DROP  CONSTRAINT nom_contrainte [ CASCADE | RESTRICT ] }

L'option CASCADE / RESTRICT permet de gérer l'intégrité de référence de la colonne ou la contrainte.
Si RESTRICT, alors tout objet dépendant de cette colonne ou de cette contrainte provoquera l'annulation de l'opération de suppression.
Si CASCADE, alors tous les objets dépendant de cette colonne ou contrainte seront supprimés.

Exemple 91

ALTER TABLE T_CLIENT
ADD CLI_PRENOM VARCHAR(25)

ALTER TABLE T_CLIENT
ADD CLI_DATE_NAISSANCE DATE,

ALTER TABLE T_CLIENT
ADD CONSTRAINT CHK_DATE_NAISSANCE CHECK (CLI_DATE_NAISSANCE BETWEEN '1880-01-01' AND '2020-01-01')

ATTENTION : ne pas tenter de rajouter une colonne avec l'attribut NOT NULL lorsque la table contient déjà des lignes. Pour cette opération, veuillez procéder en plusieurs étapes dans un script transactionné.

DROP est l'ordre de suppression. Sa syntaxe est on ne peut plus simple :

DROP {TABLE | DOMAIN | ASSERTION | VIEW } nom_objet

7-6-1 - Changer le nom ou le type d'une colonne

Ce cas n'est pas géré par un ordre simple de SQL. En effet cette modification est trop risquée pour être standardisée. Quid des données contenue dans la colonne au passage de CHAR en FLOAT ? Quid des références de cette colonne dans des vues, des contraintes, des triggers si l'on en change le nom ?

Mais il est possible de contourner le problème en réalisant un script transactionné. Certains SGBDR proposent un ordre ALTER étendu ou une procédure stockée (par exemple sp_rename de MS SQL Server).

Avant de lancer un tel script, il convient de s'assurer que la colonne ne fait l'objet d'aucune référence interne (contraintes de table par exemple) ou externe (vue, triggers…). Si c'est le cas, il faut impérativement modifier, désactiver ou supprimer ces éléments avant la modification de la colonne.

Voici les différentes étapes du script à mettre en œuvre :

- créer une colonne temporaire de même nom et même type (ALTER TABLE ADD… ;

- alimenter la colonne temporaire avec les valeurs de l'actuelle (UPDATE … ;

- supprimer l'actuelle colonne (ALTER TABLE DROP… ;

- créer une nouvelle colonne avec le nouveau nom et/ou le nouveau type (ALTER TABLE ADD… ;

- alimenter la nouvelle colonne avec les données de la colonne temporaire (UPDATE … ;

- supprimer la colonne temporaire (ALTER TABLE DROP …

Exemple 92 - modification d'une colonne CHAR(6) contenant une date courte format FR en date SQL :

-- condition de départ

CREATE TABLE T_IMPORT
(IMP_ID   INTEGER,
IMP_NOM  VARCHAR(16),
IMP_DATE CHAR(6))

INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')

-- le script de modification

ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)

UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE

ALTER TABLE
DROP IMP_DATE

ALTER TABLE
ADD IMP_DATE DATE

UPDATE T_IMPORT
SET IMP_DATE = CAST(CASE SUBSTRING(TMP_IMP_DATE, 5, 2)
WHEN < '03' THEN  '20'
ELSE '19'
END || SUBSTRING(TMP_IMP_DATE, 5, 2)
|| '-' || SUBSTRING(TMP_IMP_DATE, 3, 2)
|| '-' || SUBSTRING(TMP_IMP_DATE, 1, 2) AS DATE)

ALTER TABLE
DROP TMP_IMP_DATE

COMMIT

-- on aura noté que le pivot de date pour changement de siècle aura été géré dans le dernier update...

7-6-2 - Ajouter ou supprimer la contrainte NULL ou NOT NULL

Les étapes du script diffèrent très peu. Voici un exemple :

Exemple 93 - modification d'une colonne IMP_NOM en plaçant la contrainte NOT NULL :

-- condition de départ

CREATE TABLE T_IMPORT
(IMP_ID   INTEGER,
IMP_NOM  VARCHAR(16),
IMP_DATE CHAR(6))

INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321,  NULL,    '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')

-- le script de modification

ALTER TABLE T_IMPORT
ADD TMP_IMP_NOM VARCHAR(16)

UPDATE T_IMPORT
SET TMP_IMP_NOM = COALESCE(IMP_NOM, '')

ALTER TABLE
DROP IMP_NOM

ALTER TABLE
ADD IMP_NOM VARCHAR(16) NOT NULL

UPDATE T_IMPORT
SET IMP_NOM = TMP_IMP_NOM

ALTER TABLE
DROP TMP_IMP_NOM

COMMIT

-- on aura noté qu'afin d'éviter un rejet massif de notre script,
-- on place un nom constitué d'une chaîne vide grâce à l'opérateur
-- coalesce, dans le premier update

8 - Les vues

Les vues de la norme SQL 2 ne sont autres que des requêtes instanciées.
Elles sont nécessaires pour gérer finement les privilèges. Elles sont utiles pour masquer la complexité de certains modèles relationnels.

Voici la syntaxe SQL 2 pour définir une vue :

CREATE VIEW nom_vue [ ( nom_col1, [, nom_col2 ... ] ) ]
AS
requête_select
[WITH CHECK OPTIONS]

Exemple 94 - vue simplifiant un modèle

-- la table suivante :
CREATE TABLE T_TARIF
(TRF_ID     INTEGER PRIMARY KEY,
TRF_DATE   DATE,
PRD_ID     INTEGER,
TRF_VALEUR FLOAT)

-- permet de stocker l'évolution d'un tarif, sachant que celui-ci n'est applicable
-- pour un produit donné (PRD_ID) qu'à partir de la date TRF_DATE
INSERT INTO T_TARIF VALUES (1, '1996-01-01', 53, 123.45)
INSERT INTO T_TARIF VALUES (2, '1998-09-15', 53, 128.52)
INSERT INTO T_TARIF VALUES (3, '1999-12-31', 53, 147.28)
INSERT INTO T_TARIF VALUES (4, '1997-01-01', 89, 254.89)
INSERT INTO T_TARIF VALUES (5, '1999-12-31', 89, 259.99)
INSERT INTO T_TARIF VALUES (6, '1996-01-01', 97, 589.52)

-- pour des raisons de commodité d'interrogation des données, on voudrait
-- faire apparaître l'intervalle de validité du tarif plutôt que la date d'application

-- la vue suivante répond à cette attente
CREATE VIEW V_TARIF
AS
SELECT TRF_ID,  PRD_ID, TRF_DATE AS TRF_DATE_DEBUT,
(SELECT COALESCE(MIN(TRF_DATE) - INTERVAL 1 DAY, CURRENT_DATE)
FROM   T_TARIF T2
WHERE  T2.PRD_ID = T1.PRD_ID
AND T2.TRF_DATE > T1.TRF_DATE) AS TRF_DATE_FIN,
TRF_VALEUR
FROM   T_TARIF T1

SELECT *
FROM V_TARIF

TRF_ID  PRD_ID  TRF_DATE_DEBUT  TRF_DATE_FIN TRF_VALEUR
------- ------- --------------- ------------ ----------
1       53      1996-01-01      1998-09-14   123.45
2       53      1998-09-15      1999-12-30   128.52
3       53      1999-12-31      2002-09-03   147.28
4       89      1997-01-01      1999-12-30   254.89
5       89      1999-12-31      2002-09-03   259.99
6       97      1996-01-01      2002-09-03   589.52

Une vue peut être utilisée comme une table dans toute requête de type SELECT. Mais à la différence des tables, une vue peut être mise à jour (INSERT, UPDATE, DELETE) que si elle obéit à un certain nombre de conditions :

- ne porter que sur une table (pas de jointure) ;

- ne pas contenir de dédoublonnage (pas de mot clef DISTINCT) si la table n'a pas de clé ;

- contenir la clé de la table si la table en a une ;

- ne pas transformer les données (pas de concaténation, addition de colonne, calcul d'agrégat…) ;

- ne pas contenir de clause GROUP BY ou HAVING ;

- ne pas contenir de sous-requête ;

- répondre au filtre WHERE si la clause WITH CHECK OPTIONS est spécifié lors de la création de la vue.

Bien évidemment une vue peut porter sur une autre vue et pour que la nouvelle vue construite à partir d'une autre vue puisse être modifiée, il faut que les deux vues répondent aussi à ces critères.

En fait c'est plus simple qu'il n'y parait : il suffit que le SGBDR puisse retrouver trace de la ligne dans la table et de chaque valeur de chaque colonne.

Exemple 95 - vue restreignant l'accès aux colonnes

-- soit la table suivante :
CREATE TABLE T_EMPLOYE
(EMP_ID        INTEGER PRIMARY KEY,
EMP_MATRICULE CHAR(8),
EMP_TITRE     VARCHAR(4),
EMP_NOM       VARCHAR(32),
EMP_PRENOM    VARCHAR(32),
EMP_DATE_NAIS DATE,
EMP_SALAIRE   FLOAT,
EMP_STATUT    CHAR(8),
EMP_MAIL      VARCHAR(128),
EMP_TEL       CHAR(16))
-- permet de stocker les employés de l'entreprise

-- pour le syndicat, on pourra définir la vue suivante :
CREATE VIEW V_EMP_SYNDICAT
AS
SELECT EMP_MATRICULE, EMP_TITRE, EMP_NOM, EMP_PRENOM, EMP_DATE_NAIS, EMP_MAIL, EMP_TEL
FROM   T_EMPLOYE
-- elle ne peut être mise à jour, car la clé ne s'y trouve pas

-- pour le carnet d'adresses, on pourra définir la vue suivante
CREATE VIEW V_EMP_SYNDICAT
AS
SELECT EMP_ID, EMP_TITRE || ' ' || EMP_PRENOM || ' ' || EMP_NOM AS EMP_NOM_COMPLET, EMP_MAIL, EMP_TEL
FROM   T_EMPLOYE
-- elle ne peut être mise à jour à cause des transformations de données (concaténation au niveau du nom)

-- pour le service comptable, on pourra définir la vue suivante :
CREATE VIEW V_EMP_SYNDICAT
AS
SELECT EMP_ID, EMP_PRENOM, EMP_NOM, EMP_SALAIRE
FROM   T_EMPLOYE
WHERE  STATUT = 'ETAM'
WITH CHECK OPTIONS
-- elle pourra être mise à jour uniquement pour les salariés de type 'ETAM'

La clause WITH CHECK OPTION implique que si la vue peut être mise à jour, alors les valeurs modifiées insérées ou supprimées doivent répondre à la validation de la clause WHERE comme s'il s'agissait d'une contrainte.

Par exemple dans le cadre de la vue pour le service comptable, il n'est pas possible de faire :

UPDATE T_EMPLOYE
SET EMP_SALAIRE = EMP_SALAIRE + 100
WHERE STATUT = 'CADRE'

9 - Les informations de schéma

Toute base de données, tout SGBDR bien constitué permet de savoir ce qu'il contient. Ce sont les métadonnées ou le dictionnaire des données (souvent appelées à tort « tables système ») que la norme appelle « information de schéma ».

SQL 2 précise 23 vues permettant de connaitre les éléments constituant l'architecture de données du CATALOG du SGBDR. En voici la liste :

Élément du SGBDR

SQL_LANGUAGES

liste des langages supportés au niveau SQL API

Élément du CATALOG

SCHEMATA

liste des bases

Éléments d'une base

DOMAINS

liste des domaines de la base

TABLES

liste des tables de la base

VIEWS

liste des vues de la base

ASSERTIONS

liste des contraintes de la base

CHARACTER_SETS

liste des jeux de caractères de la base

COLLATIONS

liste des collations (schémas d'équivalence de caractères) de la base

TRANSLATIONS

liste des « translations » (schémas de remplacement de caractères) de la base

Éléments d'une table

COLUMNS

liste des colonnes de TOUTES les tables de la base

TABLE_CONSTRAINTS

liste des contraintes des tables de la base

REFERENTIAL_CONSTRAINTS

liste des intégrités référentielles de la base

CHECK_CONSTRAINTS

liste des contraintes de validité de la base

KEY_COLUMN_USAGE

liste des colonnes définissant les clés (primaire ou étrangère) de la base

CONSTRAINT_COLUMN_USAGE

liste des colonnes définissant les contraintes de la base

CONSTRAINT_TABLE_USAGE

liste des tables utilisée par les contraintes de la base

Éléments d'une vue

VIEW_TABLE_USAGE

liste des tables composant les vues de la base

VIEW_COLUMN_USAGE

liste des colonnes composant les vues de la base

Éléments d'un domaine

DOMAIN_CONSTRAINT

liste des contraintes des domaines de la base

DOMAIN_COLUMN_USAGE

liste des colonnes basées sur les domaines de la base

Privilèges

TABLE_PRIVILEGES

liste des privilèges des tables de la base

COLUMN_PRIVILEGES

liste des privilèges de colonnes de table de la base

USAGE_PRIVILEGES

liste des privilèges des autres objets de la base

En sus de ces vues, la norme SQL impose une table composée d'une unique ligne et d'une seule colonne contenant le nom du CATALOG. Cette table se nomme INFORMATION_SCHEMA_CATALOG_NAME.

Pour interroger ces vues, il faut en préciser l'origine qui est par défaut « INFORMATION_SCHEMA ».

Exemple 96

SELECT TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND TC.TABLE_NAME = 'T_EMPLOYE_EMP'

Cet exemple liste les clés étrangères et les colonnes associées de la table T_EMPLOYE_EMP.

ATTENTION : tous les SGBDR ne proposent pas ces vues standards pour accéder aux métadonnées. Voici quelques éléments pour certains SGBDR :

- DB2 : (SYSCAT.xxx) SYSCAT.TABLES, SYSCAT.SCHEMATA, SYSCAT.REFERENCES, SYSCAT.KEYCOLUSE… ;

- ORACLE : USER_CATALOG, USER_TABLES, ALL_TABLES, USER_SYNONYMS… ;

- INFORMIX : SYSTABLES, SYSREFERENCES, SYSSYNONYMS… ;

- SYBASE : SYSDATABASES, SYSOBJETCS, SYSKEYS… ;

- MS SQL SERVER : SYSDATABASES, SYSOBJETCS, SYSFOREIGNKEYS, SYSREFERENCES… ;

- INTERBASE : RDB$RELATIONS, RDB$FIELDS, RDB$DATABASE…

Dans tous les cas, si votre SGBDR supporte les vues standards de SQL 2, il vaut mieux les utiliser. Dans le cas contraire, les créer semble un moindre mal ! En effet, les vues sont garanties par la norme tandis que les tables « système » peuvent évoluer d'une version à l'autre du SGBDR…

NOTA : tous les objets d'une base ne sont pas toujours tous accessibles par les vues normalisées. Voici un exemple de requête interrogeant directement les tables système d'une base MS SQL Server à la recherche des objets « tiggers », « fonctions », « procédures stockées » et « vues » créées par l'utilisateur :

Exemple 97

-- les triggers
SELECT CAST('TRIGGER' AS VARCHAR(32)) AS TYPE_OBJET,
o1.name AS NOM_OBJET, o2.name AS TABLE_ASSOCIEE
FROM   sysobjects o1
JOIN  sysobjects o2
ON o1.parent_obj = o2.id
WHERE  o1.xtype = 'TR'
AND    o1.status >= 0
AND    o1.category = 0
UNION
-- les fonctions
SELECT CAST('FONCTION' AS VARCHAR(32)),
name, ''
FROM   sysobjects
WHERE  xtype = 'FN'
AND    status >= 0
AND    category = 0
UNION
-- les procédures stockées
SELECT CAST('PROCEDURE' AS VARCHAR(32)),
name, ''
FROM   sysobjects
WHERE  xtype = 'P'
AND    status >= 0
AND    category = 0
UNION
-- les vues
SELECT CAST('VUE' AS VARCHAR(32)),
name, ''
FROM   sysobjects
WHERE  xtype = 'V'
AND    status >= 0
AND    category = 0
ORDER BY TYPE_OBJET, NOM_OBJET, TABLE_ASSOCIEE

10 - Les index

Contrairement à une idée reçue, les index ne font nullement partie du SQL. Ce sont en revanche des éléments indispensables à une exploitation performante de base de données. En effet un index permet de spécifier au SGBDR qu'il convient de créer une structure de données adéquate afin de stocker les données dans un ordre précis. Par conséquent les recherches et en particulier les comparaisons, notamment pour les jointures, sont notablement accélérées. Dans le principe le gain de temps espéré est quadratique. Par exemple si une recherche sur une colonne dépourvue d'index met 144 secondes, avec un index cette même recherche sera supposée mettre 12 secondes (racine carrée de 144) !

Différents types d'index sont généralement proposés. Voici quelques exemples de techniques d'indexation :

- index en cluster : l'ordre des données répond à un ordre physique d'insertion, convient particulièrement pour les clés numériques auto-incrémentées (dans ce cas une table ne peut recevoir qu'un seul index de ce type) ;

- index en arbre équilibré : convient pour la plupart des types de données ;

- index en clé de hachage : convient pour des colonnes dont la dispersion est très importante. Un algorithme de hachage est mis en place (il s'agit en général d'une transformation injective) ;

- index bitmap : convient pour des colonnes à faible dispersion (idéal pour des colonnes booléennes).

En règle général les fabricants de SGBDR proposent un mécanisme de création d'index dont la syntaxe est proche des ordres basiques du SQL. C'est en général l'ordre CREATE INDEX.

Voici la syntaxe d'un tel ordre pour MS SQL Server :

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX nom_index
ON nom_table (col1 [, col2 ...] )
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = facteur_de_remplissage]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON groupe_de_fichiers]

La plupart du temps lorsque vous créez une contrainte de clé primaire, étrangère ou une contrainte d'unicité, le SGBDR implante automatiquement un index pour assurer le mécanisme de contrainte avec des performances correctes. En effet une contrainte d'unicité est facilitée si un tri sur les données de la colonne peut être activé très rapidement.

CONSEIL : pour une table donnée, il convient d'indexer dans l'ordre :

- les colonnes composant la clé ;

- les colonnes composant les clés étrangères ;

- les colonnes composant les contraintes d'unicité ;

- les colonnes dotées de contraintes de validité ;

- les colonnes fréquemment mises en relation, indépendamment des jointures naturelles ;

- les colonnes les plus sollicitées par les recherches.

Dans la mesure du possible on placera des index à ordre descendant pour les colonnes de type DATE, TIME et DATETIME.

11 - Résumé

partie en construction

Voici les différences entre les moteurs des bases de données :

Mise à jour des données

Paradox

Access

Sybase

SQL Server

Oracle

MySQL

InterBase

PostGreSQL

noms normatifs

Non

Oon

Non

CONNEXION

Non

Non

SESSION

Non

Non

CATALOG

Non

Oui

SCHEMA

Oui

Oui

jeu de caractères

Oui

Oui

collations

Oui

Oui (v2000)

translation

Non

Non

CHAR

Non

Oui

VARCHAR

Oui

Oui

NCHAR

Non

Oui

NVARCHAR

Non

Oui

BIT

Non

Non (1)

VARBINARY

Oui

Oui

INT

Oui

Oui

SMALLINT

Oui

Oui

FLOAT

Oui

Oui

DOUBLE PRECISION

Non

Oui

DECIMAL

Oui (BCD)

Oui

NUMERIC

Non

Oui

TIMESTAMP

Oui

Oui

TIME

Oui

DATE

Oui

INTERVAL

Non

BOOLEAN

Oui

Non

BLOB

Oui

Oui

CLOB / NCLOB

Oui/Non

Oui

ARRAY

Non

Non

ROW

Non

Non

REF

Non

Non

DOMAIN

Non

Non (2)

ASSERTION

Non

Non (2)

(1) le BIT SQL est limité à 1 BIT valant 0 ou 1
(2) non, mais mécanisme similaire