Clé étrangère dépendant d'un champ ou héritage dans sgbd

**benwit** · 26/07/2009, 12h39

Bonjour,

Problème

Soit une table t_a, une table t_b et une table t_c.

Dans t_c, en utilisant la notion de clé étrangère, je peux faire référence à :

un enregistrement de t_a via un champ c_a_id : CONSTRAINT fk_c_a FOREIGN KEY (c_a_id) REFERENCES a(a_id)
un enregistrement de t_b via un champ c_b_id : CONSTRAINT fk_c_b FOREIGN KEY (c_b_id) REFERENCES b(b_id)

Jusque là, c'est OK mais il se trouve que dans t_c, j'ai un autre champ qui :
tantôt devrait faire référence à un enregistrement de t_a,
tantôt devrait faire référence à un enregistrement de t_b.

1° solution :
créer un champ c_a_or_b qui m'indique s'il s'agit de t_a ou t_b,
créer un champ c_a_or_b_id qui m'indique l'identifiant de l'enregistrement dans t_a ou t_b.

Avantage : une seule table.
Inconvénient : pas de contrôle au niveau de base si je ne peux pas mettre de clé étrangère conditionnée par c_a_or_b.

2° solution :
à la place de t_c, créer deux tables t_ca et t_cb où je peux désormais me passer de c_a_or_b et mettre une contrainte de clé étrangère pour qualifier cette référence supplémentaire vers t_a ou t_b.

Avantage : contrôle au niveau de base car je peux mettre une contrainte
Inconvénient : nécessite des unions pour récupérer les données dans les deux tables t_ca et t_cb

Remarque :
L'œil expert aura reconnu deux manières d'implémenter l'héritage :

une avec une table commune (t_c) et un discriminant (c_a_or_b),
l'autre avec deux tables (t_ca, t_cb)

Mes questions :

Dans la solution 1, y a t'il moyen d'ajouter une clé étrangère sur (c_a_or_b_id) fonction d'un champ (c_a_or_b) ? de manière standard ou propriétaire ?
y a t'il moyen de faire ce contrôle au niveau de la base ? de manière standard ou propriétaire ?

Voyez vous une autre solution ?

Quelle est la meilleure solution selon vous ? suivant le cas d'usage peut être ?

**fsmrel** · 26/07/2009, 21h01

Bonsoir,

Envoyé par benwit

dans t_c, j'ai un autre champ qui :
tantôt devrait faire référence à un enregistrement de t_a,
tantôt devrait faire référence à un enregistrement de t_b.

Tout d’abord, « champ » et « enregistrement » sont des termes qu’on utilise pour les fichiers ou les SGBD pré-relationnels. J’utiliserai donc les termes SQL « colonne » et « ligne » (si l’on utilisait le Modèle relationnel, ça serait les termes « attribut » et « n-uplet » (ou « tuple »)).

Maintenant, appelons a_ou_b cet autre attribut de la table t_c.

Envoyé par benwit

1° solution :
créer un champ c_a_or_b qui m'indique s'il s'agit de t_a ou t_b,
créer un champ c_a_or_b_id qui m'indique l'identifiant de l'enregistrement dans t_a ou t_b.

Vous pouvez coder :

Code SQL :

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

1
2
3
4
5
6
7
8
create table t_C 
 (
    c_id   int   not null,
    a_ou_b int   null
  primary key (c_id),
  foreign key (a_ou_b) references t_a,
  foreign key (a_ou_b) references t_b
 ) ;

Ainsi qu’un trigger pour assurer la contrainte de partitionnement (totalité et exclusion).
Inconvénient : présence de NULL. Pour pallier, vous pouvez aussi mettre en relation t_a et t_c par le biais d’une table t_ac, et mettre en relation t_b et t_c par le biais d’une table t_bc :

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
create table t_C 
(
    c_id   int   not null,
  primary key (c_id)
) ;
create table t_ac 
(
    c_id   int   not null,
	a_id   int   not null,
  primary key (c_id),
  foreign key (c_id) references t_c,
  foreign key (a_id) references t_a
) ;
create table t_bc 
(
    c_id   int   not null,
	b_id   int   not null,
  primary key (c_id),
  foreign key (c_id) references t_c,
  foreign key (b_id) references t_b
) ;

Le coup du trigger reste valable.

