Le langage SQL, la synthèse

Avant de créer une base de données, il convient de se connecter au serveur. C'est la phase d'authentification. Mais cette connexion suppose au moins la création d'un utilisateur, qui est un objet de la base de données. SQL nous donne la clé en considérant qu'il doit toujours exister un utilisateur particulier de nom PUBLIC. Cet utilisateur est d'ailleurs plus proche de la notion de rôle que de celle d'un véritable utilisateur SQL.

Le choix de l'utilisateur « public » comme connexion par défaut à l'installation est très critiquable pour des raisons de sécurité car cet utilisetru ne peut en aucun cas être supprimé. C'est pourquoi les éditeurs de SGBDR professionnels ont optés pour d'autres comptes, comme c'est le cas avec les utilisateurs de noms « administrator » et « default » pour Oracle Database ou encore "sa" pour Microsoft SQL Server.

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

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

Exemple 3.1 – Connexion au serveur par défaut

CONNECT TO DEFAULT;

L'ordre SQL de l’exemple 3.1 permet de se connecter 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.2 – Connexion à un serveur par son nom

CONNECT TO S_MON_SERVEUR;

Dans l’exemple 3.2 on se connecte au serveur identifié « S_MON_SERVEUR ».

Il est même possible de renommer ce serveur, c'est à dire se servir d'un alias :

Exemple 3.3 – Connexion à un serveur par son nom, renommé par son alias

CONNECT TO S_MON_SERVEUR AS SRV1;

Qui se connecte à « S_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 préalablement dans le SGBDR et qui sera associé à la connexion. Sans cette précision, le SGBDR emprunte le nom par défaut implanté par le constructeur ou encore l'utilisateur PUBLIC.

Exemple 3.4 – Connexion à un serveur par son nom, avec association d’un utilisateur SQL

CONNECT TO S_MON_SERVEUR USER U_MONA_LISA;

Se connecte au serveur « S_MON_SERVEUR » en empruntant l'identité U_MONA_LISA comme utilisateur.

Vous l'aurez compris, à une connexion est toujours associée un nom d'utilisateur. Un utilisateur SQL est un objet de la base de données et se définit aussi par un ordre SQL. Nous verrons cela au chapitre 8.

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 }

Ou 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 }

Nous vous conseillons d'utiliser le site Web "The Connection Strings Reference" https://www.connectionstrings.com/ pour savoir comment paramétrer la chaine de connexion propre à votre SGBDR, au middleware utilisé, et à la sécurité que vous voulez définir. Par exemple une chaine de connexion pour Oracle Database via ODBC en sécurité SQL se définie de la manière suivante : Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

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

AUTHORIZATION : l'identifiant d'autorisation doit impérativement être choisi parmi les mots clés suivants USER, CURRENT_USER, SESSION_USER et SYSTEM_USER ou bien encore en donnant un nom d'utilisateur spécifique.

CATALOG : Un catalogue n’est ni plus ni moins qu’une base de données. Ce terme peut paraître curieux, mais souvenons-nous que le terme database n’était pas encore en vogue à l’époque de la naissance du langage SQL ou l’on parlait de databank…

SCHEMA : un schéma est un conteneur logique au sein d’une base de données, une base de données pouvant contenir de nombreux schémas destinés à isoler les différents pans logiques des objets de la base.

TIME ZONE : un fuseau horaire est l'indication du décalage de l'heure locale de la session par rapport au temps universel (UTC : Unified Time Coordination). Chaque session peut donc se connecter avec une synchronisation de fuseau horaire différente. C'est assez pratique lorsque l'on veut développer une application internationale, notamment pour les sites web et que les internautes viennent se connecter de toute la planète.

CHARACTER SET : le jeu de caractère permet de définir quel sous ensemble de symboles est utilisé pour codifier les littéraux.

Le terme SQL CATALOG désigne communément ce que nous appelons base de données. Le terme SCHEMA, désigne le seul type d'objet logique directement lié au CATALOG, c’est-à-dire un conteneur de tables, vues, procédures…

III-2-3-1. Le CATALOG, ou la base de données▲

CREATE DATABASE B_FORUM
ON PRIMARY
( NAME = F_CATALOG,
  FILENAME = 'D:\DATASQL\FORUM\F_CATALOG.D0',
  SIZE = 10 GB,
  FILEGROWTH = 100 MB ),
FILEGROUP FG_DATA1
( NAME = F_DATA1,
  FILENAME = 'E:\DATASQL\FORUM\F_DATA1.D1',
  SIZE = 80 GB,
  MAXSIZE = 160 GB,
  FILEGROWTH = 250 MB ),
( NAME = F_DATA2,
  FILENAME = 'E:\DATASQL\FORUM\F_DATA1.D2',
  SIZE = 80 GB,
  MAXSIZE = 160 GB,
  FILEGROWTH = 250 MB ),
FILEGROUP FG_INDEX1
( NAME = F_INDEX1,
  FILENAME = 'F:\DATASQL\FORUM\F_INDEX1.I1',
  SIZE = 80 GB,
  MAXSIZE = 160 GB,
  FILEGROWTH = 250 MB )
LOG ON
( NAME = 'F_LOG',
  FILENAME = 'G:\LOGSQL\FORUM\F_LOG1.L1',
  SIZE = 100 GB)
COLLATE French_CI_AI;

III-2-3-2. Le SCHEMA, ou module d'une base de données▲

CREATE SCHEMA nom_schéma [ AUTHORIZATION propriétaire ]
   [ DEFAULT CHARACTER SET jeu_de_caractères ]
   [ PATH nom_schéma {, nom_schéma ... } ] ]
   [ <objet> | <privilège> [, <objet> | <privilège> ...] ]

<objet> ::= 
CREATE { DOMAIN | TABLE | VIEW | ASSERTION |
         CHARACTER SET | COLLATION | 
         TRANSLATION | TRIGGER | TYPE |
         PROCEDURE | FUNCTION | ROLE } définition_objet

<privilège> ::= 
GRANT définition_privilège

CREATE SCHEMA S_COMMERCIAL AUTHORIZATION U_MONA_LISA
DEFAULT CHARACTER SET LATIN1

CREATE VIEW V_CLIENT_CLI
AS
    SELECT * 
    FROM   T_CLIENT_CLI 
           FULL OUTER JOIN T_PROSPECT_PSP
              ON T_CLIENT_CLI.PRS_ID = T_PROSPECT_PSP.PRS_ID

CREATE TABLE T_CLIENT_CLI 
(PRS_ID INTEGER NOT NULL PRIMARY KEY REFERENCES T_PERSONNE_PRS (PRS_ID),
 CLI_NOM VARCHAR(100),
 CLI_SIRET CHAR(14) NOT NULL)

CREATE TABLE T_PROSPECT_PSP
(PRS_ID INTEGER NOT NULL PRIMARY KEY REFERENCES T_PERSONNE_PRS (PRS_ID),
 PSP_NOM CHAR(16),
 PSP_SIRET CHAR(14) NOT NULL)

CREATE TABLE T_PERSONNE_PRS
(PRS_ID INTEGER NOT NULL PRIMARY KEY)

CREATE ASSERTION AK_SIRET
CHECK NOT EXISTS
   (SELECT *
    FROM   T_CLIENT_CLI 
           INNER JOIN T_PROSPECT_PSP
              ON T_CLIENT_CLI.PRS_ID = T_PROSPECT_PSP.PRS_ID
              AND T_CLIENT_CLI.CLI_SIRET_ID <> T_PROSPECT_PSP.PSP_SIRET);

Nous verrons au chapitre 8 consacré à la sécurité, ce qu’est la notion de propriétaire, mais disons rapidement que c’est un utilisateur SQL, propriétaire de certains objets, et par conséquent détenant tous les pouvoirs sur tous les objets qu’il créé afin de transmettre ses privilèges et finalement supprimer ces objets. Sans cette notion de propriétaire il serait bien difficile de donner à d’autres utilisateurs des privilèges (droits d’exécuter telle ou telle commande sur tel ou tel objet) et plus encore de prendre la responsabilité de la destruction des objets.

Il n’existe que deux types de contraintes propres aux colonnes :

• la contrainte d’obligation (NOT NULL) ;
• la contrainte de valeur par défaut (DEFAULT …).

Ces contraintes ne portent jamais que sur la définition de la colonne, mais nous devons les assimiler plus globalement aux contraintes de table…

Une contrainte de table permet de restreindre les conditions d'acceptation des données de la table. Rappelons que, comme pour les contraintes de domaines, dans le cas où une condition de contrainte n'est pas vérifiée, il y a violation de contrainte et le lot de lignes que l'on tentait de mettre à jour (insertion, modification ou suppression) est rejeté en bloc, même si la violation ne porte que sur une seule occurrence d'une seule colonne pour un seule ligne dans un lot comportant de nombreuses lignes.

Les contraintes permettent de rendre la base cohérente en respectant l'intégrité des données.

Outre les contraintes propres aux colonnes que nous avons vu au paragraphe précédent, SQL admet les contraintes suivantes :

• clé primaire (PRIMARY KEY) ;
• clé subrogée, unicité (UNIQUE) ;
• clé étrangère (FOREIGN KEY) ;
• validation de valeur (CHECK).

Nous les avons réparti en deux principales fonctionnalités : les contraintes validant les valeurs d'une colonne (NOT NULL, DEFAULT, CHECK) et les contraintes de clé (PRIMARY KEY, UNIQUE, FOREIGN KEY).

Comme nous l'avons fait pour les objets de la base, une bonne habitude consiste à donner un nom pertinent aux contraintes. Vous trouverez en annexe 2 la convention de nommage utilisée pour les contraintes.

III-3-2-1. Contraintes validant les valeurs des colonnes▲

<contrainte_de_valeur> ::=
   [ CONSTRAINT nom_contrainte]
 { [ NOT ] NULL |
   DEFAULT <expression_défaut> |
   CHECK ( <expression_validation> ) }

CONSTRAINT nom_contrainte
   CHECK ( <expression_validation> )

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER      NOT NULL,
 USR_LOGIN          VARCHAR(256) CONSTRAINT NK_MAIL NOT NULL,
 USR_TITRE          CHAR(6)      NULL,
 USR_NOM            CHAR(32)     NULL,
 USR_PRENOM         VARCHAR(32)  NULL,
 USR_ORGANISATION   VARCHAR(128));

