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

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

Exemple 64

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

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

Exemple 65 - insertion et modification acceptées :

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

Exemple 66 - insertion et modification refusées :

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

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

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

Exemple 67

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

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

Exemple 68

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

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

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

Exemple 69

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

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

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

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

Exemple 70

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

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

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

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

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

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

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

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

Exemple 72

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

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

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

SELECT *
FROM T_PERSONNE7

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

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

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

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

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

Sa syntaxe est :

CHECK ( prédicat )

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

Exemple 73

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

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

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

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

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

FOREIGN KEY REFERENCES table (colonne)

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

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

Exemple 74

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

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

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

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

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

Exemple 75

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

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

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

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

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

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

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

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

CONSTRAINT nom_contrainte PRIMARY KEY (liste_colonne)

Exemple 76 - clé primaire sur PRS_NOM / PRS_PRENOM

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

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

CONSTRAINT nom_contrainte UNIQUE (liste_colonne)

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

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

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

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

SELECT *
FROM T_PERSONNE10

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

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

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

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

Sa syntaxe est :

CONSTRAINT nom_contrainte CHECK ( prédicat )

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

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

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

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

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

Exemple 79

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

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

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

Exemple 80

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

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

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

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

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

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

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

Exemple 81

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

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

MATCH SIMPLE implique que :

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

Exemple 82

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

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

Insertion réussie

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

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

MATCH FULL implique que :

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

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

Exemple 83

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

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

Insertion réussie

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

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

MATCH PARTIAL implique que :

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

Exemple 84

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

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

Insertion réussie

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• créer une colonne temporaire de même nom et même type (ALTER TABLE ADD… ;
• alimenter la colonne temporaire avec les valeurs de l'actuelle (UPDATE … ;
• supprimer l'actuelle colonne (ALTER TABLE DROP… ;
• créer une nouvelle colonne avec le nouveau nom et/ou le nouveau type (ALTER TABLE ADD… ;
• alimenter la nouvelle colonne avec les données de la colonne temporaire (UPDATE … ;
• supprimer la colonne temporaire (ALTER TABLE DROP …

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

-- condition de départ

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

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

-- le script de modification

ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)

UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE

ALTER TABLE
DROP IMP_DATE

ALTER TABLE
ADD IMP_DATE DATE

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

ALTER TABLE
DROP TMP_IMP_DATE

COMMIT 

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

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

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

-- condition de départ

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

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

-- le script de modification

ALTER TABLE T_IMPORT
ADD TMP_IMP_NOM VARCHAR(16)

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

ALTER TABLE
DROP IMP_NOM

ALTER TABLE
ADD IMP_NOM VARCHAR(16) NOT NULL

UPDATE T_IMPORT 
SET IMP_NOM = TMP_IMP_NOM

ALTER TABLE
DROP TMP_IMP_NOM

COMMIT 

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