clef primaire composé de clef secondaires dans une table associative ?

**sudtek** · 03/05/2012, 20h11

Bonjour à tous,
Dans le cadre de la découverte du SQL et mysql j’ai fait une petite base dont le thème pratique est une bibliothèque de livre au sein d’un CE. Ce dernier permet à ses employés d’emprunter des livres. Pour savoir qui emprunte quoi au sein de la bibli j’ai fait une petit étude des besoins et un MLD en fonction de règles arbitraires que j’ai fixé.
Pour m’aider après le balayage des donnés j’ai fait le MLD sous windev ,mais pb ce dernier gommant les difficultés certaines choses reste floue quand je dois les réaliser à la mano sous mySQL et tout particulièrement pour les tables associatives :

sous mysql j’ai créé les tables principales + une liaison simple nommée appartenir entre les tables editeur (1,n)← appartenir →(1,1) livre (j’utilise le code 04.sql) . dans la table livre (coté 1,1) j’ai créé une colonne IDediteur de type identique à celle contenue dans la table editeur et j’ai importé la clef étrangére qui apparaît avec le suffixe MUL :

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
/*mysql> describe livre;
+----------------+----------------------+------+-----+---------+----------------
+
| Field          | Type                 | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+----------------
+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment
|
| isbn13         | smallint(5) unsigned | NO   |     | NULL    |
|
| titre          | varchar(45)          | NO   |     | NULL    |
|
| langue         | char(2)              | NO   |     | fr      |
|
| nb_page        | smallint(5) unsigned | NO   |     | NULL    |
|
| annee_parution | year(4)              | NO   |     | NULL    |
|
| resume         | text                 | YES  |     | NULL    |
|
| IDediteur      | smallint(5) unsigned | NO   | MUL | NULL    |
|
+----------------+----------------------+------+-----+---------+----------------
+
8 rows in set (0.01 sec)*/

si j’essaye de supprimer la table editeur j’obtiens un magnifique :”ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrain fails” ce qui tend à prouver que les tables sont liés par une relation .

Mon PB : dans le cadre de la céation d’une table associative nommé livre_contributeur qui fait le lien entre la table livre & contributeur des choses m'échappent et j’espere que vous pourrez m’éclairer :
La relation de ces 3 tables étant :
contributeur(1,N) <--> (1,1)livre_contributeur(1,1)<-->livre(1,N) la cardinalité (1,1) etant sur la table associative c’est là il me semble que je dois importer les clef secondaires du coup j’ai procédé comme suit :

Dans le doute j’ai essayé des variantes :

La 05a.sql
J’ai créé la table livre_contributeur avec idlivre et idcontributeur en PK :

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
/* on cré la table livre_contributeur entre les tables livre & contributeur*/
CREATE TABLE IF NOT EXISTS livre_contributeur(
	IDlivre SMALLINT UNSIGNED NOT NULL,
	IDcontributeur SMALLINT UNSIGNED NOT NULL,
	PRIMARY KEY (IDlivre,IDcontributeur)
)
ENGINE=INNODB; /* On fixe le moteur de base de donné INNODB permet de gerer les clefs etrangéres */
 
/*mysql> describe livre_contributeur
    -> ;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.07 sec)*/

J’importe les clefs PK des tables livre & editeur :

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
/*Sur la table livre_contributeur cardianlité (1,1) on importe les clefs etrangeres */
/*contributeur(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_1 FOREIGN KEY (IDcontributeur) REFERENCES contributeur(IDcontributeur);
 
/*livre(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_2 FOREIGN KEY (IDlivre) REFERENCES livre(IDlivre);
 
/*mysql> describe livre_contributeur;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)*/

Si j’essaye d'effacer la table livre_contributeur elle s’efface sans me signaler un pb de clef secondaires → j’en conclue que c’est pas la bonne méthode...

variante 05b.sql :
J’ai créé la table livre_contributeur avec idlivre et idcontributeur sans les déclarer PK :

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
 
/* ------------------ CREATION DES TABLES ASSOCIATIVES(s) V05b ------------------ */
 
/* on cré la table livre_contributeur entre les tables livre & contributeur*/
CREATE TABLE IF NOT EXISTS livre_contributeur(
	IDlivre SMALLINT UNSIGNED NOT NULL,
	IDcontributeur SMALLINT UNSIGNED NOT NULL
)
ENGINE=INNODB; /* On fixe le moteur de base de donné INNODB permet de gerer les clefs etrangéres */
 