CREATE TABLE S_COMMERCIAL.T_CLIENT_CLI
(CLI_NOM        VARCHAR(32),
 CLI_CREATION   DATE DEFAULT CURRENT_DATE,
 CLI_REMISE     D_POURCENT CONSTRAINT DK_REMISE DEFAULT 10.0);

CREATE TABLE S_COMPTA.T_FACTURE_FCT
(FCT_NUMERO         INTEGER,
 FCT_DATE_EMISSION  DATE     CONSTRAINT DF_FCT_DTE DEFAULT CURRENT_DATE,
 FCT_DATE_PAIEMENT  DATE     DEFAULT CURRENT_DATE + 30 DAY);

CREATE DOMAIN D_POURCENT
AS FLOAT
DEFAULT 0.0
CHECK (VALUE IS NOT NULL 
   AND VALUE BETWEEN 0 AND 100)

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_NOM CHAR(32)
    CONSTRAINT CK_USR_NOM
       CHECK (TRANSLATE(VALUE COLLATE French_CI_AI, 
                         'ABCDEFGHIJKLMNOPQRSTUVWXYZ –''', 
                         '                             ') = '' 
              AND SUBSTRING(VALUE FROM 1 FOR 1) 
                  NOT IN (' ', '-' , '''')
              AND SUBSTRING(VALUE FROM CHARACTER_LENGTH(VALUE) -1 FOR 1)
                  NOT IN (' ', '-' , '''')
              AND VALUE COLLATE French_CS_AI = UPPER(VALUE)),
 USR_SEXE CHAR(1)           
    CHECK (VALUE IN ('H', 'F')),
 USR_PASS NVARCHAR(32) NOT NULL
    CHECK (CHARACTER_LENGTH(VALUE) >= 8
           AND VALUE <> UPPER(VALUE) COLLATE French_CS_AS 
           AND VALUE <> TRANSLATE(VALUE, '0123456789', 
                                         '         ')
           AND VALUE <> TRANSLATE(VALUE COLLATE French_CI_AS,
                                  'àâäéèêëîïôöùûüÿ', 
                                  '               ')),
 USR_ORGANISATION VARCHAR(128)
    CHECK (VALUE COLLATE French_CS_AI = UPPER(VALUE)));

CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_NUMERO                 INTEGER,
 FCT_MONTANT_TOTAL          DECIMAL(16,2),
 FCT_DATE_EMISSION          DATE,
 FCT_DATE_LIMITE_PAIEMENT   DATE,
 CONSTRAINT CK_FCT_DLM 
    CHECK (FCT_DATE_LIMITE_PAIEMENT <= FCT_DATE_EMISSION + 30 DAY),
 FCT_MODE_PAIEMENT          VARCHAR(16) 
    CHECK(VALUE IN ('CHEQUE', 'ESPECE', 'CARTE BANCAIRE', 'VIREMENT')),
 CONSTRAINT CK_FCT_MDP 
    CHECK (   ( FCT_MODE_PAIEMENT = 'ESPECE' 
                AND FCT_MONTANT_TOTAL <= 3000 )
           OR ( FCT_MODE_PAIEMENT = 'CARTE BANCAIRE' 
                AND FCT_MONTANT_TOTAL > 15 )));

CREATE TABLE S_COM.T_CLIENT_CLI
(CLI_NUMERO                      INTEGER,
 CLI_NOM                         VARCHAR(32),
 CLI_REMISE_MAXI_POURCENT        FLOAT,
 CLI_REMISE_MAXI_MONTANT         DECIMAL(16,2));

CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_NUMERO                      INTEGER,
 CLI_NUMERO                      INTEGER,
 FCT_DATE_EMISSION               DATE,
 FCT_MONTANT_TOTAL_AVANT_REMISE  DECIMAL(16,2),
 FCT_MONTANT_REMISE_TOTALE       DECIMAL(16,2),
 CONSTRAINT CK_FCT_REMISES 
      CHECK (FCT_MONTANT_REMISE_TOTALE 
             <= (SELECT CLI_REMISE_MAXI_MONTANT
                 FROM   S_COM.T_CLIENT_CLI
                 WHERE  S_COM.T_CLIENT_CLI.CLI_NUMERO 
                        = S_COM.T_FACTURE_FCT.CLI_NUMERO)
             AND FCT_MONTANT_REMISE_TOTALE 
                 <= (SELECT CAST(CLI_REMISE_MAXI_POURCENT  
                               * FCT_MONTANT_TOTAL_AVANT_REMISE 
                               / 100.0 AS DECIMAL(16,2))
                     FROM   S_COM.T_CLIENT_CLI
                     WHERE  S_COM.T_CLIENT_CLI.CLI_NUMERO 
                            = S_COM.T_FACTURE_FCT.CLI_NUMERO)));

CREATE TABLE S_COM.T_PATIENT_PTN
(PTN_NUM_INSEE_SEXE              CHAR(1),
 PTN_NUM_INSEE_DATE_NAISSANCE    CHAR(4),
 PTN_NUM_INSEE_COMMUNE           CHAR(5),
 PTN_NUM_INSEE_RANG              CHAR(3),
 PTN_CLE_INSEE                   CHAR(2),
 PTN_NOM                         CHAR(32),
 PTN_PRENOM                      VARCHAR(25),
 CONSTRAINT CK_PTN_NNI 
    CHECK (S_ROUTINES.F_VALIDE_NNI(PTN_NUMERO_INSEE_SEXE,
                                   PTN_NUM_INSEE_DATE_NAISSANCE,
                                   PTN_NUM_INSEE_COMMUNE,
                                   PTN_NUM_INSEE_RANG,
                                   PTN_CLE_INSEE)),
 CONSTRAINT CK_PTN_NOM 
    CHECK (S_ROUTINES.F_VALIDE_NOM(PTN_NOM, 'MAJUSCULES')),
 CONSTRAINT CK_PTN_PRENOM 
    CHECK (S_ROUTINES.F_VALIDE_NOM(PTN_NOM, 'INITMAJ')));

III-3-2-2. Contrainte de clé▲

<contrainte_de_clé> ::=
   [ CONSTRAINT nom_contrainte ]
 { PRIMARY KEY |
   UNIQUE |
   [FOREIGN KEY ] REFERENCES <table_mere> ( <colonne_de_référence> ) }

<contrainte_de_clé> ::=
  [ CONSTRAINT nom_contrainte ]
 { PRIMARY KEY ( <liste_colonne> ) |
   UNIQUE ( <liste_colonne> ) |
   FOREIGN KEY ( <liste_colonne> ) 
      REFERENCES <table_mere> ( <liste_colonnes_de_référence> ) 
         [ <clause_de_validation>           ] 
         [ <clause_de_gestion_modification> ] 
         [ <clause_de_gestion_suppression>  ] }

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER      NOT NULL CONSTRAINT PK_USR PRIMARY KEY,
 USR_LOGIN          VARCHAR(256) NOT NULL);

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER      CONSTRAINT PK_USR PRIMARY KEY,
 USR_LOGIN           VARCHAR(256) NOT NULL);

CREATE TABLE S_GEO.T_COMMUNE_CMN
(CMN_CODE_DEPARTEMENT    VARCHAR(3) NOT NULL,
 CMN_CODE_COMMUNE        VARCHAR(3) NOT NULL,
 CMN_NOM                 VARCHAR(64),
 CONSTRAINT PK_CMN (CMN_CODE_DEPARTEMENT, CMN_CODE_COMMUNE));

[ CONSTRAINT nom_contrainte ] 
   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <liste_colonne> )

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER      NOT NULL PRIMARY KEY,
 USR_NOM            CHAR(32)     NOT NULL,
 USR_LOGIN          VARCHAR(256) UNIQUE);

CREATE TABLE S_RH.T_EMPLOYE_EMP
(EMP_ID                          INT NOT NULL PRIMARY KEY,
 EMP_NUM_INSEE_SEXE              CHAR(1),
 EMP_NUM_INSEE_DATE_NAISSANCE    CHAR(4),
 EMP_NUM_INSEE_COMMUNE           CHAR(5),
 EMP_NUM_INSEE_RANG              CHAR(3),
 EMP_CLE_INSEE                   CHAR(2),
 CONSTRAINT UK_EMP_NNI 
    UNIQUE(EMP_NUM_INSEE_SEXE,
           EMP_NUM_INSEE_DATE_NAISSANCE,
           EMP_NUM_INSEE_COMMUNE,
           EMP_NUM_INSEE_RANG),
 EMP_NOM                         CHAR(32),
 EMP_PRENOM                      VARCHAR(25),
 EMP_MATRICULE                   SMALLINT UNIQUE,
 EMP_LOGIN                       VARCHAR(256) UNIQUE,
 EMP_SOIGNANT                    BOOLEAN,
 EMP_RPPS                        CHAR(12),
 UNIQUE (EMP_SOIGNANT, EMP_RPPS);

[ CONSTRAINT nom_contrainte ]
   [FOREIGN KEY ] REFERENCES <table_mere> ( <colonne_de_référence> )
      [ <clause_de _validation>           ] 
      [ <clause_de _gestion_modification> ] 
      [ <clause_de _gestion_suppression>  ]

CONSTRAINT nom_contrainte 
   FOREIGN KEY ( <liste_colonne> )
      REFERENCES <table_mere> ( <liste_colonnes_de_référence> ) 
         [ <clause_de _validation>           ] 
         [ <clause_de _gestion_modification> ] 
         [ <clause_de _gestion_suppression>  ]

CREATE TABLE S_ADM.T_PRENOM_PRN
(PRN_ID             INTEGER NOT NULL PRIMARY KEY,
 PRN_PRENOM_USUEL   VARCHAR(32) NOT NULL UNIQUE)

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_NOM            CHAR(32) NOT NULL,
 PRN_ID             INTEGER 
                    REFERENCES S_ADM.T_PRENOM_PRN (PRN_ID))

CREATE TABLE S_HOPITAL.T_PERSONNE_PHYSIQUE_PPS
(PPS_ID                          INT NOT NULL PRIMARY KEY,
 PPS_NOM                         CHAR(32) NOT NULL,
 PPS_PRENOM                      VARCHAR(25));

