IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Contraintes FOREIGN KEY SQL vs code client

Se poser la question d'implanter ou pas les contraintes d'intégrité référentielle dans une base de données est aussi stupide que de se demander s'il faut vraiment des roues à une voiture… Voyons quels sont les arguments des opposants et démolissons-les un à un !


Il y a essentiellement trois types d'opposition à la mise en place des contraintes dites FOREIGN KEY.


1) C'est lent.

Nous démontrerons le contraire. Et nous montrerons même que c'est performant !


2) C'est contraignant.

C'est juste, mais nous verrons quelques moyens de contourner cela tout en respectant à la lettre ces contraintes.


3) C'est spécifique aux SGBDR et si je veux pouvoir tourner sur n'importe quel système, mieux vaut faire cela dans le code client.

C'est l'argument de loin le plus stupide, car tous les SGBD relationnels dignes de ce nom implémentent tous les contraintes d'intégrité nécessaires aux relations.

144 commentaires Donner une note à l´article (4.5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Posons le problème

Il y a en fait deux attitudes à avoir et aucune alternative : soit vous voulez mettre ces contraintes et dans ce cas ce sera toujours dans la base de données qu'il faudra les y placer ; ou alors vous ne voulez pas de contrainte d'intégrité référentielle et par conséquent elles n'ont à figurer nulle part, ni dans la base ni dans le code client.


J'entends déjà des protestations s'élever avec l'argument suivant : « Oui, mais on peut les mettre dans le code du programme client ». Et là je réponds : si vous voulez la pire des solutions, alors n'hésitez pas, amusez-vous à piloter les références de clefs étrangères directement dans le code et revenez me voir quand vous aurez des problèmes… Mon tarif actuel en matière d'audit est situé entre 1200 ? HT par jour et 1600… et ce genre de stupidité est mon pain quotidien ! Et je suis même en train d'augmenter mes tarifs, car je suis surbooké par les demandes d'audit de bases dont les performances sont lamentables causées par l'application de dogmes imbéciles tels que celui-là !

II. Des FOREIGN KEY vérifiées par code client…

Les personnes qui sortent l'argument stupide du « Mettez les contraintes FOREIGN KEY dans le code client » oublient toujours une chose fondamentale… Un SGBD relationnel est un système massivement concurrentiel et pour cela fortement parallèle. Si la base ne pouvait être utilisée que par un seul client à la fois, alors l'argument serait fort recevable et bien que je ne vous le conseille pas, je ne noterais pas dans mon rapport d'audit de faute grave. Je me contenterais de signaler qu'il n'y a pas, en général, qu'une seule application qui peut accéder à la base, mais toute une série, depuis les outils d'administration aux ETL ou outils d'import de données, et qu'il convient donc d'implanter ce code client de vérification des contraintes aussi dans ces outils-là, ce qui suppose que vous vous les fabriquiez spécifiquement, car rares sont les éditeurs sérieux qui pensent à le faire. Bref, il vous faudra donc tout redévelopper…


Étudions maintenant les choses de manière simple et directe avec un exemple concret. Soit les tables suivantes :

 
Sélectionnez
CREATE TABLE T_CLIENT
(CLI_ID    INT  NOT NULL PRIMARY KEY,
 CLI_NOM   VARCHAR(16));
 
Sélectionnez
CREATE TABLE T_COMMANDE
(COM_ID    INT  NOT NULL PRIMARY KEY,
 CLI_ID    INT,
 COM_DATE  DATE);


Pour ne pas risquer d'insérer une commande d'un client qui n'existe pas, on serait tenté de faire dans le code client, la chose suivante :


Soit à insérer la facture n° 222 pour le client 987 à la date du jour :


1) lancer la requête suivante :

 
Sélectionnez
SELECT * FROM T_CLIENT WHERE CLI_ID = 987


2) vérifier s'il y a un résultat

 
Sélectionnez
IF NOT MyQuery.Empty()
THEN


3) lancer la requête :

 
Sélectionnez
INSERT INTO T_COMMANDE VALUES (222, 987, CURRENT_DATE)