/*mysql> describe livre_contributeur
    -> ;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   |     | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   |     | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.07 sec)*/

J’importe les clefs PK des tables livre & editeur :

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
 
/*Sur la table livre_contributeur cardianlité (1,1) on importe les clefs etrangeres */
/*contributeur(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_1 FOREIGN KEY (IDcontributeur) REFERENCES contributeur(IDcontributeur);
 
/*livre(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_2 FOREIGN KEY (IDlivre) REFERENCES livre(IDlivre);
 
/*mysql> describe livre_contributeur;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   | MUL | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   | MUL | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)*/

Je déclare une PK constitué de IDlivre & IDcontributeur :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
/*On ajoute une clef primaire sur la table livre_contributeur composé de livre_contributeur.IDlivre & livre_contributeur.IDcontributeur*/
 
ALTER TABLE livre_contributeur ADD PRIMARY KEY (IDlivre,IDcontributeur);
 
/*mysql> describe livre_contributeur;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)*/

J’en conclue que c’est sûrement pas la bonne méthode car les clefs MUL sont devenues PRI → j’ai écrasé mon boulot de l'étape 2.... et je peux dropper la table livre_contributeur .

variante 05c.sql :

J’ai créé la table livre_contributeur avec IDlivre_contributeur, idlivre et idcontributeur sans les déclarer PK :

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
 
/* ------------------ CREATION DES TABLES ASSOCIATIVES(s) V05c ------------------ */
 
/* on cré la table livre_contributeur entre les tables livre & contributeur*/
CREATE TABLE IF NOT EXISTS livre_contributeur(
	IDlivre_contributeur SMALLINT UNSIGNED NOT NULL,
	IDlivre SMALLINT UNSIGNED NOT NULL,
	IDcontributeur SMALLINT UNSIGNED NOT NULL
)
ENGINE=INNODB; /* On fixe le moteur de base de donné INNODB permet de gerer les clefs etrangéres */
 