N.B. Si les valeurs de l'attribut a_id sont uniques au sein de la table t_ac, alors ajouter une contrainte UNIQUE à cet effet (idem pour t_bc).

Envoyé par benwit

[B]1 2° solution :
à la place de t_c, créer deux tables t_ca et t_cb où je peux désormais me passer de c_a_or_b et mettre une contrainte de clé étrangère pour qualifier cette référence supplémentaire vers t_a ou t_b.

Avantage : contrôle au niveau de base car je peux mettre une contrainte
Inconvénient : nécessite des unions pour récupérer les données dans les deux tables t_ca et t_cb

Rien ne vous empêche de définir une vue d’union, en sorte que les applications ne voient qu’une table (virtuelle).

Envoyé par benwit

L'œil expert aura reconnu deux manières d'implémenter l'héritage

A ceci près que lorsque vous écrivez

« dans t_c, j'ai un autre champ qui tantôt devrait faire référence à un enregistrement de t_a, tantôt devrait faire référence à un enregistrement de t_b »

on infère que t_c hérite soit de t_a soit de t_b, donc que votre héritage relève du polymorphisme. Par ailleurs, vous ne précisez pas si pour une valeur de l’attribut a_id de t_a on peut avoir plus d’une valeur de l’attribut a_id de t_c (idem pour t_b, toutes choses égales par ailleurs). A défaut, on peut interpréter votre montage comme une agrégation (dans le sens par exemple de la copropriété).

**benwit** · 27/07/2009, 11h09

Merci pour cette réponse.

Je reconnais n'avoir pas été rigoureux et je parle par abus de langage parfois indifféremment de champs/colonne/attribut, enregistrements/ligne/objet, fichiers/table/classe. Il est normal qu'en préambule à la réponse, vous fassiez une mise au point pour éviter les ambigüités.

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE t_C 
 (
    c_id   int   NOT NULL,
    a_ou_b int   NULL
  PRIMARY KEY (c_id),
  FOREIGN KEY (a_ou_b) REFERENCES t_a,
  FOREIGN KEY (a_ou_b) REFERENCES t_b
 ) ;

Je savais pas qu'on pouvais mettre 2 clés étrangères sur une même colonne.
Le sgbd va donc ralé si je met dans a_ou_b l'id "5" si celui ci n'est donc présent ni dans t_a, ni dans t_b
mais il ne me dira rien s'il est absent juste de l'une d'entre elle ?

Le trigger est il nécessaire dans le cas où
- les deux références doivent être vérifiées ?
- une seule des deux références doivent être vérifiées ?

**fsmrel** · 27/07/2009, 13h31

Bonjour,

Envoyé par benwit

Le sgbd va donc ralé si je met dans a_ou_b l'id "5" si celui ci n'est donc présent ni dans t_a, ni dans t_b
mais il ne me dira rien s'il est absent juste de l'une d'entre elle ?

Il est vrai que le SGBD rejettera, la valeur "5" si celle-ci est absente de t_a et/ou de t_b, donc la 1re proposition que j’ai faite n’est pas à retenir.

En revanche, ma proposition qui consiste à mettre en œuvre les tables t_ac et t_bc est valable. A noter que si l’on insère la valeur de clé étrangère "5" dans t_ac (en référence avec la table t_c), il faudra s’assurer (trigger) que cette valeur n’existe pas déjà dans t_ab (en référence avec la table t_c). Même principe si l'on insère cette valeur dans t_bc.

**skuatamad** · 28/07/2009, 01h04

Bonsoir,

