Discussion :

[Dens1]

Mais que les ofc_id se succèdent ou pas, ON S'EN FOUT UNE BONNE FOIS POUR TOUTES !

aie non, pas sur la tête
Oui ça, c'était okay déjà

[Dens1]

En plus, dans le mode opératoire communiqué, il manque une étape : après "compactage" pour supprimer les "trous", il faut aller modifier la valeur du prochain incrément (auto_increment_offset), sinon on va redémarrer en laissant un nouveau trou, unique, mais encore plus important !

Pas certain de comprendre ici, car il m'avait qu'on supprime l'ancien auto_increment, ancien index. Le mieux c'est que je teste (je vais copier ma base et tester sur la copie).

[Dens1]

Enfin je testerai un jour où vraiment je m'ennuie

[escartefigue]

Certes, mais après l'opération, il faut bien à nouveau redéclarer la colonne identifiante en auto_incrément et donc positionner la valeur de départ dans le paramètre offset.
Ou alors ça signifie que après l'opération, l'affectation de l'identifiant est faite par un autre mode opératoire qui garantit l'absence de "trous"...
Reste à préciser lequel.
Dans les deux cas, le mode opératoire est incomplet.

Et bien sûr, tout ceci reste parfaitement inutile.

[Artemus24]

Salut à tous.

Exactement, cette stratégie n'a de sens que si la table vit sa vie toute seule, sans lien d'intégrité avec d'autres tables.

Non, ce n'est pas vrai car cela dépend comment a été déclaré les clef étrangères.

Voici un exemple de renumérotation d'une table mère, avec une table fille ayant une clef étrangère :

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
--------------
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 `mere`
--------------
 