/* mysql> describe livre_contributeur;
+----------------------+----------------------+------+-----+---------+-------+
| Field                | Type                 | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------+-------+
| IDlivre_contributeur | smallint(5) unsigned | NO   |     | NULL    |       |
| IDlivre              | smallint(5) unsigned | NO   |     | NULL    |       |
| IDcontributeur       | smallint(5) unsigned | NO   |     | NULL    |       |
+----------------------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

J’importe les clefs étrangères de IDlivre & IDcontributeur :

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
 
/*Sur la table livre_contributeur cardianlité (1,1) on importe les clefs etrangeres */
/*contributeur(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_1 FOREIGN KEY (IDcontributeur) REFERENCES contributeur(IDcontributeur);
 
/*livre(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_2 FOREIGN KEY (IDlivre) REFERENCES livre(IDlivre);
 
/*mysql> describe livre_contributeur;
+----------------------+----------------------+------+-----+---------+-------+
| Field                | Type                 | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------+-------+
| IDlivre_contributeur | smallint(5) unsigned | NO   |     | NULL    |       |
| IDlivre              | smallint(5) unsigned | NO   | MUL | NULL    |       |
| IDcontributeur       | smallint(5) unsigned | NO   | MUL | NULL    |       |
+----------------------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Je créé une cléf primaire composé des 2 clefs secondaires :

Je ne trouve pas d’explication comment faire de IDlivre_contributeur une PK composé de IDlivre + IDcontributeur !!!

in fine je suis loin de mon MLD windev …bien sur windev n’est pas mysql mais pourriez vous me guider vers la bonne piste pour faire une table de liaison composé mes de 2 clef secondaires ?

cordialement sudtek

PS : Ci-joint un lien pour consulter mon dossier complet avec la procédure, détails des tables, le code sql …

https://docs.google.com/document/d/1...hZpT8xUW8/edit

**CinePhil** · 04/05/2012, 10h51

si j’essaye de supprimer la table editeur j’obtiens un magnifique :”ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrain fails” ce qui tend à prouver que les tables sont liés par une relation .

Oui, c'est parce que c'est la table que tu essaies de supprimer qui est référencée dans une autre table. La clé étrangère située dans l'autre table deviendrait orphelin et le SGBD l'interdit à raison.

J’ai créé la table livre_contributeur avec idlivre et idcontributeur en PK :

C'est bien comme ça qu'il faut faire.

Si j’essaye d'effacer la table livre_contributeur elle s’efface sans me signaler un pb de clef secondaires → j’en conclue que c’est pas la bonne méthode...

La suppression est autorisée parce que tu supprimes la tables qui contient les clés étrangères, pas celles qui sont référencées par les clés étrangères.
Dans ce que tu dis, c'est ta conclusion qui est fausse.

Quelques remarques sur ton MLD...
1) Du côté de la table Employe_Livre, les cardinlités devraient être à 1,1. Une table associative est identifiée relativement aux tables qu'elle associe.

2) Comme la bonne méthode est de faire des deux clés étrangères la clé primaire d'une table associative, les colonnes IDEmploye_Livre et IDLivre_Contributeur sont inutiles.

3) Ville dans Editeur devrait être externalisé dans une table des villes et il devrait y avoir ici une clé étrangère référençant la table des villes. Cela évite d'avoir plusieurs fois la même ville avec des ortographes différentes.

4) Même si ISBN13 est entièrement composé de chiffres (l'est-il toujours ?), c'est une référence et on ne fera jamais de calcul dessus. Potentiellement, cette référence pourrait aussi être composée de chiffres. Un CHAR ou VARCHAR serait plus approprié qu'un SMALLINT.

**Maljuna Kris** · 04/05/2012, 13h03

Envoyé par CinePhil

des ortographes différentes.

Pas très orthodoxe, tout de même.

**CinePhil** · 04/05/2012, 13h05

Faute de frappe !

**sudtek** · 04/05/2012, 23h04

Bonjour Cinephil et merci pour tes informations et avoir pris le temps de m'aider,

La suppression est autorisée parce que tu supprimes la table qui contient les clés étrangères, pas celles qui sont référencées par les clés étrangères.
Dans ce que tu dis, c'est ta conclusion qui est fausse.

Ok si j'ai bien compris les tables qui sont référencées par des clefs sont «lockés» pour éviter les incohérences → éviter qu'un clef MUL soit orpheline.

1) Du côté de la table Employe_Livre, les cardinlités devraient être à 1,1. Une table associative est identifiée relativement aux tables qu'elle associe

Ce que l'on peut traduire par*:
Chaque relation employé/livre à au moins un employé → (1,1) ?
Chaque relation employé/livre à au moins un livre → (1,1) ?

2) Comme la bonne méthode est de faire des deux clés étrangères la clé primaire d'une table associative, les colonnes IDEmploye_Livre et IDLivre_Contributeur sont inutiles.

En fait je comprend mieux ce qui m'a embrouillé, c'est le MLD fait sous windev qui génère automatiquement c'est clefs composés pour une BD hyperfilesql du coup quand j'essaye de porter manuellement cela sous mysql pour voir et comprendre les rouages ce n'est pas cohérent avec les explications et méthodes que je trouve sur le net ainsi que tes explications → j'en conclue que soit la représentation graphique doit être propre au MLD de windev pour rendre le MLD non équivoque à moins que leurs hyperfiles (proprio) utilise ces clefs (je posterai dans windev pour savoir ). Un peut comme mysql workbench à qui j'ai jeté un oeil (rapide car trop compliqué pour débuter et trop graphique pour vraiment comprendre le dessous des choses ) et qui représente les cardinalités inversé (style uml), chacun soft à son style de présentation graphique la représentation mysql n'étant pas forcément reflété par les interfaces de la même façon.

Donc je supprime IDEmploye_Livre & IDEmploye_Livre et j'applique tes conseils.

Donc :

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
/* on créé la table associative livre_contributeur entre les tables livre & contributeur*/
CREATE TABLE IF NOT EXISTS livre_contributeur(
	IDlivre SMALLINT UNSIGNED NOT NULL,
	IDcontributeur SMALLINT UNSIGNED NOT NULL
)
ENGINE=INNODB; /* On fixe le moteur de base de donné INNODB permet de gerer les clefs etrangéres */
 
/*mysql> describe livre_contributeur
    -> ;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   |     | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   |     | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.07 sec)*/
 