En principe tout devrait bien se passer… Sauf que c'est sans compter sur la sournoisité d'un SGBDR. Reprenons ce code avec un peu de concurrence, c'est-à-dire avec quelques dizaines d'utilisateurs qui font eux aussi des requêtes. Introduisons juste une seule petite requête en plein milieu de votre traitement, comme ceci :

 
Sélectionnez
DELETE FROM T_CLIENT WHERE CLI_ID = 987


Et vous aurez compris que vous allez insérer une commande avec un client qui vient tout juste de disparaître !


Bien entendu, il est possible d'améliorer cela par exemple en proposant d'encapsuler ce traitement dans une transaction. Sauf qu'il va falloir utiliser un niveau d'isolation suffisant. En effet une transaction garantit l'aspect « tout ou rien » (atomicité), mais c'est à vous de savoir quel niveau de verrouillage vous souhaitez donner aux tables dans votre traitement. Or il existe quatre niveaux d'isolation :

  • le niveau 0 dit « READ UNCOMITTED » propose de lire même des données qui ne sont pas valides. Par exemple un client que l'on vient d'insérer, mais qui va être annulé… Pas vraiment adapté à notre problématique !
  • le niveau 1 dit « READ COMMITED » propose de ne lire que des données impérativement validées (COMMIT). C'est séduisant, mais inefficace. En effet rien n'empêchera quelqu'un de supprimer le client que vous venez de lire et de valider sa transaction avant que vous n'ayez le temps de l'insérer. En effet dans ce mode les verrous sont posés sur les tables pour chaque ordre SQL et libérés immédiatement après ;
  • le niveau 2 dit « REPEATABLE READ » assure que les données que vous lisez une première fois puissent être relues avec les mêmes valeurs à chaque lecture. C'est bien ce mode qu'il faut utiliser dans le cas présent. Or ce mode possède un petit inconvénient : il pose des verrous de ligne sur chacune des tables des commandes SQL de la transaction et ne les libère que passé le COMMIT ou le ROLLBACK !
  • le niveau 3 dit « SERIALIZABLE » empêche toute mise à jour (INSERT, UPDATE, DELETE) dans les tables visées, sauf par vous bien entendu. Autant prendre un bulldozer pour tailler les données de la base ! En effet, il faut pour cela verrouiller le contenu intégral de chacune des tables…


Vous venez donc de comprendre que le code à exécuter pour être sûr de votre fait est bien plus complexe que cela. Pire, il est ultra contreperformant… En effet, démarrer une transaction qui maintient des verrous durant tout le traitement, et cela depuis le client, rend la transaction longue, très longue, en regard de l'exécution des commandes SQL, car le temps réseau est systématiquement l'un des plus longs qui soient. Entre les quelques microsecondes que vont durer vos requêtes et le temps de faire transiter tous les messages entre le client et le serveur dans les trames réseau dans un système non déterministe (Ethernet), il n'y a pas photo… on peut estimer que 95 % du temps sera consacré à bavarder et non à traiter les données.

Finalement le code client devrait ressembler à cela :


1) lancer la requête suivante :

 
Sélectionnez
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ


2) lancer la requête suivante :

 
Sélectionnez
BEGIN TRANSACTION


3) lancer la requête suivante :

 
Sélectionnez
SELECT * FROM T_CLIENT WHERE CLI_ID = 987


4) vérifier s'il y a un résultat et pas d'erreur

 
Sélectionnez
IF NOT exception() AND NOT MyQuery.Empty()
THEN


5) lancer la requête :

 
Sélectionnez
INSERT INTO T_COMMANDE VALUES (222, 987, CURRENT_DATE)


6) s'il n'y a pas d'erreur, lancer la requête :

 
Sélectionnez
COMMIT TRANSACTION


7) en cas d'erreur, lancer la requête :

 
Sélectionnez
ROLLBACK TRANSACTION


8) dans tous les cas, revenir au mode d'isolation par défaut (en principe READ COMMITTED)

 
Sélectionnez
SET TRANSACTION ISOLATION LEVEL READ COMMITTED


Bref, énorme et totalement contreperformant, donc parfaitement imbécile. De plus, il y a fort à parier que la plupart des développeurs oublieront tout ce cinéma et par conséquent, on se retrouvera devant une base désintégrée !


Il y a évidemment le moyen de faire ceci sans transaction… En effet, avec un peu d'astuce, on peut y arriver, mais c'est aussi complexe et contreperformant :


