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

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 !

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).

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

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

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                                 "  --> "    "   --> "
et commercial                             &  --> &    &    --> &
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.

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

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 '

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.

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

TIMESTAMP

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

Exemple 39

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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

 

Sélectionnez

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 :

Sélectionnez INTERVAL debut [TO fin]

Sélectionnez 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

 

Sélectionnez

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

 

Sélectionnez

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▲

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.

On trouve couramment les types suivants dans certains SGBDR :

SMALLDATETIME

MONEY, SMALLMONEY

TINYINT

BIGINT

LONGTEXT, TEXT, NTEXT

IMAGE

LONGBLOB

BFILE

RAW, LONG RAW

ROWVERSION, UNIQUEIDENTIFIER, ROWID

ENUM

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

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

CURRENT_TIME[(p)]

CURRENT_TIMESTAMP[(p)]

LOCALTIME[(p)]

LOCALTIMESTAMP[(p)]

USER

CURRENT_USER

SESSION_USER

SYSTEM_USER

NULL

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.