/*Sur la table livre_contributeur cardianlité (1,1) on importe les clefs etrangeres */
/*contributeur(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_1 FOREIGN KEY (IDcontributeur) REFERENCES contributeur(IDcontributeur);
 
/*livre(1,N) --> (1,1)livre_contributeur*/
ALTER TABLE livre_contributeur ADD CONSTRAINT FK_contribuer_2 FOREIGN KEY (IDlivre) REFERENCES livre(IDlivre);
 
/*mysql> show create table livre_contributeur ;
+--------------------+----------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------+
| Table              | Create Table
 
                                                                |
+--------------------+----------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------+
| livre_contributeur | CREATE TABLE `livre_contributeur` (
  `IDlivre` smallint(5) unsigned NOT NULL,
  `IDcontributeur` smallint(5) unsigned NOT NULL,
  KEY `FK_contribuer_1` (`IDcontributeur`),
  KEY `FK_contribuer_2` (`IDlivre`),
  CONSTRAINT `FK_contribuer_1` FOREIGN KEY (`IDcontributeur`) REFERENCES `contributeur` (`IDcontributeur`),
  CONSTRAINT `FK_contribuer_2` FOREIGN KEY (`IDlivre`) REFERENCES `livre` (`IDlivre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------+----------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------+
1 row in set (0.00 sec)*/
 
/*On ajoute une clef primaire sur la table livre_contributeur composé de livre_contributeur.IDlivre & livre_contributeur.IDcontributeur*/
ALTER TABLE livre_contributeur ADD PRIMARY KEY (IDlivre,IDcontributeur);
 
 
/*mysql> describe livre_contributeur;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    |       |
| IDcontributeur | smallint(5) unsigned | NO   | PRI | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
 
mysql> show create table livre_contributeur ;
+--------------------+--------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------+
| Table              | Create Table
 
                                                                       |
+--------------------+--------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------+
| livre_contributeur | CREATE TABLE `livre_contributeur` (
  `IDlivre` smallint(5) unsigned NOT NULL,
  `IDcontributeur` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`IDlivre`,`IDcontributeur`),
  KEY `FK_contribuer_1` (`IDcontributeur`),
  CONSTRAINT `FK_contribuer_1` FOREIGN KEY (`IDcontributeur`) REFERENCES `contributeur` (`IDcontributeur`),
  CONSTRAINT `FK_contribuer_2` FOREIGN KEY (`IDlivre`) REFERENCES `livre` (`IDlivre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------+--------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------+
1 row in set (0.00 sec)
*/

La procédure ci-dessus te semble-telle correcte*?

3) Ville dans Editeur devrait être externalisé dans une table des villes et il devrait y avoir ici une clé étrangère référençant la table des villes. Cela évite d'avoir plusieurs fois la même ville avec des orthographes différentes.

Ok je comprends c'est logique pas besoin de stoker paris ou Paris ...ou tout autres forme polymorphe n fois cela n'apporte rien.

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
65
66
67
68
 
/* ----------- TABLE editeur ----------
on cré la relation intitulée 'avoir' entre les tables editeur & ville
On desire ajouter la clef etrangere en provenance de la 
table 'ville' (O,N) sur la table 'editeur' coté (1,1) */
/*mysql> describe editeur;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| IDediteur      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| raison_sociale | varchar(45)          | NO   | UNI | NULL    |                |
| adresse        | varchar(45)          | YES  |     | NULL    |                |
| code_postal    | char(4)              | YES  |     | NULL    |                |
| telephone      | varchar(16)          | YES  |     | NULL    |                |
| email          | varchar(45)          | YES  |     | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)
*/
 
/*==============================================================================================================*/
/* On ajoute / cré la colonne 'IDville' dans la table intitulé 'editeur' */
/* On fixe les mêmes types que la clef étrangére pour garder la cohésion */
/*==============================================================================================================*/
ALTER TABLE editeur ADD IDville SMALLINT UNSIGNED NOT NULL;
 
/*mysql> describe editeur;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| IDediteur      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| raison_sociale | varchar(45)          | NO   | UNI | NULL    |                |
| adresse        | varchar(45)          | YES  |     | NULL    |                |
| code_postal    | char(4)              | YES  |     | NULL    |                |
| IDville        | smallint(5) unsigned | NO   |     | NULL    |                |
| telephone      | varchar(16)          | YES  |     | NULL    |                |
| email          | varchar(45)          | YES  |     | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
*/
 