--------------
CREATE TABLE `mere`
( `id`    integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `val`   varchar(10)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `mere` (`id`, `val`) VALUES
(25, 'bleu'),(75, 'rouge'),(255, 'vert'),(480, 'jaune')
--------------
 
--------------
select * from mere
--------------
 
+-----+-------+
| id  | val   |
+-----+-------+
|  25 | bleu  |
|  75 | rouge |
| 255 | vert  |
| 480 | jaune |
+-----+-------+
--------------
DROP TABLE IF EXISTS `fille`
--------------
 
--------------
CREATE TABLE `fille` (
  `id`      integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `clef`    integer unsigned NOT NULL,
  `nuance`  varchar(10)      NOT NULL,
  CONSTRAINT `fk_fille_clef` FOREIGN KEY (`clef`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `fille` (`clef`, `nuance`) VALUES
(25, 'marine'),    (75, 'carmin'),(255, 'pomme'),    (480, 'citron'),
(25, 'nuit'),      (75, 'vif'),   (255, 'émeraude'), (480, 'pâle'),
(25, 'turquoise'), (25, 'cyan'),  (255, 'olive')
--------------
 
--------------
select * from fille
--------------
 
+----+------+-----------+
| id | clef | nuance    |
+----+------+-----------+
|  1 |   25 | marine    |
|  2 |   75 | carmin    |
|  3 |  255 | pomme     |
|  4 |  480 | citron    |
|  5 |   25 | nuit      |
|  6 |   75 | vif       |
|  7 |  255 | émeraude  |
|  8 |  480 | pâle      |
|  9 |   25 | turquoise |
| 10 |   25 | cyan      |
| 11 |  255 | olive     |
+----+------+-----------+
--------------
select p.id     as 'Mère ID',
       p.val    as 'Mère VAL',
       f.id     as 'Fille ID',
       f.nuance as 'Fille NUANCE'
from       mere as p
inner join fille as f
on f.clef = p.id
--------------
 
+---------+----------+----------+--------------+
| Mère ID | Mère VAL | Fille ID | Fille NUANCE |
+---------+----------+----------+--------------+
|      25 | bleu     |        1 | marine       |
|      25 | bleu     |        5 | nuit         |
|      25 | bleu     |        9 | turquoise    |
|      25 | bleu     |       10 | cyan         |
|      75 | rouge    |        2 | carmin       |
|      75 | rouge    |        6 | vif          |
|     255 | vert     |        3 | pomme        |
|     255 | vert     |        7 | émeraude     |
|     255 | vert     |       11 | olive        |
|     480 | jaune    |        4 | citron       |
|     480 | jaune    |        8 | pâle         |
+---------+----------+----------+--------------+
--------------
update `mere`, (select @x:=0) as x set id = (@x:=@x+1)
--------------
 
--------------
select * from mere
--------------
 
+----+-------+
| id | val   |
+----+-------+
|  1 | bleu  |
|  2 | rouge |
|  3 | vert  |
|  4 | jaune |
+----+-------+
--------------
select * from fille
--------------
 
+----+------+-----------+
| id | clef | nuance    |
+----+------+-----------+
|  1 |    1 | marine    |
|  2 |    2 | carmin    |
|  3 |    3 | pomme     |
|  4 |    4 | citron    |
|  5 |    1 | nuit      |
|  6 |    2 | vif       |
|  7 |    3 | émeraude  |
|  8 |    4 | pâle      |
|  9 |    1 | turquoise |
| 10 |    1 | cyan      |
| 11 |    3 | olive     |
+----+------+-----------+
--------------
select      m.id     as 'Mère ID',
            m.val    as 'Mère VAL',
            f.id     as 'Fille ID',
            f.nuance as 'Fille NUANCE'
      from  mere  as m
inner join  fille as f
        on  f.clef = m.id
--------------
 
+---------+----------+----------+--------------+
| Mère ID | Mère VAL | Fille ID | Fille NUANCE |
+---------+----------+----------+--------------+
|       1 | bleu     |        1 | marine       |
|       1 | bleu     |        5 | nuit         |
|       1 | bleu     |        9 | turquoise    |
|       1 | bleu     |       10 | cyan         |
|       2 | rouge    |        2 | carmin       |
|       2 | rouge    |        6 | vif          |
|       3 | vert     |        3 | pomme        |
|       3 | vert     |        7 | émeraude     |
|       3 | vert     |       11 | olive        |
|       4 | jaune    |        4 | citron       |
|       4 | jaune    |        8 | pâle         |
+---------+----------+----------+--------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

En plus, dans le mode opératoire communiqué, il manque une étape : après "compactage" pour supprimer les "trous", il faut aller modifier la valeur du prochain incrément (auto_increment_offset), sinon on va redémarrer en laissant un nouveau trou, unique, mais encore plus important !

Non, car en détruisant la colonne "id" et en la recréant, comme dans mon exemple ci-dessus, l'auto incrément aura la dernière valeur +1 de la table.

Mais que les ofc_id se succèdent ou pas, ON S'EN FOUT UNE BONNE FOIS POUR TOUTES !

Je suis d'accord car la colonne "id" est une colonne technique et n'a pas d'autre fonction que de servir comme lien pour les clefs étrangères.

@+

[escartefigue]

Non, ce n'est pas vrai car cela dépend comment a été déclaré les clef étrangères.

Sauf que ce mode opératoire ne correspond pas à ce qui est proposé : vous n'avez pas détruit la PK, donc l'update cascade reste applicable !
Dans le mode opératoire proposé on supprime la PK et par la même l'option CASCADE

[CinePhil]

Dans le même ordre d'idées, quand une maison est détruite dans une rue, est-ce qu'on renumérote toutes les maisons de la rue ?

Excellente comparaison !

En plus, dans le mode opératoire communiqué, il manque une étape : après "compactage" pour supprimer les "trous", il faut aller modifier la valeur du prochain incrément (auto_increment_offset), sinon on va redémarrer en laissant un nouveau trou, unique, mais encore plus important !

De mémoire, en fait, non. Puisqu'on supprime le premier auto-incrément pour en créer un nouveau dans une autre colonne, le second redémarre à 1.

Par contre, on peut ajouter que si on fait une insertion en masse (avec un LOAD DATA INFILE par exemple), MySQL/MariaDB peut lui même créer un trou dans la série d'auto-incrément. Je ne sais plus pourquoi mais je l'ai déjà constaté. Donc bidouiller la suite des auto_increment ne sert à rien !

[escartefigue]

De mémoire, en fait, non. Puisqu'on supprime le premier auto-incrément pour en créer un nouveau dans une autre colonne, le second redémarre à 1.

Exact : car le mode op renomme la nouvelle colonne auto_incrément, du coup c'est OK sur ce point

Par contre, on peut ajouter que si on fait une insertion en masse (avec un LOAD DATA INFILE par exemple), MySQL/MariaDB peut lui même créer un trou dans la série d'auto-incrément. Je ne sais plus pourquoi mais je l'ai déjà constaté. Donc bidouiller la suite des auto_increment ne sert à rien !

C'est sans doute lié aux rejets en cas de violation de contrainte (avec le paramètre IGNORE)

[Artemus24]

Salut à tous.

Désolé Escartefigue, je suis pas mal fatigué en ce moment et il m'arrive de m'embrouiller dans mes réponses.

En effet, si l'on supprime la primary key, automatiquement, tous les lignes des tables filles seront à leur tour supprimées.
Ce n'est donc pas la bonne approche si l'on a comme contrainte l'option CASCADE pour les foreign key.

Il vaut mieux utiliser un update comme je l'ai fait dans mon exemple.
Sauf que cette approche ne va pas remettre l'auto incrément à la dernière valeur de la primary key.
Il y aura bien un trou lors de la prochaine insertion.

Dans le même ordre d'idées, quand une maison est détruite dans une rue, est-ce qu'on renumérote toutes les maisons de la rue ?

Votre comparaison est excellente !!!

Je me pose encore une fois l'intérêt de cette discussion au sujet des trous dans une clef primaire technique.
En quoi cela dérange nos chers néophytes d'avoir des trous dans la numérotation ?
Il y a une forme d'obsession de type compulsive à vouloir boucher ces trous.

@+

[Dens1]

Je me pose encore une fois l'intérêt de cette discussion au sujet des trous dans une clef primaire technique.
En quoi cela dérange nos chers néophytes d'avoir des trous dans la numérotation ?
Il y a une forme d'obsession de type compulsive à vouloir boucher ces trous.@+

De mémoire, en fait, non. Puisqu'on supprime le premier auto-incrément pour en créer un nouveau dans une autre colonne, le second redémarre à 1.

C'est ce que j'avais compris.

[QUOTE=CinePhil]Par contre, on peut ajouter que si on fait une insertion en masse (avec un LOAD DATA INFILE par exemple), MySQL/MariaDB peut lui même créer un trou dans la série d'auto-incrément. Je ne sais plus pourquoi mais je l'ai déjà constaté. Donc bidouiller la suite des auto_increment ne sert à rien ! /QUOTE]
LOAD DATA INFILE ça me plaît bien ça, et puis je commence à regarder les clés étrangères de près et .... Il est temps de plonger dans le tuto cité page 1. Nager c'est une chose, couler, ça m'ennuierait.
Cette discussion sur les trous fait avancer mine de rien, merci à vous (Rassurez-vous j'ai bien compris l'aspect purement technique de la colonne auto_incrément).