1) créer une table temporaire locale pour y stocker les lignes à insérer :

 
Sélectionnez
CREATE TEMPORARY LOCAL TABLE TMP_T_COMMANDE
(COM_ID    INT,
 CLI_ID    INT,
 COM_DATE  DATE)


2) lancer la requête suivante :

 
Sélectionnez
INSERT INTO TMP_T_COMMANDE VALUES (222, 987, CURRENT_DATE)


3) lancer la requête suivante :

 
Sélectionnez
INSERT INTO T_COMMANDE 
 SELECT * 
 FROM   T_TMP_COMMANDE
 WHERE  CLI_ID IN (SELECT CLI_ID
                   FROM   T_COMMANDE)


4) supprimer la table temporaire locale en lançant requête :

 
Sélectionnez
DROP TABLE TMP_T_COMMANDE


Voilà, il n'y a plus de transaction, mais le coût d'un tel traitement devient exorbitant. Parce que si l'on compte 1 comme mesure de coût pour un SELECT, 10 pour un INSERT, 30 pour une transaction et 100 pour un CREATE ou un DROP, alors on passe d'un coût de 50 à un coût de 260 !

Bref, c'est encore pire et plus complexe…


Vous noterez au passage que c'est dans la requête d'insertion que l'on fait le test, ce qui revient strictement à la même chose si nous avions implanté les contraintes d'intégrité référentielle, mais en pire bien entendu !


Vous venez donc de comprendre que : soit il faut implanter les contraintes d'intégrité référentielles dans la base, soit ne pas les implanter du tout !

III. C'est lent

Détruisons maintenant l'argument de la supposée lenteur…


Une contrainte SQL est vérifiée avant même que les données soient mises à jour. Pour les contraintes NOT NULL et DEFAULT, pour chaque valeur. Pour les autres contraintes, pour la ligne entière de données, mais avant toute insertion, modification ou suppression. Autrement dit, il ne s'agit que vérifier, pas encore de faire… Alors que dans le code que nous venons de voir il faut commencer à faire pour défaire…

À votre avis, quelle solution est la plus rapide et quelle est la plus lente ?


Bref, il est vrai que toute contrainte a un coût, mais bien moindre que de reproduire le mécanisme côté client.


Mais les contraintes peuvent-elles augmenter les performances d'un SGBDR ?

Chez certains éditeurs, c'est le cas. En effet Oracle ou SQL Server se servent des contraintes mises en place dans la base pour trouver la meilleure façon de faire telle ou telle requête. Analysons ce phénomène avec un exemple plus détaillé…


Soit les tables suivantes :

T_ADHERENT

ADH_ID

ADH_NOM

ADH_PRENOM

1

Philippe

Noiret

 

2

Jean

Poiret

 

3

Michel

Serrault

 

4

Jacques

Villeret

 
 

 

7892

Marcel

Dupont

 


Notez que tous les adhérents de cette association sportive ne figurent pas exhaustivement, mais que nous en avons dénombré 7892 !


T_SPORT

SPT_ID

SPT_LIBELLE

1

Escrime

5

Équitation

12

Tennis

18

Natation

23

Course à pied

47

Ping-pong

52

Golf

78

Volley

85

Hand-ball

112

Football

823

Rugby

9845

Moto

Ici, la liste des sports est exhaustive…


T_PRATIQUE

ADH_ID

SPT_ ID

1

5

1

12

2

1

2

52

3

18

3

78

3

85

3

33

4

5

4

52

 

7892

112

7892

18

7892

23

8954155

52


Là, force est de constater que nous manquons de place pour exprimer les 27 011 occurrences de cette table…


Admettons enfin que tous nos échantillons ici présentés sont parfaitement représentatifs.


La requête suivante :

 
Sélectionnez
SELECT DISTINCT SPT_LIBELLE
FROM   T_PRATIQUE AS P
       INNER JOIN T_ADHERENT AS A
             ON P.ADH_ID = A.ADH_ID
       INNER JOIN T_SPORT AS S
             ON P.SPT_ID = S.SPT_ID

permet de savoir quels sont les sports réellement pratiqués dans ce club. Bien entendu, un sport pratiqué veut dire que :

  • ce sport existe dans la liste des sports ;
  • cet adhérent existe dans la liste des adhérents.


