Discussion :

[DiverSIG]

Bonjour,

J'ai une table 'cod' qui référence des listes de valeurs avec une structure comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
COD_NOLIST  |  COD_CODE  |  COD_LIBELLE
1                   |  1                 |  Libellé 1
1                   |  2                 |  Libellé 2
1                   |  3                 |  Libellé 3
2                   |  1                 |  Libellé 4
2                   |  2                 |  Libellé 5

Je crée une table 'parametres' avec une variable 'prm_list', et je voudrais ajouter une contrainte pour m'assurer que la valeur entrée dans cette variable existe dans la variable COD_NOLIST de la table cod.
Une contrainte foreign key ça marche pas, parce qu'on a pas unicité de la valeur COD_NOLIST.
J'ai essayé une contrainte check :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT fk_prm_list CHECK (prm_list IN (SELECT DISTINCT cod_nolist FROM cod))

Mais ça renvoie une erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ERROR:  ne peut pas utiliser une sous-requête dans la contrainte de vérification

comment faire ?


Merci,

Nico

[al1_24]

Bonjour,

C'est une contrainte d'intégrité référentielle qu'il faut que tu mettes en œuvre ici.

[escartefigue]

En effet, c'est une contrainte d'intégrité référentielle qui est requise, et, si la base de données est modélisée en utilisant un logiciel de modélisation, alors le DDL produit contient toujours ce type de contraintes et, facultativement, selon le logiciel, les actions à effectuer en cas de suppression (ON DELETE CASCADE/RESTRICT/SET NULLL...) ou de modification (ON UDPATE....).

C'est un argument parmi bien d'autres qui plaide en faveur de l'utilisation systématique d'un logiciel de modélisation pour créer une base de données.

[Artemus24]

Salut à tous.

Dans ton exemple, ce qui me dérange est la formulation de ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT fk_prm_list CHECK (prm_list IN (SELECT DISTINCT cod_nolist FROM cod))

Tu veux stocker dans une colonne, une liste de valeur. Or, une colonne admet une unicité de valeur.
Je suppose aussi que si tu nommes ta colonne "prm_list", tu ne cherches pas à mettre une seule valeur mais tout une liste.

La réponse est bien celle donnée par "Al1_24". Mais il faut encore savoir comment la mettre en pratique.
Et c'est la que j'ai un doute sur la mise en place de ce que tu cherches à faire.

En fait, ton explication n'est pas clair car je ne comprends pas le rôle joué par ta colonne "prm_list".
Que veux tu mettre dans cette colonne ? Il manque un exemple pour ne pas créer d'ambiguïté sur ce que tu cherches à faire.

Tu dis :

Une contrainte foreign key ça ne marche pas, parce qu'on n'a pas unicité de la valeur COD_NOLIST.

A priori, je suppose que tu connais déjà cette "contrainte d'intégrité référentielle".
Mais ton problème est que tu as une clef primaire composée de plusieurs colonnes dans la table "cod".
Voici un exemple qui devrait répondre à ton problème :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
USE `base`
--------------
 
--------------
DROP TABLE IF EXISTS `cod`
--------------
 
