Discussion :

[WinNew]

Bonjour à tous,

Je développe un logiciel libre destiné à l'analyse d'une base de donnée. Pour le moment, le logiciel génère des représentations graphiques "propres" (optimisation du placement des tables pour limiter les croisements entre les relations) du modèle de donnée. Le niveau de détail des représentations est réglable (vue d'ensemble, représentation détaillée, représentation très détaillée).

Je m'attaque maintenant à certaines fonctionnalités qui me posent des problèmes. Je réalise que mes connaissances sur les bases de données ne sont pas très poussées, malgré le fait que je les utilise depuis plus de 10 ans.

Note : Je tiens à préciser que j'utilise uniquement MySql. Mais le logiciel que je développe est prévu pour s'adapter à toutes les bases. Pour information, il utilise l'API unifiée offerte par JDBC pour l'extraction des informations sur la structure de la base (méta données). Et il est possible de surcharger certaines fonctionnalités pour s'adapter à d'éventuelles particularités.

Ma question est la suivante :

Sous MySql, il semble qu'une clé étrangère ne peut pointer que vers une clé primaire.

Pour fixer les idées, voici un exemple valide de déclaration de clé étrangère.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS tableB;                   	  
CREATE TABLE tableB ( id INT NOT NULL,
                      otherField INT NOT NULL,
                   	  PRIMARY KEY(id))
                   	  ENGINE=INNODB; 
 
DROP TABLE IF EXISTS tableA;
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(id),
                   	  PRIMARY KEY(id))
                   	  ENGINE=INNODB;

tableA.fkb pointe vers la clé primaire tableB.id.

Et voici un exemple non valide :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS tableB;                   	  
CREATE TABLE tableB ( id INT NOT NULL,
                      otherField INT NOT NULL,
                   	  PRIMARY KEY(id))
                   	  ENGINE=INNODB; 
 
DROP TABLE IF EXISTS tableA;
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(otherField),
                   	  PRIMARY KEY(id))
                   	  ENGINE=INNODB;

tableA.fkb pointe vers un champ qui n'est pas une clé primaire.

Cette règle est-elle valable pour tous les SGBD?

Merci,

Laurent

[StringBuilder]

La norme SQL dit qu'une clé étrangère doit porter sur un INDEX UNIQUE. C'est le cas d'une clé primaire, mais pas forcément.