/*==============================================================================================================*/
/* On ajout la clef etrangere de la table 'ville' colonne 'IDville' à la table 'editeur' colonne 'IDville'  */
/* ALTER TABLE 'table_cible_où_on_ajoute_a clef_secondaire' ADD CONSTRAINT 'nom_de_la_FK_contrainte' -->		*/
/* FOREIGN KEY 'nom_colonne_CIBLE' REFERENCES 'table_source(colonne_source);									*/
/*==============================================================================================================*/
ALTER TABLE editeur ADD CONSTRAINT FK_avoir FOREIGN KEY (IDville) REFERENCES ville(IDville);
 
/*mysql> describe editeur;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| IDediteur      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| raison_sociale | varchar(45)          | NO   | UNI | NULL    |                |
| adresse        | varchar(45)          | YES  |     | NULL    |                |
| code_postal    | char(4)              | YES  |     | NULL    |                |
| telephone      | varchar(16)          | YES  |     | NULL    |                |
| email          | varchar(45)          | YES  |     | NULL    |                |
| IDville        | smallint(5) unsigned | NO   | MUL | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)*/
 
/*note perso  si on a cette erreur c'est que l'une des colonnes clef remplie et pas l’autre --> il n'y a rien en face pour la correspondance la remplir et ca roule
Moralité faire les clefs quand tout est vide c'est plus simple que au fur et à mesure....
mysql> ALTER TABLE editeur ADD CONSTRAINT FK_avoir FOREIGN KEY (IDville) REFERENCES ville(IDville);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`bibliotheque`.<result 2 when explaining filename '#sql-c4c_11'>, CONSTRAINT `FK_avoir` FOREIGN KEY (`IDville`) RE
FERENCES `ville` (`IDville`))
*/