CREATE TABLE S_HOPITAL.T_PATIENT_PAT
(PRS_ID                          INT NOT NULL PRIMARY KEY 
    REFERENCES S_HOPITAL.T_PERSONNE_PHYSIQUE_PPS (PPS_ID),
 PPS_NUM_INSEE_SEXE              CHAR(1) NOT NULL,
 PPS_NUM_INSEE_DATE_NAISSANCE    CHAR(4) NOT NULL,
 PPS_NUM_INSEE_COMMUNE           CHAR(5) NOT NULL,
 PPS_NUM_INSEE_RANG              CHAR(3) NOT NULL,
 CONSTRAINT UK_EMP_NNI 
    UNIQUE(PPS_NUM_INSEE_SEXE,
           PPS_NUM_INSEE_DATE_NAISSANCE,
           PPS_NUM_INSEE_COMMUNE,
           PPS_NUM_INSEE_RANG),
 PAT_DATE_NAISSANCE              DATE NOT NULL);

CREATE TABLE S_HOPITAL.T_SOIGNANT_TYPE_SGT
(SGT_ID                          INT NOT NULL PRIMARY KEY,
 SGT_LIBELLE                     VARCHAR(32) NOT NULL UNIQUE);

CREATE TABLE S_HOPITAL.T_SOIGNANT_SPECIALITE_SST
(SST_ID                          INT NOT NULL PRIMARY KEY,
 SST_LIBELLE                     VARCHAR(32) NOT NULL UNIQUE);

CREATE TABLE S_HOPITAL.T_SOIGNANT_SGN
(PRS_ID        INT NOT NULL PRIMARY KEY 
    REFERENCES S_HOPITAL.T_PERSONNE_PHYSIQUE_PPS (PPS_ID),
 SGT_ID        INT NOT NULL 
    REFERENCES S_HOPITAL.T_SOIGNANT_TYPE_SGT (SGT_ID),
 SST_ID        INT
    REFERENCES S_HOPITAL.T_SOIGNANT_SPECIALITE_SST (SST_ID),
 CONSTRAINTS CK_SGN_SI_MEDECIN 
    CHECK (SST_ID IS NULL 
           AND SGT_ID = (SELECT SGT_ID 
                         FROM S_HOPITAL.T_SOIGNANT_TYPE_SGT
                         WHERE SGT_LIBELLE <> 'Médecin')
       OR  SST_ID IS NOT NULL 
           AND SGT_ID = (SELECT SGT_ID 
                         FROM S_HOPITAL.T_SOIGNANT_TYPE_SGT
                         WHERE SGT_LIBELLE = 'Médecin')) ,
 SGN_RPPS                        CHAR(12) UNIQUE);

CREATE TABLE S_HOPITAL.T_ORDONANCE_ODC
(ODC_ID                          INT NOT NULL PRIMARY KEY,
 ODC_DATE                        DATE NOT NULL DEFAULT CURRENT_DATE,
 PPS_NUM_INSEE_SEXE              CHAR(1) NOT NULL,
 PPS_NUM_INSEE_DATE_NAISSANCE    CHAR(4) NOT NULL,
 PPS_NUM_INSEE_COMMUNE           CHAR(5) NOT NULL,
 PPS_NUM_INSEE_RANG              CHAR(3) NOT NULL,
 SGN_RPPS                        CHAR(12) NOT NULL 
    REFERENCES S_HOPITAL.T_SOIGNANT_SGN (SGN_RPPS)
    CHECK (VALUE IN (SELECT SGN_RPPS 
                     FROM S_HOPITAL.T_SOIGNANT_SGN 
                     WHERE SGT_ID = (SELECT SGT_ID 
                                     FROM   S_HOPITAL.T_SOIGNANT_TYPE_SGT
                                     WHERE  SGT_LIBELLE = 'Médecin'))),
 FOREIGN KEY (PPS_NUM_INSEE_SEXE,
              PPS_NUM_INSEE_DATE_NAISSANCE,
              PPS_NUM_INSEE_COMMUNE,
              PPS_NUM_INSEE_RANG)
    REFERENCES S_HOPITAL.T_PATIENT_PAT
             (PPS_NUM_INSEE_SEXE,
              PPS_NUM_INSEE_DATE_NAISSANCE,
              PPS_NUM_INSEE_COMMUNE,
              PPS_NUM_INSEE_RANG));

INSERT INTO S_HOPITAL.T_PERSONNE_PHYSIQUE_PPS VALUES
(1, 'PARÉ', 'Ambroise'),     (2, 'CHARCOT', 'Jean-Martin'),
(3, 'SCHWEITZER', 'Albert'), (4, 'BARNARD', 'Christiaan'),
(5, 'ALZHEIMER', 'Aloïs'),   (6, 'FLEMMING', 'Alexander'),
(7, 'FREUD', 'Sigmund'),     (8, 'HIPPOCRATE', NULL),
(9, 'KOCH', 'Robert'),       (10, 'PAVLOV', 'Ivan'),
(11, 'MORIN', 'Marie'),      (12, 'LADOIX', 'Albert'),
(13, 'DUPONT', 'Patrick'),   (14, 'DUPONT', 'Marc'),
(15, 'ROUX', 'Paul'),        (16, 'GENTILUCI', 'Maurizio'),
(17, 'BEN BOUZA', 'Mounir'), (18, 'KLEIN', 'Maurice'),
(19, 'SANCHEZ', 'Manuel'),   (20, 'MAC CULLOCH', 'Kenneth');

INSERT INTO S_HOPITAL.T_PATIENT_PAT VALUES 
(17, '2', '8812', '43120', '007', '1988-12-20'),
(20, '1', '6001', '75112', '124', '1960-01-13');

INSERT INTO S_HOPITAL.T_SOIGNANT_TYPE_SGT VALUES 
(201, 'Médecin'), (202, 'Kiné'), (203, 'Infirmier');

INSERT INTO S_HOPITAL.T_SOIGNANT_SPECIALITE_SST VALUES
(311, 'Urologue'),     (312, 'Gastro-enterologue'), 
(313, 'Gynécologue'),  (314, 'Neurologue'), 
(316, 'Orthopédiste'), (317, 'Pédiatre');

INSERT INTO S_HOPITAL.T_SOIGNANT_SGN VALUES 
(1, 201, 316, '14578415'),   (2, 201, 314, '12548785'),
(11, 203, NULL, '74519854'), (12, 202, NULL, '65875414');

INSERT INTO S_HOPITAL.T_ORDONANCE_ODC VALUES
(1001, '2022-12-08', '2', '8812', '43120', '007', '14578415'),
(1002, '2021-09-16', '2', '8812', '43120', '007', '14578415'),
(1003, '2022-12-05', '1', '6001', '75112', '124', '12548785');

DELETE FROM S_HOPITAL.T_ORDONANCE_ODC;
DELETE FROM S_HOPITAL.T_SOIGNANT_SGN;
DELETE FROM S_HOPITAL.T_SOIGNANT_SPECIALITE_SST;
DELETE FROM S_HOPITAL.T_SOIGNANT_TYPE_SGT;
DELETE FROM S_HOPITAL.T_PATIENT_PAT;
DELETE FROM S_HOPITAL.T_PERSONNE_PHYSIQUE_PPS;

La clause MATCH n’a d’intérêt que si la clé étrangère est composée de plusieurs colonnes. Elle précise la manière dont la contrainte valide ou refuse l'opération d'insertion ou de mise à jour en fonction des colonnes en jeu lorsque les valeurs de certaines colonnes sont manquantes (marqueurs NULL).

MATCH SIMPLE implique que :

• la contrainte s'applique si toutes les colonnes de la clé étrangère sont renseignées ;
• si une colonne au moins possède un marqueur NULL, la contrainte de clé étrangère ne s'applique pas.

Il y a donc violation de contrainte si les valeurs divergent et qu'elles sont toutes renseignées. Dans le cas ou une colonne n’est pas renseignée, la contrainte est ignorée.

MATCH PARTIAL implique que :

• La contrainte de clé étrangère s'applique pour toutes les colonnes renseignées, c’est-à-dire qu’il doit exister au moins une ligne dans la table de référence qui vérifie toutes les valeurs des colonnes renseignées.

Il y a donc violation de la contrainte si au moins une valeur renseignée diverge.

MATCH FULL implique que :

• Les colonnes composant la clé doivent être toutes vides (NULL) ou toute renseignées, et dans ce dernier cas seulement, la référence est vérifiée.

Il y a donc violation de la contrainte si les valeurs divergent ou si une colonne est renseignée et l'autre pas.

A défaut de la spécifier c'est l'option MATCH SIMPLE qui s'impose par défaut.

Pour mieux comprendre le fonctionnement de cette clause, les exemples 3.23 à 3.25 montre un modèle constitué d’une table mère (calendrier), d’une table fille (faits historiques) et une tentative d’insertion de lignes dans la table des événements de l’Histoire…

Exemple 3.23 – 3.25

Exemple 3.23* – Une table de référence avec une contrainte UNIQUE multicolonne

CREATE TABLE S_TALLY.T_CALENDRIER_CLD
(CLD_DATE       DATE PRIMARY KEY,
 CLD_AN         SMALLINT NOT NULL CHECK(VALUE BETWEEN 1 AND 9999),
 CLD_MOIS       SMALLINT NOT NULL CHECK(VALUE BETWEEN 1 AND 12),
 CLD_JOUR       SMALLINT NOT NULL CHECK(VALUE BETWEEN 1 AND 31),
 CONSTRAINT UK_CLD_AN_MOIS_JOUR UNIQUE (CLD_AN, CLD_MOIS, CLD_JOUR)) ;

Supposons que la table S_TALLY.T_CALENDRIER_CLD soit remplie avec toutes les données des dates des années de 1900 à l’an 2000, les colonnes CLD_AN, CLD_MOIS et CLD_JOUR étant respectivement, l’année, le mois et le jour du mois de chaque CLD_DATE… :

Et une table de faits historiques constituée comme montre l’exemple 3.24 :

Exemple 3.24* – Table de faits historiques faisant référence aux dates d’une table de calendrier

CREATE TABLE S_HISTORY.T_EVENEMENT_EVT
(EVT_ID            INTEGER PRIMARY KEY,
 CLD_AN            SMALLINT,
 CLD_MOIS          SMALLINT,
 CLD_JOUR          SMALLINT,
 EVT_DESCRIPTION   VARCHAR(256) NOT NULL,
 CONSTRAINT FK_EVT_AN_MOIS_JOUR 
    FOREIGN KEY (CLD_AN, CLD_MOIS, CLD_JOUR)
    REFERENCES S_TALLY.T_CALENDRIER_CLD (CLD_AN, CLD_MOIS, CLD_JOUR));