J'ai un doute quand à la possibilité de nullité de l'index référencé (je pense qu'il ne peut pas être nullable, donc une simple contrainte d'unicité n'est pas suffisante, si je ne m'abuse -et de toute façon, c'est pas assez performant-).

Avec SQL Server par exemple, il est tout à fait possible d'utiliser un index unique pour une FK. A noter aussi qu'une clé étrangère peut être composite (c'est à dire plusieurs colonnes).

[WinNew]

Merci StringBuilder pour cette réponse rapide.

J'avais effectivement lu la norme du SQL, et j'avais essayé de créer une clé étrangère qui pointe vers un index unique. Mais, sous MySql, ça ne fonctionne pas. Aussi je n'étais pas certain d'avoir bien compris.

A+

[SQLpro]

La norme SQL dit qu'une clé étrangère doit porter sur un INDEX UNIQUE. C'est le cas d'une clé primaire, mais pas forcément.

J'ai un doute quand à la possibilité de nullité de l'index référencé (je pense qu'il ne peut pas être nullable, donc une simple contrainte d'unicité n'est pas suffisante, si je ne m'abuse -et de toute façon, c'est pas assez performant-).

Avec SQL Server par exemple, il est tout à fait possible d'utiliser un index unique pour une FK. A noter aussi qu'une clé étrangère peut être composite (c'est à dire plusieurs colonnes).

Ce que vous dites est faux ! Bien que cela fonctionne...

Une clef étrangère est une contrainte qui réfère une valeur de clef d'une autre table de façon à avoir la certitude que cette référence est unique, donc clef d'entité ou clef subrogée (ou alternative).
Elle peut donc être greffée sur une contraintes de clef primaire comme sur une contrainte d'unicité.
Ceci n'ayant rien à voir avec les index qui sont inconnus de la norme SQL !
Même si derrière une clef primaire ou une contrainte d'unicité, les SGBDR créent systématiquement un index.

A +

[StringBuilder]

Hmmm, en effet, pas bien réveillé, gueule de bois tout ça...

J'ai un peu oublié qu'on pouvait mettre "NOT NULL" sur une colonne

Donc oui, une contrainte d'unicité est suffisante, en revanche, elle doit porter, si je ne m'abuse, sur un tuple de colonnes qui sont toutes "NOT NULL".
=> Sinon, on ne saurait pas si la valeur NULL fait référence à la table de référence, ou si c'est qu'il n'y a pas de donnée.

L'avantage de l'index unique par rapport à la contrainte d'unicité (sous SQL Server tout du moins), c'est qu'il interdit les NULL, d'où mon raccourci d'alcoolique...

[Waldar]

L'avantage de l'index unique par rapport à la contrainte d'unicité (sous SQL Server tout du moins), c'est qu'il interdit les NULL, d'où mon raccourci d'alcoolique...

Vous n'êtes pas encore complètement bien réveillé alors, la contrainte d'unicité n'interdit en rien les nulls.

[SQLpro]

Hmmm, en effet, pas bien réveillé, gueule de bois tout ça...

J'ai un peu oublié qu'on pouvait mettre "NOT NULL" sur une colonne

Donc oui, une contrainte d'unicité est suffisante, en revanche, elle doit porter, si je ne m'abuse, sur un tuple de colonnes qui sont toutes "NOT NULL".
=> Sinon, on ne saurait pas si la valeur NULL fait référence à la table de référence, ou si c'est qu'il n'y a pas de donnée

Et si !!!!

vraiment 2012 commence mal pour toi !!!!!

;-)

A +

[StringBuilder]

Vous n'êtes pas encore complètement bien réveillé alors, la contrainte d'unicité n'interdit en rien les nulls.

Ben... C'est bien ce que j'ai écrit...

UNIQUE CONSTRAINT : Accepte les valeurs nulles
UNIQUE INDEX : Refuse les valeurs nulles
Edit : En effet, c'est que la PK qui interdit les NULL

Edit :

Hmmm, donc une FK peut faire référence à une colonne nullable ?

Les bras m'en tombent. D'un point de vue sémantique, je trouve ça absurde.

Ca veut dire que dans une table, si j'ai une valeur pour une FK qui est nulle, je ne sais pas :
- Si c'est que j'ai pas de parent
- Ou si j'ai un parent, je ne sais pas lequel parmis les NULL dans la table de référence

(???)

Testé, et en effet, je suis sur mon derrière.

[punkoff]

Bonjour,

le problème c'est que null n'est pas une valeur, mais plutot un état ou l'abscence d'une valeur. http://sqlpro.developpez.com/cours/null/


Donc dans le cas d'une FK, ca ne référence rien du tout.

[StringBuilder]

Bonjour,

le problème c'est que null n'est pas une valeur, mais plutot un état ou l'abscence d'une valeur. http://sqlpro.developpez.com/cours/null/


Donc dans le cas d'une FK, ca ne référence rien du tout.

Ca, je suis d'accord pour la table fille.

Mais pour la table de référence, je suis très surpris qu'il puisse y avoir des valeurs nulles. Mais en effet, j'ai testé et ça marche sous SQL Server 2008 R2.

En tout cas, je suis pas près de le mettre en pratique !

[CinePhil]

Pour revenir à la question posée par cette discussion, contentons-nous peut-être de répondre que la bonne pratique est que oui, une clé étrangère doit référencer une clé primaire.

Le petit bémol est que cette clé étrangère peut par contre être une colonne nullable puisque il existe ON DELETE SET NULL.
Mais ça non plus c'est un truc que je n'aime pas et que ma rigueur m'interdit.

[StringBuilder]

Le petit bémol est que cette clé étrangère peut par contre être une colonne nullable puisque il existe ON DELETE SET NULL.

Ça, c'est dans l'autre sens (et ça ne me choque pas plus que ça) :
- Lorsqu'on supprimer une donnée dans la table de référence, on a le choix entre supprimer les données filles (ON DELETE CASCADE) ou de les rendre orphelines (ON DELETE SET NULL).
=> Moi, ce qui me chagrine, c'est que dans la table de référence, il puisse y avoir des NULL (et donc perte de l'unicité, et confusion possible lorsque les données filles font référence à NULL : sont-elles orphelines, ou référencent-elle les lignes à NULL dans la table de référence ?)

[tatayo]

Pour moi, vu que NULL n'est pas une valeur, les lignes en question sont orphelines. Les lignes de la table fillesdont la FK est null ne font référence à aucune ligne de la table mère.

Tatayo.

[CinePhil]

=> Moi, ce qui me chagrine, c'est que dans la table de référence, il puisse y avoir des NULL

On est bien d'accord !
En respectant la bonne pratique consistant à toujours faire référence à une clé primaire dans une clé étrangère, comme une clé primaire ne peut pas être à NULL, on n'aura pas non plus de clé étrangère faisant référence à NULL !

[ced]

Pour revenir à la question posée par cette discussion, contentons-nous peut-être de répondre que la bonne pratique est que oui, une clé étrangère doit référencer une clé primaire.

Le petit bémol est que cette clé étrangère peut par contre être une colonne nullable puisque il existe ON DELETE SET NULL.
Mais ça non plus c'est un truc que je n'aime pas et que ma rigueur m'interdit.

Je ne suis pas tout à fait d'accord. Une clé étrangère peut très bien référencer autre chose qu'une clé primaire (et notamment une clé alternative), du moment que la contrainte d'unicité est respectée.
Et ça n'a rien d'une mauvaise pratique.

[CinePhil]

Une clé alternative est généralement signifiante et susceptible d'être modifiée. Je sais qu'il existe ON UPDATE CASCADE mais ça non plus je n'aime pas !

Faire référence à une clé alternative rend inutile la clé primaire alors ! Selon moi, ce n'est pas de la bonne modélisation, désolé.

[ced]

Bonne modélisation ou pas (ce qui serait un autre débat...), ça répond bien à la question posée : une clé étrangère ne pointe pas "forcément" sur une clé primaire...

Le recours à la clé alternative, d'un point de vue purement pratique (et non pas théorique, sur lequel je suis d'accord avec toi), peut s'avérer utile. J'y ai eu recours sur une base de données dont les tables sont presque toutes identifiées par des clés concaténées. Impossible de changer le modèle de données sans démolir toute la machinerie applicative autour de cette base.
Du coup, la mise en place d'une clé alternative avec un identifiant numérique unique, puis le référencement de cette clé numérique, allège considérablement les tables qui pointent sur ces clés (quand la clé est la concaténation de 4 colonnes, le fait de la remplacer par une seule colonne est quand même plus léger).

Encore une fois, il faut bien distinguer "la théorie" de "la pratique"...

[Oishiiii]

Encore une fois, il faut bien distinguer "la théorie" de "la pratique"...

Dans la thèorie relationnelle la notion de "clè primaire" est en train de disparaitre.
Une clé primaire c'est une clé (dite "candidate", ou "alternative", etc..) choisie parmis d'autres. Rien de plus.

La théorie relationnelle n'impose donc pas aux clés étrangères de faire référence à une clé primaire (même si ce fût le cas plus tôt dans son histoire, comme dans l'article de Codd en 1970). L'objet de référence d'une clé étrangère c'est une clé.

