Discussion :

[L'AMI Yves]

Bonjour/bonsoir à tous.

Voici la définition de ma table ("Export" de phpmyadmin) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE `Ventilations` (
  `Numero` tinyint(3) UNSIGNED NOT NULL 
  `Ecriture` mediumint(9) UNSIGNED NOT NULL 
  `Compte` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `Sous_Compte` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `Tiers` tinytext COLLATE utf8_bin,
  `Debit` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Credit` decimal(10,2) DEFAULT NULL,
  `Commentaire` tinytext COLLATE utf8_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

On ne voit pas que l'ensemble des colonnes "Numero" et "Ecriture" est défini comme clé primaire de la table, ce qui veut dire (j'expère) qu'il peut y avoir plusieurs "Numero" identiques dans la table, mais pas plusieurs paires "Numero Ecriture".

Les colonnes "Compte" et "Sous_Compte" fonctionnent selon le même principe, et j'ai pu leur adjoindre une contrainte externe : Compte doit exister dans la table "Comptes", et le couple "Compte, Sous_Compte" dans la table "Sous_Comptes" (je ne sais pas comment visualiser cela dans phpmyadmin).

Mon problème est que je n'arrive pas à faire la même chose avec "Numero, Ecriture", sachant que Numero n'existe que dans la table Ventilations, tandis qu'Ecriture est défini comme "Mediumint(9) dans la table Ecritures (dont il est la clé).

Qu'est-ce qui cloche ?

Voici quelques informations sur la configuration sur laquelle je tourne :

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
 
 Serveur de base de données
   Serveur : Localhost via UNIX socket
    Type de serveur : MySQL
    Version du serveur : 5.7.25-0ubuntu0.18.04.2 - (Ubuntu)
    Version du protocole : 10
    Utilisateur : yves@localhost
    Jeu de caractères du serveur : UTF-8 Unicode (utf8)
 
Serveur web
 
    Apache/2.4.29 (Ubuntu)
    Version du client de base de données : libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $
    Extension PHP : mysqliDocumentation curlDocumentation mbstringDocumentation
    Version de PHP : 7.2.15-0ubuntu0.18.04.2
 
phpMyAdmin
 
    Version : 4.6.6deb5
    Documentation
    Site officiel
    Contribuer
    Obtenir de l'aide
    Liste des changements
    Licence

Merci de vos lumières.

AMIcalement.

[Artemus24]

Salut l'AMI Yves.

Vous avez raison. Le couple (Numéro ; Ecriture) doit être unique.
Mais vous pouvez avoir plusieurs Numéros identiques ou encore plusieurs Ecritures identiques.

Pour traduire cela sous forme d'une clef primaire, faites :

Code :

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

PRIMARY KEY(Numero, Ecriture)

Je conseille aussi de mettre "NOT NULL" sur chacune de vos colonnes de la clef primaire.

Non, Compte et Sous-compte ne fonctionnent pas comme Numero et Ecriture, pour la simple raison qu'ils forment votre clef primaire.
Pour la contrainte de la clef primaire, voici un exemple à déclarer dans votre table :

Code :

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

CONSTRAINT `FK_01` FOREIGN KEY (`col_ecriture`)  REFERENCES `table_ecriture` (`col_ecriture`) ON DELETE CASCADE ON UPDATE CASCADE

Quand on déclare une clef étrangère (foreign key) vous devez appliquer quelques conditions au prélable :

1) le type utilisé par la colonne et celle qui est en référence doivent être les mêmes.

2) la valeur doit exister au préalable dans la table référence avant de l'insérer dans la table où se trouve votre clef étrangère.

Voici un exemple :

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop table if exists `ref_1`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `ref_1`
( `col_1`    integer unsigned NOT NULL primary key,
  `val_1`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `ref_1` (`col_1`, `val_1`) VALUES
  (1,'un'),(2,'deux')
--------------
 
--------------
select * from `ref_1`
--------------
 
+-------+-------+
| col_1 | val_1 |
+-------+-------+
|     1 | un    |
|     2 | deux  |
+-------+-------+
--------------
drop table if exists `ref_2`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `ref_2`
( `col_2`    smallint unsigned NOT NULL primary key,
  `mes_2`    varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `ref_2` (`col_2`, `mes_2`) VALUES
  (3,'trois'),(4,'quatre')
--------------
 
--------------
select * from `ref_2`
--------------
 
+-------+--------+
| col_2 | mes_2  |
+-------+--------+
|     3 | trois  |
|     4 | quatre |
+-------+--------+
--------------
drop table if exists `ref_3`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `ref_3`
( `col_3`    tinyint unsigned NOT NULL primary key,
  `inf_3`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `ref_3` (`col_3`, `inf_3`) VALUES
  (5,'cinq'),(6,'six')
--------------
 
--------------
select * from `ref_3`
--------------
 
+-------+-------+
| col_3 | inf_3 |
+-------+-------+
|     5 | cinq  |
|     6 | six   |
+-------+-------+
--------------
drop table if exists `ref_4`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `ref_4`
( `col_4`    bigint unsigned NOT NULL primary key,
  `lib_4`    varchar(255)    NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `ref_4` (`col_4`, `lib_4`) VALUES
  (7,'sept'),(8,'huit')
--------------
 
--------------
select * from `ref_4`
--------------
 
+-------+-------+
| col_4 | lib_4 |
+-------+-------+
|     7 | sept  |
|     8 | huit  |
+-------+-------+
--------------
drop table if exists `test`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `test`
( `col_1`    integer  unsigned NOT NULL,
  `col_2`    smallint unsigned NOT NULL,
  `col_3`    tinyint  unsigned NOT NULL,
  `col_4`    bigint   unsigned NOT NULL,
  primary key (`col_1`,`col_2`),
  CONSTRAINT `FK_01` FOREIGN KEY (`col_1`) REFERENCES `ref_1` (`col_1`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_02` FOREIGN KEY (`col_2`) REFERENCES `ref_2` (`col_2`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_03` FOREIGN KEY (`col_3`) REFERENCES `ref_3` (`col_3`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_04` FOREIGN KEY (`col_4`) REFERENCES `ref_4` (`col_4`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`col_1`,`col_2`,`col_3`,`col_4`) VALUES
  (1,3,5,7),
  (2,4,6,8)
--------------
 
--------------
INSERT INTO `test` (`col_1`,`col_2`,`col_3`,`col_4`) VALUES
  (1,1,1,1)
--------------
 
ERROR 1452 (23000) at line 147: Cannot add or update a child row: a foreign key constraint fails (`base`.`test`, CONSTRAINT `FK_02` FOREIGN KEY (`col_2`) REFERENCES `ref_2` (`col_2`) ON DELETE CASCADE ON UPDATE CASCADE)
--------------
select * from `test`
--------------
 
+-------+-------+-------+-------+
| col_1 | col_2 | col_3 | col_4 |
+-------+-------+-------+-------+
|     1 |     3 |     5 |     7 |
|     2 |     4 |     6 |     8 |
+-------+-------+-------+-------+
--------------
select      t2.col_1,
            t2.val_1,
            t3.col_2,
            t3.mes_2,
            t4.col_3,
            t4.inf_3,
            t5.col_4,
            t5.lib_4
 
      from  `test` as t1
 
inner join  `ref_1` as t2
        on  t2.col_1 = t1.col_1
 
inner join  `ref_2` as t3
        on  t3.col_2 = t1.col_2
 
inner join  `ref_3` as t4
        on  t4.col_3 = t1.col_3
 
inner join  `ref_4` as t5
        on  t5.col_4 = t1.col_4
--------------
 
+-------+-------+-------+--------+-------+-------+-------+-------+
| col_1 | val_1 | col_2 | mes_2  | col_3 | inf_3 | col_4 | lib_4 |
+-------+-------+-------+--------+-------+-------+-------+-------+
|     1 | un    |     3 | trois  |     5 | cinq  |     7 | sept  |
|     2 | deux  |     4 | quatre |     6 | six   |     8 | huit  |
+-------+-------+-------+--------+-------+-------+-------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Volontairement, j'ai inséré dans la table test des valeurs qui n'existent pas dans les tables références.

@+

[L'AMI Yves]

Merci, Arthemus, toujours présent, toujours pertinent !

Mais tout ça, je le savais. J'ai repensé à mon problème cette nuit, et j'ai réfléchi à une première erreur - pourtant signalée "en filigrane" dans mon post : dans ma table "Ventilations", la colonne "Ecriture" fait bien référence à une autre table, et il est donc bien pertinent d'y mettre une contrainte, mais "Numero" est propre à la table : sa seule contrainte est donc d'être unique "par Ecriture", ce qui s'obtient automatiquement, puisque la paire est clé primaire.

Ce matin, j'ai donc tenté d'appliquer ma contrainte à la seule "Ecriture... et ça n'a pas marché !

En y regardant de plus près, "Numero" dans la tables "Ecritures" n'était pas déclaré "UNSIGNED"

Une fois cela corrigé, ça marche.

Problème résolu.

Encore merci.

AMIcalement.

[Artemus24]

Salut l'AMI Yves.

mais "Numero" est propre à la table : sa seule contrainte est donc d'être unique "par Ecriture", ce qui s'obtient automatiquement, puisque la paire est clé primaire.

Que le couple (Numero ; Ecriture) soit unique, je veux bien puisque ce couple forme votre clef primaire.
Si Ecriture est multiple dans la clef primaire, la seule façon de rendre votre clef primaire unique est de l'associer, je suppose comme vous faites, à un Numero qui va les différencier.
Dans ce cas, pourquoi créer une clef primaire regroupant la colonne Ecriture, et la colonne Numero, si je comprends bien, qui ne sert à rien ?

Je vous propose de faire ainsi :

1) une clef primaire technique :

Code :

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

`id`  integer unsigned not null auto_increment primary key,

Je suppose qu'elle a juste le rôle de rendre unique chaque ligne de votre table.
Cela simplifie grandement sa gestion, puisqu'elle sera automatique.
Mais doit-elle aussi servir à quelque chose d'autre ?

Que faites-vous de votre clef primaire ?
Faites-vous des recherche ?
Ou juste rendre unique votre ligne ?

2) de supprimer la colonne Numero et de conserver la clef étrangère Ecriture, en tant que telle, sans l'associer à votre clef primaire.
C'est par cette colonne que vous retrouverez toutes les lignes ayant une certaine valeur à Ecriture.

Ainsi la clef primaire joue le rôle d'unicité, comme elle se doit.
La colonne Ecriture sera une clef étrangère pointant sur votre table de référence.

@+