Si la contrainte FK_EVT_AN_MOIS_JOUR est en MATCH SIMPLE, alors les insertions suivantes sont possibles :

Exemple 3.25* – Tentatives d’insertion dans la table des faits historiques liée par intégrité référentielle à la table de calendrier

INSERT INTO S_HISTORY.T_EVENEMENT_EVT VALUES
(1, 1969, 7, 20, 'Premier pas de l''homme sur la lune'),
(2, 1945, 8, 6, 'Explosion de la bombe atomique à Hiroshima, Japon'),
(3, NULL, NULL, NULL, 'J.-M. Le Pen devient Président de la République'),
(4, NULL, 25, 212, 'Disparition d''Harry Potter dans la 4e dimension'),
(5, 1968, 5, NULL, 'Manifestations d''étudiants et grèves générales');

Si la contrainte FK_EVT_AN_MOIS_JOUR est en MATCH PARTIAL, alors la quatrième ligne (disparition d’Harry Potter) fait échouer le lot de requête car un mois 25 comme un jour de mois 212 n’existe pas.

Si la contrainte FK_EVT_AN_MOIS_JOUR est en MATCH FULL, alors la quatrième ligne (disparition d’Harry Potter) comme la cinquième ligne (Mai 68) font échouer le lot de requête car la clé étrangère est partiellement vide.

Rares sont les SGBDR qui en pratique mettent en œuvre toutes les options possibles de l’option MATCH pour la validation des références. En l’absence de cette clause, il est possible de programmer des déclencheurs car l’intégrité référentielle, comme nous l’avons déjà dit, peut être réalisée de manière procédurale… Nous verrons un tel cas au chapitre 7.

Au cours de la vie de la base de données, il est possible que l'on soit amené à supprimer la ligne qui sert de référence (DELETE), comme à en modifier la valeur de la clé (UPDATE). Une telle manipulation des données de la table mère n'est pas sans conséquence dans le maintient de l'intégrité référentielle en regard des lignes des tables filles.

Le mode de gestion de l'intégrité consiste à se poser la question de la règle qui doit être mise en œuvre dans le cas ou l'on tente de modifier les données de référence qu’utilise différentes intégrités référentielles.

Les différents modes de gestion de l'intégrité référentielle que propose SQL sont les suivants :

NO ACTION, RESTRICT, CASCADE, SET DEFAULT, SET NULL et ne peuvent s'appliquer qu'aux ordres SQL UPDATE (modification de données) et DELETE (suppression de lignes).

ON DELETE NO ACTION / ON UPDATE NO ACTION :

Toute action de modification de clé ou de suppression de ligne dans la table mère échoue pour les lignes en relation d'intégrité référentielle entre table mère et fille.

Dans le cas du NO ACTION, il y a blocage de l'ordre SQL UPDATE ou DELETE dans le but de maintenir le lien d'intégrité tel quel. Ce blocage intervient en fin de transaction si la contrainte est déférée.

ON DELETE RESTRICT / ON UPDATE RESTRICT :

Similaire à NO ACTION, mais le blocage intervient immédiatement sur l'ordre SQL qui a engendré le viol de la contrainte.

ON DELETE CASCADE / ON UPDATE CASCADE :

Toute modification de clé ou suppression de ligne dans la table mère est répercutée dans la table fille : si l'on entreprend une suppression de lignes de référence les lignes de la table fille en regard sont elles aussi supprimées. Si l'on modifie des valeurs de clés qui servent de référence, alors ces valeurs modifiées sont répercutées dans la table fille.

Dans le cas du CASCADE, il y répercussion de l'ordre SQL avec modification de valeur ou suppression de lignes afin de maintenir ou d'évacuer le lien d'intégrité.

ON DELETE SET NULL / ON UPDATE SET NULL :

Toute action de modification ou de suppression dans la table mère est répercutée dans la table fille par la suppression des valeurs des clés étrangères, qui sont remplacées par des marqueurs NULL.

Dans le cas du SET NULL, il y suppression des valeurs des clés étrangères pour les lignes concernées, ce qui conduit à un déréférencement du lien d'intégrité.

En pratique, cela suppose que la clé étrangère est « NULLable » sinon cela n’a aucun intérêt.

ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT :

Toute action de modification ou de suppression dans la table mère est répercutée dans la table fille par la mise en place des valeurs spécifiées par défaut pour les colonnes des clés étrangères.

Dans le cas du SET DEFAULT, il y mise en place des valeurs par défaut des clés étrangères pour les lignes concernées, ce qui conduit à un glissement des valeurs de références du lien d'intégrité.

En pratique, cela suppose que la clé étrangère est pourvue d’une contrainte de défaut et que cette valeur est renseignée dans la référence, sinon cela n’a aucun intérêt.

À ce stade il convient de préciser les éléments suivants concernant le fonctionnement du mode de gestion de la référence :

• Autant le mode NO ACTION / RESTRICT est toujours possible et s'avère être celui qui opère par défaut, autant les autres modes sont dépendants de facteurs complémentaires tel que d'autres contraintes de clé étrangères ou bien l'absence de spécification de valeurs par défaut ou encore l'obligation de spécification de valeur (NOT NULL).
• Le mode cascade est très tentant, mais son coût de traitement peut s'avérer élevé. Mal maîtrisé il peut se révéler bloquant et devenir totalement contre performant, notamment dans le cas de cascades multiples ou de combinaisons de cascades et NO ACTION / RESTRICT.
• L'intérêt du SET NULL est de permettre la suppression des lignes devenues orphelines de manière différée, par exemple dans un traitement par lot intervenant aux heures creuses.
• L'intérêt du SET DEFAULT réside dans la possibilité de définir une référence particulière qui concentre les efforts de gestion des références obsolètes dans un traitement spécifique, par exemple le client 0 ou moi-même et, bien entendu de différer la suppression physique des lignes…
• NO ACTION est le mode par défaut en l'absence de spécification.

Exemple 3.26* – Tables référencées par différentes intégrités référentielles de différents modes

CREATE TABLE S_COM.T_CLIENT_CLI
(CLI_ID           INTEGER NOT NULL PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL);

CREATE TABLE S_COM.T_COMMANDE_CMD
(CMD_ID           INTEGER NOT NULL PRIMARY KEY,
 CMD_DATE         DATE NOT NULL DEFAULT CURRENT_DATE,
 CLI_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID)
       ON DELETE CASCADE);

CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_ID           INTEGER NOT NULL PRIMARY KEY,
 FCT_DATE         DATE NOT NULL DEFAULT CURRENT_DATE,
 CLI_ID           INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
 FCT_TOTAL_TTC    DECIMAL(16,2) NOT NULL,
 FCT_ECHEANCE     DATE NOT NULL DEFAULT CURRENT_DATE + 30 DAY);

L’exemple 3.26 montre les tables client, produit, commande, ligne de commande et facture dont l’intégrité référentielle entre les tables est gérée par différents modes (NO ACTION et CASCADE).

La représentation schématique de cette intrication est montrée par la figure 3.2 :

Avec une telle base la suppression d'un client entraîne la suppression de ses commandes. Mais d'un autre côté la suppression d'un client n'est pas possible si des factures portent la référence de ce client.

La figure 3.2 montre les identifiants des clients dans les différentes tables. La suppression des clients dont CLI_ID vaut 1 ou 2 réussie car l’intégrité en mode CASCADE est gérée entre la table des commandes et celle des clients.

Pour le client 3, rien n’est supprimé, ni le client, ni ses commandes ni les factures car la transaction échoue au niveau de la facture (NO ACTION).

Pour le client 4 là aussi la suppression est impossible car il a des factures…

Comme la suppression d'un client est un ordre atomique, si ce client à des factures il ne sera pas possible de le supprimer et la violation de la contrainte entraînant l'abandon de l'ordre, aucune commande ne sera supprimée.

En revanche la suppression en cascade se produira bien si ce client à des commandes mais n'a pas encore été facturé. Et c’est bien ce que nous voudrions dans la vraie vie, supprimer les commandes qui ne font l’objet d’aucun paiement !

En ce qui concerne les contraintes de validation, il est extrêmement dangereux et très contre-performant de faire référence à des données externes à la table. De plus, dans un tel cas, il serait possible d’utiliser la contrainte CHECK comme substitut à la contrainte FOREIGN KEY (ce que nous avons vu dans l’exemple 3.21 avec la contrainte CHECK de nom CK_SGN_SI_MEDECIN).

Dangereux, car pour des raisons de performance, certains SGBDR exécutent en parallèle les commandes SQL. Dès lors, des effets de bords pourraient se faire sentir sur les jointures résultant des opérations de vérification des contraintes de validation. À titre d’exemple, un calcul parallélisé comptant les occurrences de données externes à la table pourrait comptabiliser plusieurs fois les mêmes lignes de la table externe (recoupement de threads), pour différentes occurrences de la table interne. Cela entrainerait de facto l’interdiction du parallélisme et donc des problèmes de performance.

Pour ces mêmes raisons de performance, il faudrait poser de nombreux verrous afin d’interdire aux utilisateurs concurrents toute modification, le temps de vérifier la règle de validation de la contrainte CHECK, sur les lignes de la table externe, ce qui pose des problèmes de ressources et des blocages pour les autres utilisateurs.

Pire, il faudrait que les tables externes à la règle de validation soient dotées d’un mécanisme qui, lors de chaque mise à jour (INSERT, UPDATE, DELETE…) vérifient « à l’envers » que la contrainte dans la table distante est toujours validée…

Bien que la norme SQL n’interdise pas l’accès à des tables externes pour vérifier la validation de la règle d’une contrainte CHECK, rares sont les SGBDR professionnels qui le permettent.

Rares aussi sont les SGBDR qui permettent une contrainte CHECK portant sur plusieurs lignes de la table dans laquelle figure cette contrainte de validation, toujours pour des raisons de parallélisme, de concurrence et donc de performances.

Nous dirons donc que, même si votre SGBDR permet de créer des contraintes portant sur des tables externes ou vérifiant un lot de ligne de la table interne, il est plus que préférable de s’en abstenir.