Si une contrainte d'intégrité de référence avait été posée entre le couple de tables T_PRATIQUE / T_ADHERENT en vérifiant l'existence des noms et prénoms des adhérents lors de la saisie dans la table T_PRATIQUE et que de même, on avait posé une intégrité de référence entre le couple de tables T_PRATIQUE / T_SPORT en vérifiant l'existence de chaque sport lors de la saisie dans la table T_PRATIQUE, alors on aurait évité les erreurs de saisies comme :

ADH_ID

SPT_LIBELLE

3

33

8954155

52


En effet, ni le sport « 33 » n'existe dans la table T_SPORT, ni l'adhérent 8954155 n'existe dans la table T_ADHERENT (peut être ont-ils réellement existé quelques années auparavant !).


Avec de telles contraintes, il était alors possible de simplifier grandement la requête en :

 
Sélectionnez
SELECT DISTINCT SPT_LIBELLE
FROM   T_PRATIQUE AS P
       INNER JOIN T_SPORT AS S
             ON P.SPT_ID = S.SPT_ID


Bien évidemment, cette dernière requête est incommensurablement plus performante : elle travaille sur un jeu de données moindre et n'a qu'une jointure à faire ! Et que pensez-vous qu'un bon moteur relationnel soit capable de faire ? Justement ce genre de simplification. En effet un moteur de requêtes est un outil mathématique qui travaille par transformation afin de trouver une écriture allégée de la requête et la moins consommatrice de ressources… Encore faut-il que le moteur SQL soit bon !


Pour vous en convaincre, vous pouvez vous amuser à mesurer cela sur votre SGBDR favori à l'aide du script téléchargeable ici [Loading database.zip].


Et voici les essais effectués :

 
Sélectionnez
SET STATISTICS IO ON;

-- requête originale 
SELECT DISTINCT SPT_LIBELLE
FROM   T_PRATIQUE AS P
       INNER JOIN T_ADHERENT AS A
             ON P.ADH_ID = A.ADH_ID
       INNER JOIN T_SPORT AS S
             ON P.SPT_ID = S.SPT_ID;

-- nombre d'IO pour la lecture des données de la requête sans contraintes
Table 'T_ADHERENT'. lectures logiques 36
Table 'T_PRATIQUE'. lectures logiques 222
Table 'T_SPORT'.    lectures logiques 2

-- nettoyage de la table des données parasites :
DELETE FROM T_PRATIQUE
WHERE  ADH_ID = 3
  AND  SPT_ID = 33;
DELETE FROM T_PRATIQUE
WHERE  ADH_ID = 8954155
  AND  SPT_ID = 52;

-- implantation des contraintes FK :
ALTER TABLE T_PRATIQUE 
   ADD CONSTRAINT FK_ADH 
   FOREIGN KEY (ADH_ID) 
   REFERENCES T_ADHERENT (ADH_ID);
ALTER TABLE T_PRATIQUE 
   ADD CONSTRAINT FK_SPT 
   FOREIGN KEY (SPT_ID) 
   REFERENCES T_SPORT (SPT_ID);

-- nouvelle requête optimisée :
SELECT DISTINCT SPT_LIBELLE
FROM   T_PRATIQUE AS P
       INNER JOIN T_SPORT AS S
             ON P.SPT_ID = S.SPT_ID;

-- nombre d'IO pour la lecture des données de la requête avec contraintes
Table 'T_SPORT'.    lectures logiques 20
Table 'Worktable'.  lectures logiques 0
Table 'T_PRATIQUE'. lectures logiques 96,

--> y a pas photo : 360 lectures contre 116, soit 3 fois mieux ! merci les contraintes !!!

IV. C'est contraignant

Eh oui, les contraintes sont faites… pour contraindre ! Donc, c'est contraignant. Et de ce fait, de nombreux utilisateurs se plaignent de la problématique des FOREIGN KEY.

  • Comment donc reconstruire une base de données avec un script SQL où il y a des tables liées les unes aux autres par des contraintes d'intégrité référentielle ?
  • Comment vider toutes les tables d'une base, ou bien insérer un jeu d'essai dans différentes tables sans être obligé de respecter un ordre précis, difficile à déterminer ?


Ces deux questions souvent posées dans les forums ont des réponses très simples.