Tout d'abord fsmrel je suis un peu surpris par votre réponse, je pensais que vous seriez plus hostile à la solution 1 consistant à insérer des valeurs provenant de 2 tables t_a et t_b dans une seule colonne d'une table t_c.
Je ne trouve pas cette solution très correct en terme de modélisation relationnelle, ceci dit je n'ai pas votre expertise dans ce domaine (et encore moins dans les concepts d'héritage/polymorphisme), il existe donc peut être certaines exceptions (mais j'en doute pour moi ça revient à mélanger des torchons et des serviettes).

Pour revenir à ta problématique benwit, je dirais :

Envoyé par benwit

Avantage : une seule table.
Inconvénient : pas de contrôle au niveau de base si je ne peux pas mettre de clé étrangère conditionnée par c_a_or_b.

Je ne pense pas que le nombre de tables à créer soit un avantage ou un inconvénient.
Dans les inconvénients, je rajouterais :
complexifie l'écriture des requêtes notemment pour faire des jointures (nécessite des sous-requêtes et des unions également).
Concernant l'inconvénient du manque de contrainte FK, il est possible comme précisé par fsmrel de développer des triggers.
Il est également possible en fonction des SGBDR (à ce sujet quel est ton SGBDR ?) d'implémenter les contraintes via des vues matérialisées.
Mon exemple est développé sous oracle mais je pense que la technique est valable quelque soit le SGBDR (à la syntaxe près) du moment que les vues matérialisées sont disponnibles (donc pas mysql ou access par ex)

Création des tables :

Code :

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
DROP MATERIALIZED VIEW LOG ON t_C_1_table
/
DROP MATERIALIZED VIEW vm_t_c
/
drop table t_a
/
drop table t_b
/
drop table t_c_1_table
/
drop table t_ac
/
drop table t_bc
/
 
create table t_a (a_id int,
                 a_val varchar2(1),
                 primary key (a_id)
                 )
/
 
create table t_b (b_id int,
                 b_val varchar2(1),
                 primary key (b_id)
                 )
/
 
CREATE TABLE t_c_1_table
(
    c_id   int   NOT NULL,
    c_a_or_b varchar2(1),
    c_a_or_b_id int,
  PRIMARY KEY (c_id)
)
/
 
 
CREATE MATERIALIZED VIEW LOG ON t_c_1_table WITH PRIMARY KEY;
 
create MATERIALIZED VIEW vm_t_c
REFRESH FAST ON COMMIT
AS
select c_id,
       (case when c_a_or_b='a' then c_a_or_b_id else -1 end) as a_id,
       (case when c_a_or_b='b' then c_a_or_b_id else -1 end) as b_id
from t_c_1_table
/
 
 
alter table vm_t_c add FOREIGN KEY (a_id) REFERENCES t_a(a_id) DEFERRABLE
/
alter table vm_t_c add FOREIGN KEY (b_id) REFERENCES t_b(b_id) DEFERRABLE
/
 
insert into t_a values (-1,'b');
insert into t_a values (1,'a');
insert into t_a values (2,'a');
insert into t_a values (3,'a');
 
insert into t_b values (-1,'a');
insert into t_b values (1,'b');
insert into t_b values (2,'b');
insert into t_b values (4,'b');
commit;

et le jeu de test :

Code :

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
SQL> insert into t_c_1_table values (1,'a',1);
 
1 ligne crÚÚe.
 
SQL> insert into t_c_1_table values (2,'b',1);
 
1 ligne crÚÚe.
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> insert into t_c_1_table values (3,'b',3);
 
1 ligne crÚÚe.
 
SQL> commit;
commit
*
ERREUR Ó la ligne 1 :
ORA-12008: erreur dans le chemin de rÚgÚnÚration de la vue matÚrialisÚe
ORA-02291: violation de contrainte d'intÚgritÚ (SKUATAMAD.SYS_C004089) - clÚ parent
introuvable
 
 
SQL> insert into t_c_1_table values (3,'a',3);
 
1 ligne crÚÚe.
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> select * from t_c_1_table;
 
      C_ID C C_A_OR_B_ID
---------- - -----------
         1 a           1
         2 b           1
         3 a           3
 
SQL>

Il y avait d'ailleurs une solution 1 bis à ta problématique :
1 seule table t_c (c_id,a_id,b_id), mais je crois, si j'ai bien compris, que tu as déjà dans ta table les colonnes a_id et b_id et que tu veux rajouter une colonne batarde a_or_b_id.

Sinon concernant la solution 2, il est également possible d'utiliser les VM pour enforcer une contrainte d'unicité par exemple:
c_id doit être relié à 1 a_id ou 1 b_id mais pas les 2 à la fois (mais tu peux résoudre d'autres types de contraintes en utilisant les contraintes CHECK):
Création des tables :

Code :

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
 
drop MATERIALIZED VIEW LOG ON t_ac;
drop MATERIALIZED VIEW LOG ON t_bc;
drop MATERIALIZED VIEW LOG ON t_c;
drop MATERIALIZED VIEW vm_ca_union_cb;
 
drop table t_ac
/
drop table t_bc
/
drop table t_c
/
 
CREATE TABLE t_c
(
    c_id   int   NOT NULL,
  PRIMARY KEY (c_id)
)
/
 
CREATE TABLE t_ac
(
    c_id   int   NOT NULL,
	a_id   int   NOT NULL,
  PRIMARY KEY (c_id),
  FOREIGN KEY (c_id) REFERENCES t_c,
  FOREIGN KEY (a_id) REFERENCES t_a
)
/
CREATE TABLE t_bc
(
    c_id   int   NOT NULL,
	b_id   int   NOT NULL,
  PRIMARY KEY (c_id),
  FOREIGN KEY (c_id) REFERENCES t_c,
  FOREIGN KEY (b_id) REFERENCES t_b
)
/
 
CREATE MATERIALIZED VIEW LOG ON t_ac WITH rowid INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON t_bc WITH rowid INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON t_c WITH rowid INCLUDING NEW VALUES;
 
 
create MATERIALIZED VIEW vm_ca_union_cb
REFRESH FAST ON COMMIT
AS
select t_ac.rowid as rid,
       t_ac.c_id,
       t_ac.a_id as c_a_or_b_id,
       't_ac' QMARK
from t_ac
union all
select t_bc.rowid as rid,
       t_bc.c_id,
       t_bc.b_id as c_a_or_b_id,
       't_bc' QMARK
from t_bc
/
 
alter table vm_ca_union_cb add constraints uk_c_id unique (c_id);

Et le jeu de test :

Code :

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
SQL> insert into t_c values (1);
 
1 ligne crÚÚe.
 
SQL> insert into t_c values (2);
 
1 ligne crÚÚe.
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> insert into t_ac values (1,1);
 
1 ligne crÚÚe.
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> insert into t_bc values (1,1);
 
1 ligne crÚÚe.
 
SQL> commit;
commit
*
ERREUR Ó la ligne 1 :
ORA-12008: erreur dans le chemin de rÚgÚnÚration de la vue matÚrialisÚe
ORA-00001: violation de contrainte unique (SKUATAMAD.UK_C_ID)
 
SQL> select * from vm_ca_union_cb;
 
RID                      C_ID C_A_OR_B_ID QMAR
------------------ ---------- ----------- ----
AAADX7AAEAAAAHPAAA          1           1 t_ac

Et tu retrouves la table t_c de la solution 1 (qmark=c_a_or_b) que tu trouvais plus simple à intéroger.

Mais bon ça dépend de ce que tu veux :

Code :

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
SQL> select * from t_c_1_table;
 
      C_ID C C_A_OR_B_ID
---------- - -----------
         1 a           1
         2 b           1
         3 a           3
 
SQL> select c.c_id,a.a_val
  2  from t_c_1_table c
  3  join t_a a on (c.c_a_or_b_id=a.a_id)
  4  join t_b b on (c.c_a_or_b_id=b.b_id)
  5  /
 
      C_ID A
---------- -
         1 a
         2 a
 
SQL> select c.c_id,c.c_a_or_b_id,a.a_val as val
  2  from t_c_1_table c
  3  join t_a a on (c.c_a_or_b_id=a.a_id and c.c_a_or_b='a')
  4  union all
  5  select c_id,c.c_a_or_b_id,b.b_val as val
  6  from t_c_1_table c
  7  join t_b b on (c.c_a_or_b_id=b.b_id and c.c_a_or_b='b')
  8  /
 
      C_ID C_A_OR_B_ID V
---------- ----------- -
         1           1 a
         3           3 a
         2           1 b

Bon tout ce blabla sur les VM ne t'intéressera peut être pas directement (surtout si elles n'existent pas dans ton SGBD

) mais l'idée c'est que développer des triggers peut être long et fastideux mais surtout ils peuvent générer,si mal testés, des bugs notemment en concurance d'accès (surtout vrai chez oracle avec les readers (select) et les writers (update) qui ne se bloquent pas mutuellement).

Pour conclure je dirais que créer peu de tables semble simplifier le schéma certes, mais généralement complexifie le développement des requêtes.
Pour mettre en relation des données, il faut créer des tables également appelée "relation".
Je vote donc pour la solution 2 également.

[EDIT] Bon c'était pour montrer qu'il était possible de créer des contraintes complexes dans la base de façon un peu détournée.
Mais ça n'est absolument pas une solution miracle en 2 clics, car déjà les VM sont des objets qui ont plein de limitation et donc il peut être délicat de trouver la bonne syntaxe, mais aussi parce qu'à la base les VM ne sont pas faitent pour ça donc il faut bien penser à benchmarker un peu, notamment en cas d'applicatif très très fortement OLTP, mais bon les triggers ça plombent aussi les perfs.

**fsmrel** · 28/07/2009, 02h34

Bonsoir,

Envoyé par skuatamad

fsmrel je suis un peu surpris par votre réponse, je pensais que vous seriez plus hostile à la solution 1 consistant à insérer des valeurs provenant de 2 tables t_a et t_b dans une seule colonne d'une table t_c.

Sans doute avez-vous mal lu. Je n’ai pas dit que j’étais favorable à cette solution, mais qu’on pouvait tenter de l'interpréter, la justifier au nom du polymorphisme, puisque benwit a parlé d’héritage : il lui fallait une réponse à ce sujet.

En revanche, vous aurez noté que je suis hostile au Bonhomme NULL, d’où ma proposition d’évolution qui consiste à mettre en œuvre les tables t_ac et t_bc. Je reprends ici le code que j’ai proposé (en complétant avec les Create Table correspondant à t_a et t_b ) :

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 t_a 
(
    a_id  int   not null,
  primary key (a_id)
) ;
create table t_b 
(
    b_id  int   not null,
  primary key (b_id)
) ;
create table t_c 
(
    c_id   int   not null,
  primary key (c_id)
) ;
create table t_ac 
(
    c_id   int   not null,
    a_id   int   not null,
  primary key (c_id),
  foreign key (c_id) references t_c,
  foreign key (a_id) references t_a
) ;
create table t_bc 
(
    c_id   int   not null,
    b_id   int   not null,
  primary key (c_id),
  foreign key (c_id) references t_c,
  foreign key (b_id) references t_b
) ;

Avec un MLD, ça sera peut-être moins indigeste et plus parlant :

N.B. Si par ailleurs {a_id} est clé candidate de t_ac, il faudra ajouter la clause UNIQUE qui va bien (même chose pour l'attribut b_id de la table t_bc).

En tout état de cause, sur la base des informations données par benwit, au niveau conceptuel cette solution est parfaitement satisfaisante et saine.

**benwit** · 28/07/2009, 09h21

Envoyé par skuatamad

Je ne pense pas que le nombre de tables à créer soit un avantage ou un inconvénient.

Je ne pense pas non plus.

Ce que je voulais dire par là, c'est que dans ma problématique d'héritage (a et b ont un ancètre commun d), si je veux tous les d (a ou b) associé à un e (via t_c qui est une table de jointure entre t_e et t_d), je peux les obtenir en un seul select avec la solution d'une seule table t_c (sol1) alors qu'il faut faire un union avec t _ca et t_cb (sol2).
Ceci dit, si je veux uniquement les d de type a ou des d de type b, il faut ajouter un critère dans sol1.
C'était également oublié comme tu le dis justement :
- qu'une seule table avec une colonne polymorphe peut sembler "batarde" dans le modèle relationnel
- qu'on pourrait également choisir la solution 1bis (avec 1 null à chaque ligne)
- qu'à l'inverse, ça complexifie d'autres requêtes ...
La sol2 semble donc plus "propre".

Envoyé par skuatamad

Il est également possible en fonction des SGBDR (à ce sujet quel est ton SGBDR ?) d'implémenter les contraintes via des vues matérialisées.

Je n'ai pas encore décidé ... je crains que dans les "open source" il n'existe pas de VM ?

Un grand merci à tous les deux d'avoir pris le temps de développer vos réponses.

**skuatamad** · 28/07/2009, 14h11

Fsmrel, merci pour vos précisions, je me doutais bien que la solution 1 ne vous convenait pas mais maintenant c'est plus clair.

Benwit, concernant les vues matérialisées dans les SGBD open source, elles existent bien dans postgreSQL, par contre je ne sais pas si la (nécessaire) méthode REFRESH ON COMMIT existe.
Une rapide recherche ne m'a pas convaincu, cependant tu peux gérer ce type de contrainte via les triggers.

En tout cas, si ça peut t'aider à faire un choix, le langage procédural de postgre est très abouti, contrairement à mysql encore un peu débutant dans ce domaine (je ne connais pas firebird).

Clé étrangère dépendant d'un champ ou héritage dans sgbd

Langage SQL

Discussions similaires

Partager

Partager