--------------
CREATE TABLE `cod`
( `cod_id`       integer unsigned NOT NULL auto_increment primary key,
  `cod_nolist`   integer unsigned NOT NULL,
  `cod_code`     integer unsigned NOT NULL,
  `cod_libelle`  varchar(255)     NOT NULL,
  unique key (`cod_nolist`,`cod_code`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `cod` (`cod_nolist`,`cod_code`,`cod_libelle`) values (1, 1, 'libelle 1'),(1, 2, 'libelle 2'),(1, 3, 'libelle 3')
--------------
 
--------------
insert into `cod` (`cod_nolist`,`cod_code`,`cod_libelle`) values (2, 1, 'libelle 4'),(2, 2, 'libelle 5')
--------------
 
--------------
insert into `cod` (`cod_nolist`,`cod_code`,`cod_libelle`) values (1, 1, 'libelle 6')
--------------
 
ERROR 1062 (23000) at line 25: Duplicata du champ '1-1' pour la clef 'cod.cod_nolist'
--------------
select * from `cod`
--------------
 
+--------+------------+----------+-------------+
| cod_id | cod_nolist | cod_code | cod_libelle |
+--------+------------+----------+-------------+
|      1 |          1 |        1 | libelle 1   |
|      2 |          1 |        2 | libelle 2   |
|      3 |          1 |        3 | libelle 3   |
|      4 |          2 |        1 | libelle 4   |
|      5 |          2 |        2 | libelle 5   |
+--------+------------+----------+-------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS `prm`
--------------
 
--------------
CREATE TABLE `prm`
( `prm_id`    integer unsigned NOT NULL auto_increment primary key,
  `prm_list`  integer unsigned NOT NULL,
  CONSTRAINT `FK_01` FOREIGN KEY (`prm_list`) REFERENCES `cod` (`cod_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `prm` (`prm_list`) values (1)
--------------
 
--------------
insert into `prm` (`prm_list`) values (7)
--------------
 
ERROR 1452 (23000) at line 42: Cannot add or update a child row: a foreign key constraint fails (`base`.`prm`, CONSTRAINT `FK_01` FOREIGN KEY (`prm_list`) REFERENCES `cod` (`cod_id`) ON DELETE CASCADE ON UPDATE CASCADE)
--------------
select * from `prm`
--------------
 
+--------+----------+
| prm_id | prm_list |
+--------+----------+
|      1 |        1 |
+--------+----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

En procédant ainsi, tu utilises la colonne "cod_id" pour associer le libellé que tu cherches à référencer.
La multiplicité des valeurs de la colonne "cod_nolist" est ainsi résolu.
A toi de nous dire si cela te convient ou pas ?

[escartefigue]

Dans la table COD, l'unicité est assurée par le couple (COD_NOLIST, COD_CODE) qui semble être la PK (primary key).
Or, le plus souvent, quand une PK est composée de plusieurs colonnes de type numérique, c'est qu'on est en présence d'identification relative : une partie de la PK est une foreign key héritée d'une autre table.

Ainsi, on est probablement dans cette situation, au niveau conceptuel (j'ai modifié les noms des entités, pour éviter les confusions avec les balises CODE et LISTE utilisées par l'éditeur de developpez.com) :


et donc au niveau tabulaire :


Il faut en ce cas que la nouvelle table à ajouter fasse référence à cette PK composée. On modifiera donc le modèle conceptuel pour y ajouter la nouvelle entité [PARAM] ainsi (j'ai ajouté arbitrairement quelques attributs) :


qui donne le modèle tabulaire suivant :


Comme j'ai utilisé un logiciel de modélisation, en l'occurrence l'excellent Looping, après avoir choisi le SGBD PostGre, j'obtiens automatiquement le DDL qui suit :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE LST(
   LST_NOLIST SERIAL,
   LST_LIBELLE VARCHAR(50) NOT NULL,
   PRIMARY KEY(LST_NOLIST)
);
 
CREATE TABLE COD(
   LST_NOLIST INTEGER,
   COD_NOCODE INTEGER,
   COD_LIBELLE VARCHAR(50) NOT NULL,
   PRIMARY KEY(LST_NOLIST, COD_NOCODE),
   FOREIGN KEY(LST_NOLIST) REFERENCES LST(LST_NOLIST) on delete restrict on update cascade
);
 
CREATE TABLE PRM(
   PRM_NOPARAM SMALLINT,
   PRM_LIBELLE VARCHAR(50) NOT NULL,
   PRM_DEBVAL DATE NOT NULL,
   PRM_FINVAL DATE NOT NULL,
   LST_NOLIST INTEGER NOT NULL,
   COD_NOCODE INTEGER NOT NULL,
   PRIMARY KEY(PRM_NOPARAM),
   FOREIGN KEY(LST_NOLIST, COD_NOCODE) REFERENCES COD(LST_NOLIST, COD_NOCODE) on delete restrict on update cascade
);

Notes :

1. la cardinalité marquée (R) entre [COD] et (référencer) signifie qu'on identifie [COD] relativement à [LST] dont [COD]dépend.
   
   c'est ce qui fait que la primary key LST_NOLIST de [LST] participe à la primary key de [COD] ;
2. j'aurais pu, le cas échéant, identifier également [PRM] relavivement à [COD], auquel cas la PK de [PRM] aurait été composée de 3 colonnes : LST_NOLIST + COD_NOCODE + PRM_NOPARAM ;
3. grâce à l'utilisation d'un logiciel de modélisation, une même colonne porte par défaut le même nom là où elle est PK et là où elle est héritée comme FK.
   
   Ainsi, la colonne LST_NOLIST PK de [LST] qu'on retrouve comme FK dans [COD] et dans [PRM] porte partout le même nom.
   Ça facilite les études d'impact et évite les erreurs de typage ou de longueur ;
4. également grâce à l'utilisation d'un logiciel de modélisation, le DDL est correct et la contrainte foreign key est directement codée, avec les actions associées souhaitées.
   

Ainsi, comme je l'indiquais plus haut, si vous aviez utilisé un logiciel de modélisation, votre script aurait été directement correct.

Vous pouvez télécharger gratuitement Looping ICI

[Michel.Priori]

Dans la table COD, l'unicité est assurée par le couple (COD_NOLIST, COD_CODE) qui semble être la PK (primary key).

Par principe, j'évite d'avoir une PK composite ; surtout pour servir de référence de FK.

Je préfère ajouter une colonne ID en tant que compteur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

id bigint GENERATED ALWAYS AS IDENTITY

en faire la PK
Les FK pointeront sur la PK (d'1 colonne)
Et pour la bonne forme, il faut ajouter un index unique sur (COD_NOLIST, COD_CODE) et les contraintes NOT NULL

Mais POURQUOI ???
parce qu'il est plus performant tant en écriture du SQL qu'en temps de traitement (lorsqu'il y aura du volume et de l'activité ^^) d'évaluer un rapprochement de tableau à 1 colonne plutôt qu'à 2.

Il est cependant exact qu'en adoptant ceci, on se prive de simplifier les requêtes quand la table intermédiaire peut être "oubliée" sans mettre en danger la cohérence du résultat.
Mais c'est "normalement" rare.

[escartefigue]

Bonjour Michel,

Ceci est une digression par rapport à la question initiale, mais c'est un point très important.

Si la table concernée est issue d'une entité du modèle conceptuel, alors on a en effet le plus souvent une PK mono colonne et c'est très bien ainsi.

Si la table concernée est issue d'une association du modèle conceptuel, alors la PK aura autant de colonnes qu'il y a d'attributs identifiants dans les entités du MCD qui participent à l'association et il ne saurait en être autrement
Par exemple, la table issue d'une association binaire dont les deux entités ont une seule colonne identitifiante aura une PK composée de deux colonnes. C'est incontournable.
Créer une PK mono colonne pour cette table associative serait une erreur, car ça permettrait de créer des liens multiples là où l'unicité des paires de FK s'impose.

Mais si on complique un peu le modèle conceptuel et qu'on utilise l'identification relative, par exemple, avec le cas d'école des clients et de leurs commandes, on modélise souvent ainsi (notez le (R) de [LC_ligne] vers (contenir)).



Avec ce MCD, tout logiciel de modélisation donne le script suivant (ici décliné à la sauce PostGre) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE CL_client(
   CL_ident SERIAL,
   CL_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(CL_ident)
);
 
CREATE TABLE CO_commande(
   CO_ident SERIAL,
   CO_date DATE NOT NULL,
   CL_ident INTEGER NOT NULL,
   PRIMARY KEY(CO_ident),
   FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident)
);
 
CREATE TABLE AR_article(
   AR_ident SERIAL,
   AR_reference CHAR(8) NOT NULL,
   AR_libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(AR_ident),
   UNIQUE(AR_reference)
);
 
CREATE TABLE LC_ligne_commande(
   CO_ident INTEGER,
   LC_sequence SMALLINT,
   LC_quantite NUMERIC(9,2) NOT NULL,
   AR_ident INTEGER NOT NULL,
   PRIMARY KEY(CO_ident, LC_sequence),
   FOREIGN KEY(CO_ident) REFERENCES CO_commande(CO_ident),
   FOREIGN KEY(AR_ident) REFERENCES AR_article(AR_ident)
);

On constate, et c'est normal - identification relative oblige, que la table LC possède une PK constituée de deux colonnes : la PK de la commande, complétée d'un numéro de ligne.
Dans cette table, si on définit l'index de la PK comme index cluster, alors toutes les lignes d'une même commande seront rangées physiquement dans un espace contigu. Ainsi, en un seul accès disque, on ramassera toutes les lignes d'une même commande. À l'inverse, si j'avais opté pour une identification absolue (un numéro de ligne seul), dans un environnement concurrentiel où plusieurs threads créent des lignes pour plusieurs commandes, ces lignes auraient porté des numéros non contigus et de ce fait dispersés dans l'espace disque.
En outre, comme le plus souvent il y a peu d'occurrences de lignes pour une même commande, un type numérique court (small integer) donc peu encombrant suffit.
Ici, j'ai pris un cas de modèle de données simples, mais si j'enrichis ce modèle en y ajoutant les engagements de commandes, les lignes de livraison, etc. on observe le même phénomène, très bénéfique aux performances.
Si l'identification relative est propagée dans d'autres tables au travers de clefs étrangères, il est très facile de réaliser des jointures avec la table parente (ici la table commande) sans passer par toute une série de tables intermédiaires, ce qui simplifie certaines jointures et augmente aussi les performances.


En résumé, contrairement à ce que tu affirmes, l'identification relative, dont la conséquence est justement l'obtention d'une PK multi-colonnes est bénéfique aux performances et ce mesures de performances à l'appui

[Michel.Priori]

Bonjour Michel,

Bonjour Escartefigue

Merci pour ce retour très bien explicité.

Nous sommes en phase
Dans mon brouillon j'avais pensé à écrire cette exclusion ; puis, la flemme ...
Et je ne l'aurais pas exprimé avec un tel talent.
Merci.

[Artemus24]

@ Escartefigue : je vois que tu es devenu un expert dans la modélisation que tu maitrises parfaitement.
Pourquoi ce choix de PostGre ? Et non pas Microsoft SQL Server ?

La question posée par DiverSIG me gène car je ne sais pas trop ce qu'il veut mettre dans sa colonne PRM_LIST et comment il va l'exploiter.
J'ai compris qu'il voulait mettre une seule valeur mais issue de la colonne COD_NOLIST.
Quel est l'intérêt de procéder ainsi car il aurait à sa disposition plusieurs libellé, puisque la colonne COD_CODE pouvant être multiple en valeur.
Je suis comme Michel.Priori, je pense plutôt à une table associative où la valeur à mettre dans la colonne PRM_LIST serait unique, même si elle regroupe plusieurs associations.

Je ne cherche pas une modélisation à ce problème mais de comprendre le choix qui sera fait ici pour résoudre le problème de DiverSIG.
J'aimerai que DiverSIG vienne nous éclairer sur ce point.

[escartefigue]

Bonjour Artemus24

Pourquoi ce choix de PostGre ? Et non pas Microsoft SQL Server ?

Parce que la question est posée dans la section PostGreSQL, mais en vérité, ici, le choix du SGBD n'a guère d'importance.

Je ne cherche pas une modélisation à ce problème mais de comprendre le choix qui sera fait ici pour résoudre le problème de DiverSIG.
J'aimerai que DiverSIG vienne nous éclairer sur ce point.

J'en suis venu à la modélisation, car d'une part c'est souvent la source de bien des maux et surtout j'ai supposé qu'on avait affaire ici à une table associative.
Et si c'est bien le cas, affubler une table associative d'une PK artificielle est une erreur qui ne fait qu'ajouter de la complexité et des risques de redondances.

[Artemus24]

Salut Escartefigue.

Désolé, je n'avais pas vérifié que nous étions dans la section PostGreSQL.

j'ai supposé qu'on avait affaire ici à une table associative.

C'est ce qu'il m'a semblé être le cas, sauf que j'ai eu un doute dans la formulation de la question posée par DiverSIG.

Et si c'est bien le cas, affubler une table associative d'une PK artificielle est une erreur qui ne fait qu'ajouter de la complexité et des risques de redondances.

Je ne peux pas confirmer que cela soit le cas, car avec aussi peu d'information sur le problème, il se peut que cela soit tout autre chose.
Sinon, oui, encore un problème de modélisation mal posé.

[fsmrel]

@ escartefigue

Capitaine, dans le DDL (cf. post #7),concernant la table LC_ligne_commande, la présence de la clé alternative
{CO_ident, AR_ident} est plus que fortement recommandée...

Cette recommandation vaut évidemment, mutatis mutandis, pour la table PRM (post #5).

[escartefigue]

Certes, les sripts que j'ai fournis n'étaient que des ébauches à minima, cette digression sur la modélisation n'étant pas le sujet initial du fil de discussion.
Mais la nature de la question m'incite à penser que c'est bien par là que le bât blesse... comme très souvent du reste.