Voir Clé primaire

Bien sûr on se place dans un context où NULL n'existe pas.
Les indexs non plus, cela a déjà était dit par SQLPro plus haut.

[SQLpro]

Hmmm, donc une FK peut faire référence à une colonne nullable ?

Les bras m'en tombent. D'un point de vue sémantique, je trouve ça absurde.

Ca veut dire que dans une table, si j'ai une valeur pour une FK qui est nulle, je ne sais pas :
- Si c'est que j'ai pas de parent
- Ou si j'ai un parent, je ne sais pas lequel parmis les NULL dans la table de référence

(???)

Testé, et en effet, je suis sur mon derrière.

Parce que vous n'avez pas lu mon livre....
En effet les contraintes de type FK permettent de faire du MATCH PARTIAL, SIMPLE ou FULL.
Je donne un exemple dans mon livre avec une table de date composée de 3 colonnes AN, MOIS, JOUR. Historiquement, il existe des événement dont on ne connais pas la date certaine. Dans ce genre de table de datation on ajoute des année sans mois ni jour et des année + mois sans jour, afin de dater par exemple un événement survenu en août 1238...

Voilà un exemple typique de référence partielle.

Dans l'industrie vous en trouverez de nombreux. Par exemple dans la grande production (prenons les voitures par exemple) on à des références majeures, mineures, des options, sous options... Tout ce qui constitue une référence précise. Mais certains pièces peuvent être montée sur toute une gamme de véhicule et d'autres plus spécifiques sur un modèle précis avec des options particulières...

Si vous n'avez pas mon livre, lisez le paragraphe 5.2.2 de l'article que j'ai écrit sur les contraintes : http://sqlpro.developpez.com/contrai...aintes_SQL.pdf

Bref, il vous reste beaucoup à apprendre visiblement !

A +

[WinNew]

Hmmm, en effet, pas bien réveillé, gueule de bois tout ça...

J'ai un peu oublié qu'on pouvait mettre "NOT NULL" sur une colonne

Donc oui, une contrainte d'unicité est suffisante, en revanche, elle doit porter, si je ne m'abuse, sur un tuple de colonnes qui sont toutes "NOT NULL".
=> Sinon, on ne saurait pas si la valeur NULL fait référence à la table de référence, ou si c'est qu'il n'y a pas de donnée.

L'avantage de l'index unique par rapport à la contrainte d'unicité (sous SQL Server tout du moins), c'est qu'il interdit les NULL, d'où mon raccourci d'alcoolique...

Oui, tu as raison. Je me suis embrouillé. Voici des exemples vérifiés :

Exemple qui fonctionne