Pour reconstruire une base de données avec toutes les contraintes d'intégrité référentielle deux moyens sont possibles :

  • générer un script de création des tables sans les contraintes FOREIGN KEY et un autre avec des ALTER TABLE afin de remettre en place les contraintes d'intégrité référentielle, ce que font les bons outils de modélisation comme Power AMC ou les outils de rétro-ingénierie de bases de données comme SQL Server management Studio. Entre le passage des deux scripts, on peut insérer un script de chargement des données ;
  • utiliser le principe de création de schéma SQL. J'ai indiqué comment faire dans un de mes blogs intitulé « De l'intérêt des SCHÉMA SQL » (http://blog.developpez.com/sqlpro/p5835/langage-sql-norme/de-l-interet-des-schema-sql/). L'avantage avec la création des objets au sein d'un schéma, c'est que vous n'avez plus à vous poser aucune question sur l'ordre d’enchaînement de la création des objets. C'est prévu par la norme SQL !

Pour vider toutes les tables d'une base de données ou bien insérer des données d'un jeu d'essais dans différentes tables, il suffit de désactiver ou supprimer les contraintes FOREIGN KEY puis de les remettre en place après le travail !

Certains SGBDR proposent une option (interdite par la norme SQL) pour désactiver les contraintes, genre ALTER TABLE … DISABLE CONSTRAINT. Je n'en suis pas fan ! Mieux vaut avoir sous la main un script de construction/suppression des contraintes FOREIGN KEY. C'est généralement simple à obtenir si vous avez un bon outil d'administration de votre SGBDR… Si ce n'est pas le cas, il faudra le générer par des requêtes dans les vues d'information de schéma.


Voici par exemple deux scripts SQL qui ajoutent et suppriment toutes les contraintes FOREIGN KEY d'une base de données, que l'on peut obtenir par exécution de la procédure dbo.P_DDL_FK_CONSTRAINTS pour MS SQL Server :

 
Sélectionnez
CREATE PROCEDURE dbo.P_DDL_FK_CONSTRAINTS @SQLCOMMAND VARCHAR(4)
AS

BEGIN

IF @SQLCOMMAND NOT IN ('ADD', 'DROP')
   RETURN;

DECLARE @SQL VARCHAR(MAX), @COLS_FK VARCHAR(MAX), @COLS_UK VARCHAR(MAX),
        @TABLE_SCHEMA sysname, @TABLE_NAME sysname, 
        @CONSTRAINT_SCHEMA sysname, @CONSTRAINT_NAME sysname, 
        @MATCH_OPTION NVARCHAR(7), 
        @UPDATE_RULE NVARCHAR(11), @DELETE_RULE NVARCHAR(11),
        @U_TABLE_SCHEMA sysname, @U_TABLE_NAME sysname, 
        @U_CONSTRAINT_NAME sysname, @U_CONSTRAINT_SCHEMA sysname;

DECLARE C CURSOR
FOR 
   SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, RC.CONSTRAINT_SCHEMA, 
          RC.CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE,
          TU.TABLE_SCHEMA AS U_TABLE_SCHEMA, TU.TABLE_NAME AS U_TABLE_NAME,
          TU.CONSTRAINT_SCHEMA AS U_CONSTRAINT_SCHEMA,  
          TU.CONSTRAINT_NAME AS U_CONSTRAINT_NAME
   FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
          INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  AS RC
                ON RC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
                   AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
          INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TU
                ON RC.UNIQUE_CONSTRAINT_SCHEMA = TU.CONSTRAINT_SCHEMA
                   AND RC.UNIQUE_CONSTRAINT_NAME = TU.CONSTRAINT_NAME

FOR READ ONLY;

OPEN C;

FETCH C INTO @TABLE_SCHEMA, @TABLE_NAME, 
             @CONSTRAINT_SCHEMA, @CONSTRAINT_NAME, 
             @MATCH_OPTION, @UPDATE_RULE, @DELETE_RULE, 
             @U_TABLE_SCHEMA, @U_TABLE_NAME,
             @U_CONSTRAINT_SCHEMA, @U_CONSTRAINT_NAME;

WHILE @@FETCH_STATUS = 0
BEGIN

   SET @COLS_FK = '';

   SELECT @COLS_FK = @COLS_FK + COLUMN_NAME + ', '
   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE  TABLE_SCHEMA =      @TABLE_SCHEMA
     AND  TABLE_NAME  =       @TABLE_NAME
     AND  CONSTRAINT_SCHEMA = @CONSTRAINT_SCHEMA
     AND  CONSTRAINT_NAME =   @CONSTRAINT_NAME;

   SET @COLS_FK = SUBSTRING(@COLS_FK, 1, LEN(@COLS_FK) -1);

   SET @COLS_UK = '';

   SELECT @COLS_UK = @COLS_UK + COLUMN_NAME + ', '
   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE  TABLE_SCHEMA =      @U_TABLE_SCHEMA
     AND  TABLE_NAME  =       @U_TABLE_NAME
     AND  CONSTRAINT_SCHEMA = @U_CONSTRAINT_SCHEMA
     AND  CONSTRAINT_NAME =   @U_CONSTRAINT_NAME;

   SET @COLS_UK = SUBSTRING(@COLS_UK, 1, LEN(@COLS_UK) -1);

   SET @SQL = 'ALTER TABLE [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME +'] '
              + CASE WHEN @SQLCOMMAND = 'DROP' THEN 'DROP CONSTRAINT [' 
                          + @CONSTRAINT_NAME +'];'
                     ELSE 'ADD CONSTRAINT [' + @CONSTRAINT_NAME +'] FOREIGN KEY (' + @COLS_FK 
                           +') REFERENCES ['+ @U_TABLE_SCHEMA + '].[' + @U_TABLE_NAME 
                           + '] (' + @COLS_UK + ') MATCH ' + @MATCH_OPTION 
                           + ' ON UPDATE ' + @UPDATE_RULE + ' ON DELETE ' + @DELETE_RULE +';'
                END;
   PRINT @SQL;

   FETCH C INTO @TABLE_SCHEMA, @TABLE_NAME, 
                @CONSTRAINT_SCHEMA, @CONSTRAINT_NAME, 
                @MATCH_OPTION, @UPDATE_RULE, @DELETE_RULE, 
                @U_TABLE_SCHEMA, @U_TABLE_NAME,
                @U_CONSTRAINT_SCHEMA, @U_CONSTRAINT_NAME;
