SQL > créer, modifier, vider des tables

CRÉATION D’UNE TABLE

CREATE TABLE nom_table (
   nom_colonne type DEFAULT valeur contrainte_de_colonne COLLATE collation,
   CONSTRAINT nom_contrainte
   contrainte_de_table [ { , colonne | contrainte_de_table }...
)

 

Contrainte de colonne

CONSTRAINT nom_contrainte
[NOT] NULL
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 }

 

Voici quelques exemple de création de table utilisant tantôt des types SQL tantôt des domaines.

 

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

 

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 majuscule et dont le prénom doit figurer dans la table de référence TR_PRENOM à la colonne PRN_PRENOM.

 

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

 

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 clef est le couple de colonne NOM/PRENOM.

 

7.1. Les contraintes de colonnes (verticales)

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ération particulières, lorsque l’on 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 évnetuelles confusions possible (minuscules/majuscules, caractères diacritiques distinct ou non). Voir paragraphe 4 à ce sujet

PRIMARY KEY : précise si la colonne est la clef 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éexitantes dans une colonne d’une autre table. Ce mécanisme s’apelle intégrité référentielle

NOTA : toutes ces contraintes peuvent être placées dans plusieurs colonnes, à l’exception de la contrainte de clef PRIMARY KEY qui ne peut être placée que sur une seule colonne. Pour faire de plusieurs colonnes une clef, 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é (ROLLBACK).

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

NULL - NOT NULL

Pour rendre la saisie d’une colonne obligatoire : NOT NULL, sinon NULL ou rien.

 

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

 

NOTE : les colonnes présentant une clef doivent être NOT NULL.

DEFAULT

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

 

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)

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

COLLATE

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

 

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ère. Pour de plus amples informations, voir "Jeu de caractères et séquence de collation".

PRIMARY KEY

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.

 

CREATE TABLE matable (
   c_ID INTEGER NOT NULL PRIMARY KEY,
   c_NOM 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 clef. 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 clef en tête de la description de la table pour des fins de lisibilité.

Exemple 71 - clef 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'.

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.

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 refuse de voir la présence de plusieurs marqueurs NULL dans la cas d’une contrainte d’unicité. D’autres comme InterBase refusent une contrainte d’unicité dépourvue d’une contrainte NOT NULL…

7.1.6. Validation (CHECK)

La contrainte CHECK de validation est celle qui offre le plus de possibilité. En contre partie son exécution est très coûteuse. 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 :

Sélectionnez

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.

 

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é, 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) colonnes d’une table et la (ou les) colonne composant la clef d’une autre table afin d’assurer les relations existantes et joindre les tables dans le 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 quelque soit les modiifications 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é à des fins didactiques :

Sélectionnez

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

 

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 clef étrangère PRS_ID de la table T_FACTURE1 et la clef de la table T_PERSONNE5 qui s’intitule aussi PRS_ID.

 

NOTA : il est très important que les noms des colonnes de jointure soit 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ésente 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 :

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é 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 personnet 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 maintient 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 !

CONTRAINTES DE TABLE

Clef multicolonne (PRIMARY KEY)

La clef 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 - clef 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))

UNIQUE

Peut être portée sur plusieurs colonnes. Dans ce cas chaque n-uplets de valeurs explicite doit être différents.Dans ce cas la syntaxe est :

CONSTRAINT nom_contrainte UNIQUE (liste_colonne)

Validation de ligne (CHECK)

Permet d’effectuer un contrôle de validation multicolonne au sein de la table.

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.

FOREIGN KEY / REFERENCES

Comme dans la cas d’une contrainte référentielle de colonne, il est possible de placer une contrainte d’intégrité portant sur plusieurs colonne. Ceci est d’autant plus important qu’il n’est pas rare de trouver des tables dont la clef 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)

 

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 clef étrangère composite PRS_NOM / PRS_PRENOM de la table T_FACTURE2 et la clef 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 :

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é 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 personnet 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.

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 la référence, clause MATCH

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

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

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’aplique toujours sauf si toutes les colonnes sont pourvues d’un marqueur NULL

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

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

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 desdonnées dans la table des factures et qu’il n’est pas possible d’insérer une facture pour une personne nonréférencée. Se 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ême 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 aux aussi supprimés. En cas de modification de la valeur de la clef, les valeurs des clefs étrangères qui le référence 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érence voit leur clef étrangère posséder le marqueur NULL . De même en cas de modification de la valeur de la clef. Le lien d’intégrité est alors brisé.
L’intérêt d’une telle manoeuvre est de permettre la suppression des lignes devenues orphelines de manière différé, 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 clef 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 lient d’intégrité référentielle. Bien entendu on veillera ensuite à rectifier la vrai 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 intéragissent créant ainsi ce que l’on apelle 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é au problème de l’oeuf et de la poule… C’est pour trancher ce dilemne que la déférabilité d’une contrainte a été défini par la norme SQL 2. Les SGBDR metant en oeuvre cette gestion, comme ORACLE, ne courrent 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 :

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 :

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 met au défi de pouvoir insérer quoi que ce soit dans l’une quelconque des tables, puisque l’une à besoin des informations de l’autre et vice versa…

Pour répondre à ce cas de figure, la norme SQL 2 à 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’apllique 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 à 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 :

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é :

 

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é référentielle !

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érement en contraintes de colonnes comme en contrainte de ligne. Ainsi une clef portant sur une seule colonne peut parfaitement être définie en tant que contrainte de table.

Ainsi les deux ordres suivants :

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ématiquemen des contraintes horizontales.
Simplement parce que :

• elle sont plus lisible
• elle sont nommées
• elle peuvent facilement être supprimées et réinsérées

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

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

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 onne 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 le 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 oeuvre :

• 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 grace à l'opérateur
-- coalesce, dans le premier update

LES VUES

Les vues ne sont que des requêtes instanciées, nécessaires pour gérer finement les privilèges, utiles pour masquer la complexité de certains modèles relationnel.

 

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 clef
• contenir la clef 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é, 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 clef ne s'y trouve pas

-- pour le carnet d'adresse 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 transformation 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 mis à 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éta données ou le dictionnaire des données (souvent appelées à tort "tables systèmes") que la norme apelle "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
Élements 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 clefs (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 clef é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éta donné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 standard 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èmes" 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èmes 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 particuliers 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 seconde (racine carré 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 clefs 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 clef 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 (ideal pour des colonnes booléennes)

En règle général les fabriquants 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 clef primaire, étrangère ou une contrainte d’unicité, le SGBDR implante automatiquement un index pour assurer la mécanisme de contrainte avec des performances correctes. En effet une contrainte d’unicité est facilité 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 clef
• les colonnes composant les clefs é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épendemenent 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