Partie 2

Création et manipulation des schémas - CREATE, ALTER, DROP

6. Les assertions

CREATE ASSERTION nom_assertion
CHECK (predicat)
[attribut_assertion]

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

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

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

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

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

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

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

7. Les tables

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

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

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

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

• une table peut être créée de manière durable (par défaut) ou temporaire et dans ce dernier cas uniquement pour l'utilisateur et la connexion qui l'a créé ou bien pour l'ensemble des utilisateurs de la base
• une table comporte des colonnes et des contraintes de table
• une colonne peut être spécifiée d'après un type SQL ou un domaine créé par l'utilisateur
• une colonne définie peut être dotée de contraintes de colonnes telles que : obligatoire, clef, unicité, intégrité référentielle et validation
• une contrainte de table porte sur une ou plusieurs colonnes et permet l'unicité, la validation et l'intégrité référentielle

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

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

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

CREATE DOMAIN D_ALFA_FIX_32 CHAR(32)

CREATE DOMAIN D_ALFA_VAR_32 VARCHAR(32)

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

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

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

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

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

7.1.1. Obligatoire ([NOT] NULL)

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

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

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

7.1.2. Valeur par défaut (DEFAULT)

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)

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

7.1.3. Séquence de collation (COLLATE)

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

7.1.4. Clef (PRIMARY KEY)

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

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.

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

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

7.1.5. Unicité (UNIQUE)

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

7.1.6. Validation (CHECK)

CHECK ( prédicat )

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

7.1.7. Intégrité référentielle (FOREIGN KEY / REFERENCES)

FOREIGN KEY REFERENCES table (colonne)

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

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.

7.2. Les contraintes de table

• PRIMARY KEY : précise que la ou les colonnes composent la clef de la table. ATTENTION : nécessite que chaque colonne concourrant à la clef soit NOT NULL.
• UNIQUE : les valeurs de la ou les colonnes doivent être unique ou NULL, c'est à dire qu'à l'exception du marqueur NULL, il ne doit jamais y avoir plus d'une fois la même valeur (pas de doublon) au sein de l'ensemble de données formé par les valeurs des différentes colonnes composant la contrainte.
• CHECK : permet de préciser un prédicat validant différentes colonnes de la table et qui accepterons les valeurs s'il est évalué à vrai.
• FOREIGN KEY : permet, pour les valeurs de la ou les colonnes, de faire référence à des valeurs préexitantes dans une ou plusieurs colonnes d'une autre table. Ce mécanisme s'apelle intégrité référentielle.

7.2.1. Clef multicolonne (PRIMARY KEY)

CONSTRAINT nom_contrainte PRIMARY KEY (liste_colonne)

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

7.2.2. Unicité globale (UNIQUE)

CONSTRAINT nom_contrainte UNIQUE (liste_colonne)

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

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

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

SELECT *
FROM T_PERSONNE10

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

7.2.3. Validation de ligne (CHECK)

CONSTRAINT nom_contrainte CHECK ( prédicat )

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

7.2.4. Integrité référentielle de table (FOREIGN KEY / REFERENCES)

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

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.

7.3. La gestion de l'intégrité référentielle

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

7.3.1. Mode de gestion de la la référence, clause MATCH

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

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

• la contrainte s'aplique toujours sauf si toutes les colonnes sont pourvues d'un marqueur NULL

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.

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

7.3.2. Mode de gestion de l'intégrité clauses ON UPDATE / ON DELETE

7.4. Mode de gestion de la déférabilité

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

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)

• lors de la création du schéma
• lors de l'exécution de la contrainte

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

• 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

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

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

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

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

7.5. Contraintes horizontales ou verticales ?

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

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

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

• supprimer une colonne
• supprimer une contrainte
• ajouter une colonne
• ajouter une contrainte
• ajouter une contrainte de ligne DEFAULT

• changer le nom d'une colonne
• changer le type d'une colonne
• ajouter une contrainte de ligne NULL / NOT NULL

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

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

DROP {TABLE | DOMAIN | ASSERTION | VIEW } nom_objet

7.6.1. Changer le nom ou le type d'une colonne

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

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

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

8. Les vues

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

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

• 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

	
-- 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'

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

9. Les informations de schéma

	
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'

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

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

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

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]

• 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

11. Résumé