END

CLOSE C;

DEALLOCATE C;

END;


Cette procédure se basant sur les vues d'information de schéma de la norme SQL, vous n'aurez pas de difficulté à la remanier pour l'implanter au sein de votre SGBDR.


Exemple d'exécution :

 
Sélectionnez
EXEC dbo.P_DDL_FK_CONSTRAINTS 'DROP'

V. C'est spécifique

Il n'y a rien de spécifique dans la gestion par contrainte de l'intégrité référentielle. Elle repose sur la norme SQL utilisée à ce niveau par tous les SGBDR. Tous les bons gestionnaires de bases de données relationnelles s'y sont mis, mais certains très tardivement il est vrai… Certes tout le monde ne respecte pas toutes les options disponibles de paramétrage des FOREIGN KEY mais ce n'est pas le plus important.


La syntaxe minimale qu'est CONSTRAINT … FOREIGN KEY (…) REFERENCES … (…) passe aujourd'hui sur tout les SGBDR transactionnels. Il n'y a donc aucune raison logique de donner un tel argument, sauf la méconnaissance, hélas bien répandue en matière de bases de données.

VI. En conclusion

Il n'y a rien de spécifique dans la gestion par contrainte de l'intégrité référentielle. Elle repose sur la norme SQL utilisée à ce niveau par tous les SGBDR. Tous les bons gestionnaires de bases de données relationnels s'y sont mis, mais certains très tardivement il est vrai… Certes tout le monde ne respecte pas toutes les options disponibles de paramétrage des FOREIGN KEY mais ce n'est pas le plus important.

VI-A. Arguments supplémentaires pour la mise en place des contraintes de clef étrangère

L'absence de contraintes d'intégrité référentielle a de multiples autres conséquences néfastes.

1) Cela empêche la pose des index les plus importants, ceux devant figurer sur les clefs étrangères. Or ces index sont en général tout à fait indispensables à un maintien basique des performances. En effet, la plupart des jointures des tables se font entre clef primaire et clef étrangère. En l'absence d'index sur ces éléments, les jointures seront lentes.