Les contraintes généralisées de type « assertions » sont des contraintes portant sur différentes tables de la base et doivent être vérifiées à chaque mise à jour des tables mentionnées dans la règle de validation.

Dans l’exemple 3.29 Ceci suppose de vérifier l’assertion pour :

• La table S_PRS.T_PERSONNE_PHYSIQUE_PSP lors des commandes INSERT, UPDATE et DELETE
• La table S_PRS.T_PERSONNE_MORALE_PMR lors des commandes INSERT, UPDATE et DELETE

Cela fait donc 6 événements à surveiller repartis en deux objets.

Or on s’aperçoit que dans le lot d’exemples 3.28 et 3.29 la vérification lors des suppressions n’a aucun intérêt à être entreprise.

De même, la vérification lors des modifications n’a aucun intérêt à s’exécuter si la valeur de la clé PRS_ID est inchangée.

Enfin, dans tous les cas, cette vérification devrait n’être entreprise que sur les valeurs modifiées :

• nouvelle lignes ;
• valeurs modifiées des colonnes ;
• lignes supprimées ;

…et non sur l’ensemble de toutes les lignes des tables invoquées.

De fait, les assertions posent donc des problèmes de performances en demandant un travail trop important, inutile, mobilisant trop de ressources et bloquant la concurrence. C’est pourquoi l'éditeur Sybase avec son SGBDR SQL Server (l'ancètre de Microsoft SQL Server) a riposté en proposant la notion de déclencheur (trigger) rapidement adoptée par les autres SGBDR, dans laquelle on peut choisir les actions à circonscrire et limiter la règle de vérification aux lignes portant les valeurs nouvelles ou anciennes. Ce concept de déclencheur inventé par l’éditeur Sybase dans son SGBDR SQL Server en 1987 a finalement été accepté dans la norme SQL.

Heureusement, rares sont les SGBDR ayant mis en œuvre les assertions.

De manière systématique, abstenez vous de créer des contraintes portant sur des jeux de données, même à travers des fonctions « utilisateur » mais recourez aux déclencheurs pour ce faire. Ils ont performants, à condition de fonctionner en mode ensembliste (per statement). Voir le chapitre 7 à ce sujet.

Là encore, si la chose apparait séduisante, de nombreux auteurs, parmi lesquels Franck Edgar Codd lui-même et Chris Date, déconseille la notion de déferrabilité des contraintes, car elle permet de violer à l’évidence quelques uns des principes mêmes du fondement des SGBD Relationnels comme la notion d’exécution atomique ou le fonctionnement ensembliste…

La solution de contournement passe par l’utilisation systématique des vues, associées à des contraintes simples, des procédures stockées et des déclencheurs. Nous étudierons ces deux derniers éléments au chapitre 7.

Nous avons vu au chapitre 1, § 1.3.1 que les contraintes pouvaient jouer un rôle important pour l’optimiseur, l’aidant à simplifier une requête. Cela porte sur tous les types de contrainte… PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK

Quelques exemples théoriques simples permettent de comprendre la chose…

Si une requête recherche un produit dont le prix est inférieur à une valeur négative, la présence d’une contrainte limitant les prix à des valeurs positive ou 0, entrainera un résultat immédiat et vide, car l’optimiseur constatant cette impossibilité n’ira même pas scruter les valeurs de la table.

Dans le cas d’une interrogation multi-tabulaire, les tables peuvent être références par des valeurs de clé à d’autres tables comme nous l’avons vu au § 3.3.2.2 consacré à la contrainte FOREIGN KEY entre autres. On parle alors de table mère possédant une clé primaire et d’une table fille possédant une clé étrangère référençant la clé primaire de la table mère. En l’absence de mécanisme garantissant l’intégrité d’une telle correspondance, les requêtes sont moins bien optimisées que si l’intégrité référentielle est activée.

Voici un exemple modélisant un club de sport dans lequel nous trouvons une table des adhérents (ils sont nombreux) une table des sports pratiqués (plus d’une centaine) et une table associant les adhérents aux sports qu’ils pratiquent. Pour connaître les sports réellement pratiqués, c’est-à-dire, ceux sur lesquels il y a encore des adhérents, la requête proposée est la suivante :

Le plan d’exécution montre que l’on accède aux trois tables et le coût de la requête (juste un indice de comparaison établi par l’optimiseur) est évalué à 0,264.

En ajoutant les contraintes d’intégrité référentielles et en rejouant la requête :

Le coût de la requête est maintenant évalué à 0,0376… soit un gain de 7 fois !

La raison en est simple : la jointure entre la table des pratiques et la table des adhérents n’est plus nécessaire car l’intégrité référentielle garantie que toute clé d’adhérent (ADR_ID) dans la table des pratiques existe bien dans la table des adhérents. Bien qu’exprimée dans la requête, cette jointure est totalement éliminée pour des raisons de performance !

La volumétrie des données d’une base induit des temps de réponse qu’il est facile de combattre avec de bonne pratique comme la nécessité absolue de mettre en place le plus possible de contraintes simples. L’optimiseur en tiendra compte pour simplifier certains plans de requête.

Certaines contraintes sont créées avec un index qui permet d’en accélérer l’exécution.

À bien y regarder, aucun index n’est logiquement utile à une base de données (c’est pourquoi la norme SQL n’y fait aucune référence), mais physiquement nécessaire à l’obtention de bonnes performances.

Par exemple le fait de contrôler l’unicité d’une série de valeur n’a pas besoin d’un index, censé accélérer la recherche de l’information. Pour garantir lors de l’insertion que l’on ajoute une valeur unique à la table, il suffit alors de verrouiller l’intégralité des lignes de la table de manière exclusive et les passer toutes en revue pour savoir si cette valeur existe déjà. Si oui, on rejette l’insertion, si non, on l’accepte. Mais il est évident que bloquer la totalité des accès concurrents à une table pour assurer une telle fonctionnalité n’est pas jouable, sauf si l’on est l’utilisateur unique de la base (dans ce cas, mieux vaut sans doute utiliser un tableur).

C’est pourquoi lorsque des contraintes FOREIGN KEY et UNIQUE sont posées, un index est créé de manière sous-jacente et cet index porte généralement le nom de la contrainte.

Certains SGBDR peu professionnel, en particulier MySQL et MariaDB ont décidé de créer des index sous les clés étrangères. Mais indexer systématiquement toutes les contraintes FOREIGN KEY est une absurdité que nous démonterons au chapitre 14.

Comme lorsqu'il s'agit d'une table, on peut mettre à jour directement les données des vues (insertion, suppression, modification), mais à condition que certaines règles soient respectées :

• ne porter que sur un ensemble de tables ou de vues modifiables : pas de sous requête sauf dans la clause WHERE, ni de requête ensembliste EXCEPT, UNION ou INTERSECT). Les vues contenant des tables jointes ou l'opérateur ensembliste UNION ALL peuvent être mise à jour dans certains cas, notamment si la vue publie la clé primaire ;
• ne pas contenir de données transformées (pas de concaténation, addition de colonne, fonction...) ni calcul d'agrégat (COUNT, SUM, AVG, MIN, MAX…) ;
• ne pas contenir l’opérateur DISTINCT ni de clause GROUP BY ou HAVING ;
• contenir toutes les colonnes obligatoires dans le cas de l’ajout de ligne (INSERT) ;

Cependant, et comme l'indique la première condition, une vue peut porter sur une autre vue et pour que la nouvelle vue construite à partir d'une autre vue puisse être modifiée, il faut qu'à chaque niveau d'imbrication des vues l'ensemble de ces règles soient respectées.

Les contraintes de la table étant respectées lors des mises à jour de vue, on ne peut insérer des données à travers une vue que si ces dernières respectent les contraintes. En particulier une vue qui ne contiendrait pas une colonne rendue obligatoire par une contrainte NOT NULL ne pourrait faire l'objet d'une insertion, alors qu'elle peut faire l'objet d'une modification ou d'une suppression de ligne.

En fait on peut dire que toute vues encapsulant un sous ensemble des lignes d'une unique table, dont aucune colonne n’est le résultat d’une transformation ou d’une d’un calcul, peuvent toujours être mises à jour.

Dans tous les cas il est possible de contourner ces limitations en ayant recours aux déclencheurs INSTEAD OF.

III-8-1-1. Mise à jour directe dans une vue mono-tabulaire▲

CREATE VIEW S_RH.V_EMP_SALARIE
AS
SELECT EMP_ID, EMP_NOM, EMP_PRENOM, EMP_DATE_NAISSANCE, 
       EMP_NNI_SEXE, EMP_NNI_DATE_NAISS, EMP_NNI_COMMUNE_NAISS,
       EMP_NNI_RANG_NAISS, EMP_DATE_ENTREE, EMP_SALAIRE, EMP_FONCTION,
       EMP_SERVICE, EMP_MEDECIN_TRAITANT
FROM   S_RH.T_EMPLOYE_EMP 
WHERE  EMP_SERVICE <> 'Direction';

INSERT INTO S_RH.V_EMP_SALARIE VALUES
(1, 'DUPONT', 'Marc', '1987-12-21', '1', '8712', '54336', '028', 
 '2010-03-11', 1250, 'Découpeur', 'Production', NULL),
(2, 'LAMBERT', 'Luc', '1976-06-30', '1', '7606', '2A285', '001', 
 '2018-05-20', 18500, 'Président', 'Direction', NULL);

CREATE VIEW S_RH.V_EMP_SALARIE_NNI
AS
SELECT EMP_ID, EMP_NOM, EMP_PRENOM, EMP_DATE_NAISSANCE, 
       CONCAT(EMP_NNI_SEXE, EMP_NNI_DATE_NAISS, EMP_NNI_COMMUNE_NAISS,
       EMP_NNI_RANG_NAISS) AS NNI, EMP_DATE_ENTREE, EMP_SALAIRE,
       EMP_FONCTION, EMP_SERVICE
FROM   S_RH.T_EMPLOYE_EMP;

INSERT INTO S_RH.V_EMP_SALARIE_NNI VALUES
(3, 'SCHMIDT', 'Albert', '1982-11-21', '1821154220030', 
 '2010-03-11', 1450, 'Manoeuvre', 'Logistique'),
(4, 'MOULIN', 'Marc', '1969-11-18', '1691125321007', 
 '2018-05-20', 3200, 'Vendeur', 'Commercial');