Une jointure de A(FK) vers B(PK)
tableA.fkb pointe vers une clé *PRIMAIRE* (tableB.id).

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
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS tableB;
DROP TABLE IF EXISTS tableA;
SET foreign_key_checks = 1;
 
CREATE TABLE tableB ( id INT NOT NULL,
                      otherField INT NOT NULL,
                      PRIMARY KEY(id))
                      ENGINE=INNODB;
 
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(id),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;

Exemple qui fonctionne

tableA.fkb pointe vers un un *index unique* (tableB.otherField).

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
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS tableB;
DROP TABLE IF EXISTS tableA;
SET foreign_key_checks = 1;
 
CREATE TABLE tableB ( id INT NOT NULL,
                      otherField INT NOT NULL,
                      UNIQUE INDEX idx (otherField),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;
 
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(otherField),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;

Exemple qui fonctionne

tableA.fkb pointe vers un un *index PAS unique* (tableB.otherField).

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
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS tableB;
DROP TABLE IF EXISTS tableA;
SET foreign_key_checks = 1;
 
CREATE TABLE tableB ( id INT NOT NULL,
                      otherField INT NOT NULL,
                      INDEX idx (otherField),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;
 
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(otherField),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;

Exemple qui fonctionne

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
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS tableB; 
DROP TABLE IF EXISTS tableA;
SET foreign_key_checks = 1;
 
CREATE TABLE tableB ( id1 INT NOT NULL,
                      id2 INT NOT NULL,
                      otherField INT NOT NULL,
 
                      PRIMARY KEY(id1, id2))
                      ENGINE=INNODB; 
 
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb1 INT,
                      fkb2 INT,
                      FOREIGN KEY (fkb1, fkb2) REFERENCES tableB(id1, id2),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;

Exemple qui fonctionne

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
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS tableB;
DROP TABLE IF EXISTS tableA;
SET foreign_key_checks = 1;
 
CREATE TABLE tableB ( id1 INT NOT NULL,
                      id2 INT NOT NULL,
                      otherField INT NOT NULL,
                      UNIQUE INDEX idx (otherField),
                      PRIMARY KEY(id1, id2))
                      ENGINE=INNODB;
 
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(id1),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;

Ne fonctionne pas ???

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
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS tableB;
DROP TABLE IF EXISTS tableA;
SET foreign_key_checks = 1;
 
CREATE TABLE tableB ( id1 INT NOT NULL,
                      id2 INT NOT NULL,
                      otherField INT NOT NULL,
                      UNIQUE INDEX idx (otherField),
                      PRIMARY KEY(id1, id2))
                      ENGINE=INNODB;
 
CREATE TABLE tableA ( id INT NOT NULL,
                      fkb INT,
                      FOREIGN KEY (fkb) REFERENCES tableB(id2),
                      PRIMARY KEY(id))
                      ENGINE=INNODB;
 
mysql> insert into tableB set id1=1, id2=1, otherfield=1;
Query OK, 1 row affected (0.11 sec)
 
mysql> insert into tableB set id1=2, id2=1, otherfield=1;
Query OK, 1 row affected (0.08 sec)
 
mysql> insert into tableB set id1=2, id2=2, otherfield=1;
Query OK, 1 row affected (0.06 sec)
 
=> id1 n'est pas unique.
=> id2 n'est pas unique.
 
mysql> insert into tableB set id1=2, id2=2, otherfield=1;
ERROR 1062 (23000): Duplicate entry '2-2' for key 'PRIMARY'
 
=> (id1, id2) est unique.

Liste les contraintes d'unicité :

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
    SELECT   b.column_name, b.constraint_name
    FROM     information_schema.table_constraints a
    JOIN     information_schema.key_column_usage b
    ON       a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name
    WHERE    a.table_schema='mydb'
    AND      a.constraint_type='UNIQUE'
    AND      b.table_name='TableB'
    ORDER    BY b.table_name, b.constraint_name;
 
+-------------+-----------------+
| column_name | constraint_name |
+-------------+-----------------+
| otherField  | idx             |
+-------------+-----------------+
 
    SELECT   b.table_name, b.column_name, b.constraint_name
    FROM     information_schema.table_constraints a
    JOIN     information_schema.key_column_usage b
    ON       a.table_schema = b.table_schema
    AND      a.constraint_name = b.constraint_name
    AND      a.table_name = b.table_name
    WHERE    a.table_schema='mydb'
    AND      a.constraint_type='PRIMARY KEY'
    AND      b.table_name='TableB'
    ORDER    BY b.table_name, b.constraint_name;   
 
+------------+-------------+-----------------+
| table_name | column_name | constraint_name |
+------------+-------------+-----------------+
| tableb     | id1         | PRIMARY         |
| tableb     | id2         | PRIMARY         |
+------------+-------------+-----------------+

A+