2) Cela interdit aux utilisateurs avancés comme aux administrateurs applicatifs de comprendre le modèle de données par la simple lecture des métadonnées de la base (vues SQL des contraintes FOREIGN KEY).

3) Cela empêche l'utilisation d'outil de rétro-ingénierie pour tenter de récupérer un modèle compréhensible, car ces outils se basent sur les métadonnées disponibles dans les vues système afin d'en déduire le modèle physique de données et, par là même, remonter à un MCD (modèle conceptuel de données).

4) Cela ne facilite pas les imports/exports du fait de la méconnaissance de l'imbrication des tables.

5) Cela lie encore plus l'utilisateur de la base à son créateur, chose que de nombreux éditeurs ont bien compris afin de rendre captifs leurs clients à un modèle opaque dans le but de vendre au prix fort des prestations qui n'auraient pas lieu d'être avec un modèle de données respectant les règles de l'art…

VI-B. Contreargument indéfendable : les progiciels

La plupart des mauvais éditeurs de progiciels (et ils sont hélas très nombreux) invoquent la nécessaire souplesse du modèle de données d'un progiciel pour défendre l'idée de l'absence de contrainte. C'est particulièrement stupide. En effet la pose de contrainte de clef étrangère est aussi dynamique que tout autre code SQL. Elle peut donc se faire à chaud, même si l'on rajoute une ou plusieurs tables au modèle.

Par exemple, lors de l'ajout dynamique d'une table à une base de données, dans le cas d'une nouvelle table liée à d'autres tables, la pose des différentes contraintes de clef étrangère permet d'automatiser la création de la jointure. En d'autres termes, grâce aux métadonnées des FOREIGN KEY, il est possible de réaliser des requêtes dont la jointure entre une nouvelle table créée dynamiquement et les autres tables de la base peut s'écrire automatiquement…

Démonstration : avec la requête SQL suivante :

 
Sélectionnez
WITH
T AS
(SELECT TCF.TABLE_SCHEMA + '.' + TCF.TABLE_NAME AS TF, 
        TCU.TABLE_SCHEMA + '.' + TCU.TABLE_NAME AS TM,
        FK.COLUMN_NAME AS COL_FK, UK.COLUMN_NAME AS COL_UK,
        ROW_NUMBER() OVER(ORDER BY FK.COLUMN_NAME) AS N
 FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RF
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK
              ON     RF.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA 
                 AND RF.CONSTRAINT_NAME   = FK.CONSTRAINT_NAME 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS UK
              ON     RF.UNIQUE_CONSTRAINT_SCHEMA  = UK.CONSTRAINT_SCHEMA
                 AND RF.UNIQUE_CONSTRAINT_NAME    = UK.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TCF
              ON     RF.CONSTRAINT_SCHEMA = TCF.CONSTRAINT_SCHEMA 
                 AND RF.CONSTRAINT_NAME   = TCF.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TCU
              ON     RF.UNIQUE_CONSTRAINT_SCHEMA = TCU.CONSTRAINT_SCHEMA 
                 AND RF.UNIQUE_CONSTRAINT_NAME   = TCU.CONSTRAINT_NAME
WHERE   TCF.TABLE_SCHEMA + '.' + TCF.TABLE_NAME = @TF
  AND   TCU.TABLE_SCHEMA + '.' + TCU.TABLE_NAME = @TM),
TF AS
(SELECT TF, TM, COL_FK, COL_UK, N, MAX(N) OVER() AS NN
 FROM   T),
TR AS
(SELECT CAST(TF + '.' + COL_FK + ' = ' 
           + TM + '.' + COL_UK AS VARCHAR(max)) AS JOINED, N, NN
 FROM   TF
 WHERE  N = 1
 UNION  ALL                 
 SELECT CAST(JOINED + ' AND ' + TF + '.' + TF.COL_FK + ' = ' 
                              + TM + '.' + TF.COL_UK AS VARCHAR(max)), TF.N, TF.NN
 FROM   TF
        INNER JOIN TR
              ON TF.N = TR.N + 1)
SELECT JOINED
FROM   TR              
WHERE  N = NN;