III-8-1-2. Mise à jour directe dans une vue multi-tabulaire▲

INSERT INTO S_PRS.V_PERSONNE_PHYSIQUE 
       (PSG_ID, PSG_DATE_CREATION)
VALUES (987,    CURRENT_DATE);

INSERT INTO S_PRS.V_PERSONNE_PHYSIQUE
       (PSP_NOM,   PSP_PRENOM, PSP_DATE_NAISSANCE)
VALUES ('BONNARD', 'Frédéric', '1963-02-18');

INSERT INTO S_PRS.V_PERSONNE_PHYSIQUE 
       (PSG_ID, PSP_NOM,   PSP_PRENOM, PSP_DATE_NAISSANCE)
VALUES (987,    'BONNARD', 'Frédéric', '1963-02-18');

ALTER VIEW S_PRS.V_PERSONNE_PHYSIQUE
AS
SELECT PG.*, PP.PSG_ID AS PSG_ID_PSP, PSP_NOM , PSP_PRENOM, PSP_DATE_NAISSANCE
FROM   S_PRS.T_PERSONNE_GENERIQUE_PSG AS PG
       INNER JOIN S_PRS.T_PERSONNE_PHYSIQUE_PSP AS PP
          ON PG.PSG_ID = PP.PSG_ID;

INSERT INTO S_PRS.V_PERSONNE_PHYSIQUE 
       (PSG_ID_PSP, PSP_NOM,   PSP_PRENOM, PSP_DATE_NAISSANCE)
VALUES (987,        'BONNARD', 'Frédéric', '1963-02-18');

III-8-1-3. Mise à jour d’une vue calculée▲

CREATE TRIGGER E_I_EMP_SALAIRE_NNI
ON S_RH.V_EMP_SALARIE_NNI
INSTEAD OF INSERT
FOR EACH STATEMENT
BEGIN ATOMIC
   INSERT INTO S_RH.T_EMPLOYE_EMP
   SELECT EMP_ID, EMP_NOM, EMP_PRENOM, EMP_DATE_NAISSANCE, 
          SUBSTRING(NNI, 1, 1), 
          SUBSTRING(NNI, 2, 4),
          SUBSTRING(NNI, 6, 5),
          SUBSTRING(NNI, 10, 3),,
          EMP_DATE_ENTREE, EMP_SALAIRE, EMP_FONCTION, 
          EMP_SERVICE, NULL
   FROM   new;
END;

INSERT INTO S_RH.V_EMP_SALARIE_NNI VALUES
(3, 'SCHMIDT', 'Albert', '1982-11-21', '1821154220030', 
 '2010-03-11', 1450, 'Manoeuvre', 'Logistique'),
(4, 'MOULIN', 'Marc', '1969-11-18', '1691125321007', 
 '2018-05-20', 3200, 'Vendeur', 'Commercial');

CREATE TRIGGER E_I_V_PERSONNE_PHYSIQUE
ON S_PRS.V_PERSONNE_PHYSIQUE
INSTEAD OF INSERT
FOR EACH STATEMENT
BEGIN ATOMIC
   INSERT INTO S_PRS.T_PERSONNE_GENERIQUE_PSG
   SELECT PSG_ID, COALESCE(PSG_DATE_CREATION, CURRENT_DATE)   
   FROM   new;
   INSERT INTO S_PRS.T_PERSONNE_PHYSIQUE_PSP
   SELECT PSG_ID, PSP_NOM, PSP_PRENOM, PSP_DATE_NAISSANCE
   FROM   new;
END;

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.

Dans la vue de l’exemple 3.37, nous avions une clause WHERE indiquant que la vue était restreinte à tous les employés sauf ceux de la direction (an fait les PDG ne sont pas des salariés…) avec la clause WHERE suivante :

WHERE EMP_SERVICE <> 'Direction'

Lorsque nous avions tenté d’insérer Luc LAMBERT en tant que président à la direction, cette commande avait bien fonctionné mais cette personne n’était plus visible dans la vue… Dans un tel cas, il n’est alors plus possible de modifier (par un UPDATE) les informations de Luc LAMBERT, ni même de le supprimer (par un DELETE) à travers cette vue. Aucun message d’erreur, mais simplement le fait qu’aucune ligne n’est modifiée ou supprimée En revanche il est possible de promouvoir tout autre employé ne faisant pas partie de la direction en tant que membre de la direction.

C’est ce que montre l’exemple 3.42 :

Exemple 3.42* – Mise à jour d’une vue pour des données qualifiées ou non sans CHECK OPTION

UPDATE S_RH.V_EMP_SALARIE
SET    EMP_DATE_NAISSANCE = '1978-06-30'
WHERE  EMP_ID = 2; --> Luc Lambert;

DELETE FROM S_RH.V_EMP_SALARIE
WHERE  EMP_ID = 2;  --> Luc Lambert;

UPDATE S_RH.V_EMP_SALARIE
SET    EMP_SERVICE = 'Direction',
       EMP_FONCTION = 'Directeur'
WHERE  EMP_ID = 1; --> Marc Dupont;

En modifiant la vue pour y ajouter la qualification des mises à jour qui doivent respecter le filtre WHERE, comme le montre l’exemple 3.42 :

Exemple 3.43* – Modification d’une vue pour y ajouter la clause CHECK OPTION

CREATE VIEW S_RH.V_EMP_SALARIE
AS
SELECT EMP_ID, EMP_NOM, EMP_PRENOM, EMP_DATE_NAISSANCE, 
       EMP_NNI_SEXE, EMP_NNI_DATE_NAISS, EMP_NNI_COMMUNE_NAISS, EMP_NNI_RANG_NAISS, 
       EMP_DATE_ENTREE, EMP_SALAIRE, EMP_FONCTION, EMP_SERVICE
FROM   S_RH.T_EMPLOYE_EMP 
WHERE  EMP_SERVICE <> 'Direction'
WITH CHECK OPTION;

Les mises à jour de l’exemple 3.42 vont toutes échouées avec l'apparition d'un message d'erreur comme celui-ci : « L'ordre UPDATE/DELETE a échoué parce qu'une ou plusieurs lignes résultant de l'opération n'étaient pas qualifiées sous la contrainte CHECK OPTION »

Les options CASCADED et LOCAL (CASCADE par défaut) font que la clause CHECK OPTION porte uniquement sur la vue (LOCAL) ou bien sur toutes les vues dérivées qui en dépendent (CASCADED), c’est-à-dire sur les vues construites sur d’autres vues. À défaut de le spécifier l'option CASCADED est prise en compte.

Les vues SQL sont essentielles et on devrait toujours passer par ces objets qui constituent le modèle externe de Données (ou MED), grand oublié de la plupart des cours et tutoriaux de la modélisation des bases de données. En voici les raisons…

III-8-3-1. Simplification de la présentation des données▲

III-8-3-2. Facilitation de la gestion de la sécurité▲

III-8-3-3. Abstraction des tables▲

III-8-3-4. Simulation du comportement objet (CRUD)▲

La norme SQL:2003 a formalisé un mécanisme déjà bien implanté dans certains SGBDR : les colonnes calculées, que SQL appelle GENERATED COLUMNS. Il s'agit tout simplement de définir dans la table une colonne dont le type sera remplacé par une expression scalaire pouvant être bâtie à partir d'autres colonnes de la table. Une colonne calculée doit être introduite par la clause :

GENERATED { ALWAYS | BY DEFAULT } AS ( <expression_de_calcul> )

Exemple 3.44 : tables avec des colonnes calculées

CREATE TABLE S_COM.T_FACTURE_ENTETE_FCE
(FCE_ID                  INTEGER PRIMARY KEY,
 CLI_ID                  INTEGER NOT NULL 
    REFERENCES S_COM.T_CLIENT_CLI (CLI_ID),
 FCE_DATE                DATE NOT NULL DEFAULT CURRENT_DATE,
 FCE_REMISE_POURCENT     FLOAT NOT NULL DEFAULT 0 
    CHECK (VALUE BETWEEN 0 AND 100),
 FCE_TOTAL_HT            GENERATED ALWAYS AS 
    ((SELECT SUM(FCD_PRIX_VENTE_HT) 
      FROM   S_COM.T_FACTURE_ETAIL_FCD AS T
      WHERE  T.FCE_ID = FCE_ID) * (1 - FCE_REMISE_POURCENT / 100.0)),
 FCE_TOTAL_TVA           GENERATED ALWAYS AS 
    ((SELECT SUM(FCD_TVA) 
      FROM   S_COM.T_FACTURE_ETAIL_FCD AS T
      WHERE  T.FCE_ID = FCE_ID) * (1 - FCE_REMISE_POURCENT / 100.0)));

CREATE TABLE S_COM.T_FACTURE_ETAIL_FCD
(FCD_ID                  INTEGER PRIMARY KEY,
 FCE_ID                  INTEGER NOT NULL 
    REFERENCES S_COM.T_FACTURE_ENTETE_FCE (FCE_ID) ON DELETE CASCADE, 
 PRD_ID                  INTEGER NOT NULL
    REFERENCES S_COM.T_PRODUIT_PRD (PRS_ID),
 FCD_PRIX_UNITAIRE_HT    DECIMAL(16, 2) NOT NULL 
    CHECK (VALUE >= 0),
 FCD_QUANTITE            FLOAT NOT NULL DEFAULT 1
    CHECK (VALUE > 0),
 FCD_REMISE_POURCENT     FLOAT NOT NULL DEFAULT 0 
    CHECK (VALUE BETWEEN 0 AND 100),
 TVA_ID                  INTEGER 
    REFERENCES S_REF.T_TVA (TVA_ID),
 FCD_PRIX_VENTE_HT       GENERATED ALWAYS AS 
    (FCD_PRIX_UNITAIRE_HT * FCD_QUANTITE * (1 - FCD_REMISE_POURCENT / 100.0)),
 FCD_TVA                 GENERATED ALWAYS AS 
    (FCD_PRIX_VENTE_HT 
     * (1 + COALESCE((SELECT TVA_TAUX 
                      FROM   S_REF.T_TVA AS T 
                      WHERE  T.TVA_ID = TVA_ID), 0))));

Cependant, et pour les mêmes raisons qu’invoquées concernant les contraintes de validation CHECK, la plupart des SGBDR refusent les expressions de calcul portant sur d’autres choses que la ligne courante de la table. Est donc généralement interdit dans l’expression d’une colonne calculée :