4) Même si ISBN13 est entièrement composé de chiffres (l'est-il toujours ?), c'est une référence et on ne fera jamais de calcul dessus. Potentiellement, cette référence pourrait aussi être composée de chiffres. Un CHAR ou VARCHAR serait plus approprié qu'un SMALLINT.

Oui tu as raison c'est une norme qui fluctue pas mal en fait … varchar c'est plus adapté mais bon dasn le cadre de ce test de découverte mysql le but est de découvrir mysql me familiariser et prendre de l’avance (niveau compréhension) pour les cours de BD de l'année prochaine.

Voici Le nouveau MLD :

Par contre j'ai un OVNI :

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
 
 
mysql> show create table livre;
+-------+---------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--------------------+
| Table | Create Table
 
 
                    |
+-------+---------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--------------------+
| livre | CREATE TABLE `livre` (
  `IDlivre` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `isbn13` varchar(13) NOT NULL,
  `titre` varchar(255) NOT NULL,
  `langue` char(2) NOT NULL DEFAULT 'fr',
  `nb_page` smallint(5) unsigned NOT NULL,
  `annee_parution` year(4) NOT NULL,
  `resume` text,
  `IDediteur` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`IDlivre`),
  UNIQUE KEY `isbn13` (`isbn13`),
  UNIQUE KEY `isbn13_2` (`isbn13`),
  KEY `FK_appartenir` (`IDediteur`),
  CONSTRAINT `FK_appartenir` FOREIGN KEY (`IDediteur`) REFERENCES `editeur` (`IDediteur`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

Je ne comprends pas pourquoi j'ai 2 clefs unique

Code :

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

 UNIQUE KEY `isbn13` (`isbn13`),  UNIQUE KEY `isbn13_2` (`isbn13`),

*? Ca correspond à quoi selon toi ce isbn13_2 ? Car j'ai pas 2 colonnes dans ma table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> describe livre;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| isbn13         | varchar(13)          | NO   | UNI | NULL    |                |
| titre          | varchar(255)         | NO   |     | NULL    |                |
| langue         | char(2)              | NO   |     | fr      |                |
| nb_page        | smallint(5) unsigned | NO   |     | NULL    |                |
| annee_parution | year(4)              | NO   |     | NULL    |                |
| resume         | text                 | YES  |     | NULL    |                |
| IDediteur      | smallint(5) unsigned | NO   | MUL | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

En tous cas merci pour toutes ses informations CinePhil !

Bon ben je vais attaquer la création de la table employe_livre ; est il judicieux que je créé un clef prime composé de (id_livre,id_employe,date_emprunt) pour avoir un historique des emprunts ?

Je reste ouvert à toutes suggestions.

sudtek

**CinePhil** · 04/05/2012, 23h46

Envoyé par sudtek

Ok si j'ai bien compris les tables qui sont référencées par des clefs sont «lockés» pour éviter les incohérences → éviter qu'un clef MUL soit orpheline.

Je ne saispas ce que tu appelles une "clef MUL" mais c'est ça oui.

Ce que l'on peut traduire par*:
Chaque relation employé/livre à au moins un employé → (1,1) ?
Chaque relation employé/livre à au moins un livre → (1,1) ?

Oui.
On part du MCD suivant :
Employe -0,n----emprunter----0,n- Livre

Si on veut gérer les emprunts individuellement, par exemple pour savoir quels sont ceux qui ont fait l'objet d'une relance, on peut transformer l'association Emprunter en entité associative Emprunt de cette manière :
Employe -0,n----effectuer----(1,1)- Emprunt -(1,1)----concerner----0,n- Livre

La table issue de Emprunt aura la même structure que la table issue l'association Emprunter.

En fait je comprend mieux ce qui m'a embrouillé, c'est le MLD fait sous windev qui génère automatiquement c'est clefs composés pour une BD hyperfilesql du coup quand j'essaye de porter manuellement cela sous mysql pour voir et comprendre les rouages ce n'est pas cohérent avec les explications et méthodes que je trouve sur le net ainsi que tes explications → j'en conclue que soit la représentation graphique doit être propre au MLD de windev pour rendre le MLD non équivoque à moins que leurs hyperfiles (proprio) utilise ces clefs (je posterai dans windev pour savoir ). Un peut comme mysql workbench à qui j'ai jeté un oeil (rapide car trop compliqué pour débuter et trop graphique pour vraiment comprendre le dessous des choses ) et qui représente les cardinalités inversé (style uml), chacun soft à son style de présentation graphique la représentation mysql n'étant pas forcément reflété par les interfaces de la même façon.

Si tu débutes en modélisation des données, je te conseille de ne pas sauter l'étape MCD.
Ton schéma est un mix entre le MCD (avec des cardinalités) et un MLD (avec les tables associatives et les clés étrangères) et se rapproche ainsi du schéma Entity/Relashionship de MySQL Workbench.

Pour modéliser un MCD, tu peux utiliser Open Modelsphere ou bien JMerise.

Donc je supprime IDEmploye_Livre & IDEmploye_Livre et j'applique tes conseils.

Donc :
...
La procédure ci-dessus te semble-telle correcte*?

Oui.

Ok je comprends c'est logique pas besoin de stoker paris ou Paris ...ou tout autres forme polymorphe n fois cela n'apporte rien.

Avec Paris, il y a de grande chances de la voir orthographiée de la même manière (Paris sera toujours Paris !) mais Pour Saint-Étienne par exemple... (St. Etienne, Saint Etienne...)

Par contre j'ai un OVNI :
Je ne comprends pas pourquoi j'ai 2 clefs unique

Code :

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

 UNIQUE KEY `isbn13` (`isbn13`),  UNIQUE KEY `isbn13_2` (`isbn13`),

*? Ca correspond à quoi selon toi ce isbn13_2 ? Car j'ai pas 2 colonnes dans ma table

Peut-être as-tu demandé deux fois à créer un index UNIQUE sur cette table ?
Si tu utilises Windev pour générer le modèle, peut-être sépare t-il la notion de contrainte d'unicité de l'index alors que dans MySQL une contrainte UNIQUE aboutit automatiquement à la création d'un index de type UNIQUE et que les deux se sont créés séparément ?
=> Tu peux en supprimer un sans remord !

Bon ben je vais attaquer la création de la table employe_livre ; est il judicieux que je créé un clef prime composé de (id_livre,id_employe,date_emprunt) pour avoir un historique des emprunts ?

Si tu veux l'historique et si tu autorises un employé à emprunter plusieurs fois le même livre, ce qui semble logique, tu dois en effet ajouter la date à la clé primaire.

**sudtek** · 05/05/2012, 01h10

Je ne sais pas ce que tu appelles une "clef MUL"*

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
 
mysql> describe livre;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| IDlivre        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| isbn13         | varchar(13)          | NO   | UNI | NULL    |                |
| titre          | varchar(255)         | NO   |     | NULL    |                |
| langue         | char(2)              | NO   |     | fr      |                |
| nb_page        | smallint(5) unsigned | NO   |     | NULL    |                |
| annee_parution | year(4)              | NO   |     | NULL    |                |
| resume         | text                 | YES  |     | NULL    |                |
| IDediteur      | smallint(5) unsigned | NO   | MUL | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)

J'ai repris le terme de mysql exemple dans le cas de la table livre → IDediteur | smallint(5) unsigned | NO | MUL | NULL |
Quand j'ajoute la clef secondaire à la table livre Idediteur prend la valeur MUL dans la colonne KEY
J'avoue que MUL c'est pas très parlant et j'ignore le mot exact qui se cache derrière ce MUL*?

On part du MCD suivant :
Employe (0,n)----emprunter----(0,n) Livre

Si on veut gérer les emprunts individuellement, par exemple pour savoir quels sont ceux qui ont fait l'objet d'une relance, on peut transformer l'association Emprunter en entité associative Emprunt de cette manière :
Employe (0,n)----effectuer----(1,1)- Emprunt -(1,1)----concerner----(0,n) Livre

La table issue de Emprunt aura la même structure que la table issue l'association Emprunter.

En fait on «développe / étend » la relation emprunter pour en faire une table associative! Ok c'est limpide!

Peut-être as-tu demandé deux fois à créer un index UNIQUE sur cette table ?

Oui c'est même sur dans mes innombrables tests j'ai fait cela …

Code :

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

alter table livre drop index isbn13_2;

→ c'est fait !

Si tu veux l'historique et si tu autorises un employé à emprunter plusieurs fois le même livre, ce qui semble logique, tu dois en effet ajouter la date à la clé primaire.

Oui effectivement le fait qu'il puisse emprunter plusieurs fois le même livre serait un comportement normal.

Du coup cela me donnerai ce code :

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
 
/*Créé la table associative */
CREATE TABLE IF NOT EXISTS employe_livre(
	IDemploye SMALLINT UNSIGNED NOT NULL,
	IDlivre SMALLINT UNSIGNED NOT NULL,
	date_emprunt DATE,
	date_restitution DATE,
	PRIMARY KEY (IDemploye,IDlivre,date_emprunt)
)ENGINE=INNODB;
/*livre(0,N) <-concerner-> (1,1) employe_livre (1,1)<-effectuer->(0,N)employe */
/*livre(0,N) <-concerner-> (1,1) emprunt (1,1)<-effectuer->(0,N)employe */
 
/*Sur la table employe_livre cardinalité (1,1) on importe les clefs etrangeres */
/*livre(0,N) <-concerner-> (1,1)livre_contributeur*/
ALTER TABLE employe_livre ADD CONSTRAINT FK_concerner FOREIGN KEY (IDlivre) REFERENCES livre(IDlivre);
 
/*Sur la table employe_livre cardinalité (1,1) on importe les clefs etrangeres */
/*employe(0,N) <-effectuer-> (1,1)livre_contributeur*/
ALTER TABLE employe_livre ADD CONSTRAINT FK_effectuer FOREIGN KEY (IDemploye) REFERENCES employe(IDemploye);

Si tu débutes en modélisation des données, je te conseille de ne pas sauter l'étape MCD.
Ton schéma est un mix entre le MCD (avec des cardinalités) et un MLD (avec les tables associatives et les clés étrangères) et se rapproche ainsi du schéma Entity/Relashionship de MySQL Workbench.

Je sais bien mais c'est la métode iut info … alors j'essaye de recoler les morceaux tout seul car on fait quasi direct du windev en ayant très peut de base en mcd d'ou mon idée de m'inventer mon «projet BD» pour mieux comprendre.

Pour modéliser un MCD, tu peux utiliser Open Modelsphere ou bien JMerise.

Merci pour l'info! Je vais télécharger cela car j'avoue que workbench pour faire un simple test quand on débute ca fait un poil peur...

Encore une fois merci beaucoup CinePhil

pour ton aide !

Cordialement sudtek.

clef primaire composé de clef secondaires dans une table associative ?

MySQL

Discussions similaires

Partager

Partager