Qui admet en paramètre @TF la table fille et @TM la table mère afin de fournir l'écriture de la jointure. À nouveau, cette requête utilise les vues normalisées d'information de schéma avec lesquelles tous les bons SGBDR travaillent. Au pire, si ce n'est pas le cas de votre SGBDR, rien n'empêche de créer ces vues en partant des tables système de votre SGBDR.

VI-C. La Loi, les règles de l'art

L'absence de FOREIGN KEY dans une base de données apporte de multiples préjudices que nous venons d'énumérer. Le fait que la structure de la base, comme le code applicatif, ne sont pas de votre œuvre, ne peut en aucun cas justifier l'absence de professionnalisme. Par conséquent, le préjudice subi par l'absence de ces FOREIGN KEY peut donc être condamné devant les tribunaux. En effet, les principes généraux du droit sont la Loi, mais aussi les usages (us et coutumes) et la jurisprudence.

En matière professionnelle, les us et coutumes sont appelés règles de l'art. En matière de bases de données, les règles de l'art sont basées sur les travaux des professionnels reconnus de tous, comme Frank Edgar Codd (père de la théorie des bases de données relationnelles), Peter Chen (père du modèle relationnel), Donald Chamberlin et Raymond Boyce (auteurs du langage SQL) et enfin Chris Date et Hugh Darwen, experts en bases de données relationnelles. Parmi les textes qui parlent des contraintes, citons les règles de Codd, le modèle entité/association (Peter Chen), la norme SQL (disponible à l'ISO) ou encore les ouvrages de Date et Darwen comme « An Introduction to Relational Database Theory », ou « SQL and Relational Theory » (How to Write Accurate SQL Code) ou encore « Logic and Databases »… Tous ces textes mentionnent les contraintes d'intégrité référentielle comme un élément essentiel et indispensable de la mécanique relationnelle.

Il ne viendrait pas à l'esprit à un maçon auquel on demande de construire un mur d'enceinte de le faire penché ou que son arase soit ondulée. Il est donc stupéfiant et condamnable de voir que certains informaticiens considèrent comme superflu de respecter les fondements mêmes de la création des bases de données relationnelles et s'abstiennent de toute contrainte d'intégrité, en particulier référentielle. On doit même se poser la question  ?« À quoi sert un SGBDR si l'auteur de la structure de la base de données ne l'utilise pas de manière rationnelle ? » Ne serait-il pas mieux de revenir à la simple utilisation de fichiers Cobol de style ISAM ?

Lorsque l'on me demande mon avis pour choisir un logiciel dans une offre pléthorique, je conseille toujours de demander à l'éditeur le modèle conceptuel de données, car c'est celui qui sert de fondement à la modélisation des données. En effet, il faut partir du fait que, quels que soient la qualité du logiciel et son prix, ce n'est rien devant le capital que représentent vos données. N'oubliez jamais que vos données vous appartiennent. Choisir un outil incapable de les récupérer est souvent synonyme de gâchis.

Lorsqu'il n'y a pas de MCD ou que l'éditeur refuse de le communiquer (ce qui revient au même), je vous conseille vivement de vous pencher sur la structure de la base de données et si l'application vous tente vraiment de faire auditer la base.

Aucun refus d'aucune sorte n'est justifiable même lors de la phase d'avant vente. Certains éditeurs particulièrement peu scrupuleux vous glissent comme argument qu'étant donné que la base est leur œuvre, ils ne voudraient pas qu'elle soit pillée en la montrant à tout un chacun… C'est d'une grande bêtise, car lorsqu'on a une belle œuvre, on n'a pas honte de la montrer, d'autant que la justice protège du copiage la structure des bases de données. En effet, une jurisprudence constante démarrée il y a près de 100 ans (hé oui !) avec l'affaire Didot Bottin (CA Paris, 18 décembre 1924), renforcée par une directive européenne (du 11 mars 1996) traduite en droit français (loi n°98-536 du 1er juillet 1998 - Code de la Propriété Intellectuelle) protège la structure d'une base de données au titre des œuvres de l'esprit, exactement de la même manière que l'est tout code applicatif. Certains éditeurs honnêtes et parfaitement intelligents comme SAGE le savent bien et proposent à tous leurs clients de montrer comment a été dessinée l'architecture de la base et je dois dire que c'est souvent du bel ouvrage venant de chez eux… et pourtant il s'agit bien là aussi de progiciels !

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2009 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.