• un accès à plus d’une ligne de la table hôte ;
• un accès à une table externe à la table hôte.

Nous verrons au chapitre 7 que les vues matérialisées peuvent correspondre, sous certaines conditions à l’obtention d’un résultat similaire.

En l’occurrence, seule la colonne calculée FCD_PRIX_VENTE_HT est prise en compte dans la plupart des SGBDR.

Il existe deux méthodes bien distinctes. L’usage de la propriété IDENTITY qui s’applique à une colonne d’une table, et l’objet SEQUENCE indépendant qui fournit des valeurs par le biais d’un appel intégré soit dans la définition de la colonne d’une table soit dans un déclencheur BEFORE.

III-9-2-1. Propriété IDENTITY▲

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 
   [ ( <option_autoincrément> ) ]

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER 
    GENERATED ALWAYS AS IDENTITY 
       (START WITH -2147483648
       INCREMENT BY 1
       NO CYCLE
       CACHE 100)
    CONSTRAINT PK_USR PRIMARY KEY,
 USR_LOGIN          VARCHAR(256) NOT NULL);

III-9-2-2. Objet SEQUENCE▲

CREATE SEQUENCE [ nom_schema. ] nom_sequence  
    AS { <type_SQL_entier> | nom_domaine_entier }  
    [ START WITH <valeur> ]  
    [ INCREMENT BY <valeur> ]  
    [ { MINVALUE [ <valeur> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <valeur> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <valeur> ] } | { NO CACHE } ]

NEXT VALUE FOR [ nom_schema. ] nom_sequence 
   [ OVER ( <clause_orde_by_over> ) ]

CREATE SEQUENCE S_SYSTEM.SQC_TOUTES_TABLES
   AS BIGINT  
      START WITH 1
      INCREMENT BY 255
      NO CYCLE 
      CACHE 10000;

-- utilisation dans la définition d'une table
CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_ID             INTEGER NOT NULL 
    DEFAULT NEXT VALUE FOR S_SYSTEM.SQC_TOUTES_TABLES
    CONSTRAINT PK_USR PRIMARY KEY,
 USR_LOGIN          VARCHAR(256) NOT NULL);

 -- utilisation dans une requêtes d'extraction SELECT
SELECT NEXT VALUE FOR S_SYSTEM.SQC_TOUTES_TABLES 
          OVER(ORDER BY  EMP_DATE_NAISSANCE ), 
       *
FROM S_RH.T_EMPLOYE_EMP;

La syntaxe est simple :

DROP <type_objet> <nom_objet> [ { RESTRICT | CASCADE } ]

Avec :

<type_objet> ::= 
   { DATABASE | SCHEMA | DOMAINE | SEQUENCE | TABLE | ASSERTION | VIEW }
<nom_objet> ::= [ nom_schema.]nom_objet

RESTRICT est par défaut, sauf dans le cas de DATABASE (la base de données) dont la suppression entraine la perte de tout le contenu de la base.

CASCADE n’est pas disponible pour le domaine mais la suppression est réussie dans tous les cas car le domaine se propage par copie de sa définition.

La suppression d’un schéma en mode CASCADE peut échouer s’il existe des intégrités référentielles entre tables de différents schémas.

La suppression d’une table en mode CASCADE supprimera toutes les tables filles liées par l’intégrité référentielle mais échouera si la table elle-même est une fille d’une intégrité référentielle à un niveau supérieur.

Exemple 3.47 – Suppression de divers objets

DROP SCHEMA S_COM WITH CASCADE;
DROP TABLE S_ADM.T_UTILISATEUR_USR;
DROP SEQUENCE S_SYSTEM.SQC_TOUTES_TABLES;
DROP VIEW S_TAXE.V_TVA WITH CASCADE;

La norme SQL ne propose pas de commande pour modifier une base de données. Notons cependant que tous les éditeurs permettent de modifier des éléments d’une base de données, comme par exemple la collation par défaut, le stockage, différents paramétrages, etc.

La syntaxe générale pour ce faire est :

ALTER DATABASE nom_base <commande_modifiant_la_base>;

Pour supprimer une base de données, la commande est :

DROP DATABASE nom_base;

Bien que la norme SQL ne propose aucune manière de modifier un SCHEMA la plupart des éditeurs de SGBDR proposent une commande ALTER SCHEMA, par exemple pour en changer le propriétaire du schéma (nous verrons cette notion au chapitre 8 consacré à la sécurité), ou encore, renommer le schéma ou transférer des objets d’un schéma dans l’autre, etc.

La syntaxe générale pour ce faire est :

ALTER SCHEMA nom_schema <option_modification_schema>;

Pour supprimer un schéma SQL, la commande est :

DROP SCHEMA nom_schema [ { RESTRICT | CASCADE } ];

La syntaxe de l’ordre SQL ALTER DOMAIN est la suivante :

ALTER DOMAIN nom_domaine
{ SET DEFAULT <valeur_par_défaut> |
  DROP DEFAULT |
  ADD <contrainte_de_domaine> |
  DROP CONSTRAINT <nom_contrainte> }

Exemple 3.48 – Modification d’un domaine SQL

CREATE DOMAIN S_TYPE.D_JOUR_SEMAINE
AS CHAR(8)
DEFAULT 'Dimanche'
CONSTRAINT CK_D_JOUR CHECK (VALUE IN ('Lundi', 'Mardi', 'Mercredi',
                            'Jeudi', 'Vendredi', 'Samedi', 'Dimanche'));

On peut changer la contrainte listant les valeurs comme ceci :

ALTER DOMAIN S_TYPE.D_JOUR_SEMAINE
   DROP DEFAULT
   DROP CONSTRAINT CK_D_JOUR;

ALTER DOMAIN S_TYPE.D_JOUR_SEMAINE
   SET DEFAULT 'DIM';
   ADD CONSTRAINT CK_D_JOUR CHECK (VALUE IN ('LUN', 'MAR', 'MER',
                                             'JEU', 'VEN', 'SAM', 'DIM'));

S’il existe des informations conforme à l’ancienne définition du domaine, une violation de contrainte de domaine se produira.

On peut modifier les caractéristiques d’une séquence par le biais de la commande :

ALTER SEQUENCE [ nom_schema.] nom_sequence
AS { <type_SQL_entier> | nom_domaine_entier }  
    [ START WITH <valeur> ]  
    [ INCREMENT BY <valeur> ]  
    [ { MINVALUE [ <valeur> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <valeur> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <valeur> ] } | { NO CACHE } ];

Pour un IDENTITY, les paramètres modifiables sont les mêmes, mais doivent être modifiés via la commande ALTER TABLE en précisant le nom de la colonne.

Syntaxe de l’ajout d’un IDENTITY à une colonne existante :

ALTER TABLE [ nom_schéma.] nom_table
   ALTER [ COLUMN ] nom_colonne 
      ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 
         [ ( <options_identity> ) ];

Syntaxe de la modification des propriétés d’un IDENTITY lié à une colonne existante :

ALTER TABLE[ nom_schéma.] nom_table
   ALTER [ COLUMN ] nom_colonne 
      { SET GENERATED { ALWAYS| BY DEFAULT } 
       | SET <options_identity> 
       | RESTART [ [ WITH ] graine ] }

Les options identity sont les mêmes que celle de la séquence. Graine est la nouvelle valeur initiale de l’identity.

Syntaxe de la suppression d’un IDENTITY à une table :

ALTER TABLE[ nom_schéma.] nom_table DROP IDENTITY

Exemple 3.49 – Divers modification d’IDENTITY

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ALTER COLUMN USR_ID 
      RESTART WITH 1;
ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ALTER COLUMN USR_ID 
      SET GENERATED BY DEFAULT;
ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ALTER COLUMN USR_ID 
      SET MAXVALUE 32765, CYCLE;

La commande SQL DROP TABLE, permet de supprimer une table. Comme dans le cadre du DROP SCHEMA, elle prend elle aussi un éventuel paramètre RESTRICT ou CASCADE permettant de définir si une exception est levée et l'ordre annulé dans le cas ou la table est référencée par une vue, une assertion, un déclencheur ou une routine (RESTRICT), ou encore s'il faut aussi détruire en chaîne les objets ainsi référencés (CASCADE).

Plus intéressant est l'ordre SQL ALTER TABLE de modification d'une table, dont la syntaxe est la suivante :

ALTER TABLE nom_table
   ADD [ COLUMN ] nom_colonne <type_ou_domaine> [ nom_collation ]
          [ [ NOT ] NULL] [ <liste_constrainte>
   | DROP [ COLUMN ] nom_colonne { RESTRICT | CASCADE } 
   | ALTER [ COLUMN ] nom_colonne SET DEFAULT <valeur_par_defaut> 
   | ALTER [ COLUMN ] nom_colonne DROP DEFAULT 
   | ALTER [ COLUMN ] nom_colonne <identity_option> 
   | ADD CONSTRAINT <contrainte_de_table> 
   | DROP CONSTRAINT nom_contrainte { RESTRICT | CASCADE }

On note donc qu'il est possible de faire :

• un ajout ou une suppression de colonne ou de contrainte de table ;
• ajouter ou supprimer une valeur par défaut à une colonne.

On en déduit qu'il n'est pas possible de faire :

• une modification de type de données ou de nom de la colonne;
• supprimer ou ajouter une contrainte de colonne et en particulier jamais pour la contrainte de type NULL / NOT NULL;
• une modification de collation d'une colonne existante.

Dans le cas de la suppression de la colonne avec l'option CASCADE, alors tous les objets (vues, assertions, déclencheurs, routines...), à commencer par les contraintes internes à la table et référençant cette colonne, sont supprimés.

Exemple 3.50 – Modification de la structure d’une table

En partant de la table ainsi définie :

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_NOM            CHAR(32)     NOT NULL,
 USR_PRENOM         VARCHAR(16)  NOT NULL,
 USR_MAIL           VARCHAR(256) NOT NULL,
 USR_ORG            VARCHAR(128),
 CONSTRAINT PK_USR_NOMPRE PRIMARY KEY (USR_NOM, USR_PRENOM),
 CONSTRAINT UK_USR_MAIL   UNIQUE (USR_MAIL),
 CONSTRAINT UK_USR_NOMORG UNIQUE (USR_NOM, USR_ORG));

La séquence des ordres suivants :

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   DROP COLUMN USR_ORG 
      CASCADE;

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD COLUMN USR_ID INTEGER NOT NULL;

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   DROP CONSTRAINT PK_USR_NOMPRE;

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD CONSTRAINT PK_USR PRIMARY KEY (USR_ID);

…a pour effet d'obtenir la structure de table suivante :

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_NOM            CHAR(32)     NOT NULL,
 USR_PRENOM         VARCHAR(16)  NOT NULL,
 USR_MAIL           VARCHAR(256) NOT NULL,
 USR_ID             INTEGER      NOT NULL,
 CONSTRAINT UK_USR_MAIL UNIQUE (USR_MAIL),
 CONSTRAINT PK_USR      PRIMARY KEY (USR_ID));

Malgré que SQL n'ait pas prévu la possibilité de modifier directement le type ou le nom d'une colonne, on peut utiliser un script SQL transactionnel en plusieurs étapes pour y parvenir.

En partant de la structure de la table S_ADM.T_UTILISATEUR_USR obtenue dans l’exemple 3.49, voici un exemple de script SQL qui change le type et le nom de la colonne USR_MAIL en USR_USER_EMAIL NVARCHAR(128), ajoute une colonne référençant le DNS depuis la table S_ADM.T_DNS, coupe l’email en deux parties : la partie nom d’utilisateur allant dans la colonne USR_USER_EMAIL et le DNS faisant référence à la table des DNS dont la description est la suivante :

CREATE TABLE S_ADM.T_DNS
(DNS_ID             INTEGER PRIMARY KEY,
 DNS_NAME           VARCHAR(128) NOT NULL UNIQUE);

Le tout en préservant les données…

Exemple 3.51 – Modification de table incluant un changement de nom de colonne

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD USR_USER_EMAIL NVARCHAR(128) NOT NULL,
       DNS_ID INTEGER NOT NULL REFERENCES S_ADM.T_DNS(DNS_ID);

UPDATE S_ADM.T_UTILISATEUR_USR
   SET USR_USER_EMAIL = 
          SUBSTRING(USR_MAIL FROM 1 
                    FOR CHARACTER_LENGTH(USR_MAIL)
                          -POSITION('@' IN USR_MAIL)-1),
       DNS_ID = (SELECT DNS_ID 
                 FROM S_ADM.T_DNS 
                 WHERE DNS_NAME = 
                    SUBSTRING(USR_MAIL FROM 
                              POSITION('@' IN USR_MAIL) + 1 FOR 
                                       CHARACTER_LENGTH(USR_MAIL) 
                                       - POSITION('@' IN USR_MAIL)));

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD CONSTRAINT UK_USR_MAIL UNIQUE (USR_USER_EMAIL, DNS_ID);

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   DROP COLUMN USR_MAIL CASCADE;

Qui donne au final la table suivante :

CREATE TABLE S_ADM.T_UTILISATEUR_USR
(USR_NOM            CHAR(32)     NOT NULL,
 USR_PRENOM         VARCHAR(16)  NOT NULL,
 USR_ID             INTEGER      NOT NULL,
 USR_USER_EMAIL     NVARCHAR(128) NOT NULL,
 DNS_ID             INTEGER NOT NULL REFERENCES S_ADM.T_DNS(DNS_ID)
 CONSTRAINT PK_USR      PRIMARY KEY (USR_ID)
 CONSTRAINT UK_USR_MAIL UNIQUE (USR_USER_EMAIL, DNS_ID));

On peut être surpris de voir que la première commande ALTER TABLE fonctionne alors que l’on a spécifié NOT NULL pour la colonne DNS_ID sans préciser de valeur par défaut. Mais lors d’une modification de table avec ajout de colonne, les lignes déjà présentes ne sont pas vérifiées pour les valeurs de la nouvelle colonne. On en conclu donc que l’obligation de valeurs ne porte que sur les opérations futures de mise à jour et non sur le passé. Mais le langage SQL à prévu que l’on puisse forcer les valeurs par défaut dans les lignes actuelles à condition que l’option WITH VALUES soit mentionnée juste après la valeur par défaut. À titre d’exemple on aurait pu lancer la commande suivante :

ALTER TABLE S_ADM.T_UTILISATEUR_USR
   ADD USR_USER_EMAIL NVARCHAR(128) NOT NULL,
       DNS_ID INTEGER NOT NULL DEFAULT 1 WITH VALUES 
       REFERENCES S_ADM.T_DNS(DNS_ID);

On conçoit facilement qu'il est préférable d'utiliser un outil de modélisation de données capable d'automatiser de tels script SQL de modification de table.

Il n'est pas possible de modifier une assertion, mais il est possible de la supprimer à l'aide de l'ordre SQL DROP ASSERTION puis de la recréer, le tout dans une transaction explicite.

La norme SQL n’a pas prévu la possibilité de changer le nom d’un objet et propose de le supprimer puis le recréer. Pour une table, il est plus facile de la recopier par création à la volée et détruire la table source (voire paragraphe suivant).

Certains SGBDR proposent une option de de changement de nom intégrée à la commande ALTER.

C’est le cas de PostGreSQL qui permet la commande :

ALTER <type_objet> [ nom_schema. ] nom_objet RENAME TO nouveau_nom;

…pour un objet de premier niveau (table, vue, …) et :

ALTER TABLE [ nom_schema. ] nom_table
   { RENAME [ COLUMN ] nom_colonne TO nouveau_nom_colonne
     |  RENAME CONSTRAINT nom_constrainte TO nouveau_nom_constrainte

…pour un objet de second niveau (colonnes, contraintes, …).

Ces commandes spécifiques à PostGreSQL ne permettent pas de changer le schéma de l’objet. Pour déplacer un objet d’un schéma à l’autre il faut utiliser la commande PostGreSQL :

ALTER <type_objet> [ nom_schema. ] nom_objet SET SCHEMA autre_nom_schéma;

Dans les deux syntaxes ci-avant :

<type_objet> ::= { SCHEMA | TABLE | VIEW | DOMAIN }

Exemple 3.52 – Renommage d’une table et déplacement dans un autre schéma (PostGreSQL).

ALTER TABLE S_ADM.T_DNS RENAME TO T_NOM_SERVEUR_WEB_DNS;
ALTER TABLE S_ADM.T_NOM_SERVEUR_WEB_DNS SET SCHEMA S_INTERNET;

Dans Microsoft SQL Server ceci se fait, pour le changement de nom, à l’aide de la procédure stockée sp_rename et pour le changement de schéma avec ALTER SCHEMA :

EXEC sp_rename '[ nom_schema. ] nom_objet' , 'nouveau_nom_objet', '<type_classe>';

Dans lequel :

<type_classe ::= { COLUMN | DATABASE | <object_name> }

La sous-classe object_name devant être le nom d’une contrainte (CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE…) par exemple.

Enfin, la commande :

ALTER SCHEMA nom_schéma TRANSFERT [ nom_schéma. ] nom_objet;

…permet de transférer tout objet dans son nouveau schéma.

Exemple 3.53 – Renommage d’une table et déplacement dans un autre schéma (SQL Server).

EXEC sp_rename 'S_ADM.T_DNS', 'T_NOM_SERVEUR_WEB_DNS', 'OBJECT';
ALTER SCHEMA S_INTERNET TRANSFER S_ADM.T_NOM_SERVEUR_WEB_DNS;

Certains SGBDR permettent de modifier un objet, ou si l’objet n’existe pas de le créer. C’est la commande CREATE OR ALTER, disponible uniquement pour certains objets comme des domaines, des vues… mais pas des tables.

La syntaxe générale est :

CREATE OR ALTER [ nom_schéma.]nom_objet <definition_objet>

Certains SGBDR permettent de supprimer un objet uniquement au cas ou ce dernier existe déjà.

La syntaxe générale est :

IF EXISTS DROP <classe_objet> [nom_schéma.]nom_objet [;]

Comme dans le cas de la création ou modification, cela évite une erreur si l'objet n'existe pas dans le cadre de l'ALTER ou du DROP.

Pour créer une table en partant d'une autre table, il suffit de préciser, n'importe ou à la place d'une définition de colonne, le mot clé LIKE et de l'accompagner du nom de la table. On peut ajouter des colonnes supplémentaires ou bien exclure certaines colonnes comme le montre la syntaxe suivante :

CREATE TABLE [ nom_schéma.]nom_table
   (LIKE [ nom_schéma.]nom_autre_table <options_like>

   <options_like> ::= option1 [, option2 [, option3 ] ]

      <optionN> ::= { INCLUDING | EXCLUDING } IDENTITY
                   | { INCLUDING | EXCLUDING } DEFAULTS
                   | { INCLUDING | EXCLUDING } GENERATED

Exemple 3.52 – Création d’une table vide empruntant la structure d’une table existante :

CREATE TABLE S_ADM.T_LISTE_UTILISATEUR_LUT
(LIKE S_ADM.T_UTILISATEUR_USR);

Il est possible de créer une table à partir d'une expression de requête d'extraction de l'information (les requêtes SELECT d'extraction de l'information SELECT seront vues aux chapitres 4 et 5). Une syntaxe simplifiée est la suivante :

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY } ] 
   TABLE  [nom_schéma.]nom_table
   [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
   AS ( <requête_SELECT> )
  [ WITH [ NO ] DATA ]

La clause ON COMMIT n’est valable que si la table est temporaire et permet de conserver ou supprimer les lignes ayant été mises à jour (INSERT, UPDATE, DELETE) pendant la transaction explicite. Nous expliquerons ce point plus en détail au chapitre 7.

DATA : signifie que les données émanent de la requête seront insérées à la suite de la création de la table. Dans le cas contraire (NO DATA) la table sera créée vide. Par défaut la table est copiée avec ses données

Exemple 3.53 – Création d’une table à partir d’une requête :

CREATE TEMPORARY LOCAL TABLE T_USR 
AS ( SELECT USR_PRENOM, USR_NOM, 
            USR_USER_EMAIL || '@' || DNS_NAME AS DEMAIL
     FROM   S_ADM.T_UTILISATEUR_USR AS U
            JOIN S_ADM.T_DNS AS D ON U.DNS_ID = D.DNS_ID)
WITH DATA;

Créé une table temporaire locale avec les noms et prénoms des utilisateurs et leur email reconstitué.