Discussion :

[Paprick]

Bonsoir,

Le problème c'est d'avoir une pk avec un identifiant relatif qui est un auto_increment + une autre colonne, c'est là que ça ne fonctionne pas, or, c'est le cas le plus fréquent.

Le problème de MySQL, c'est qu'il considère surement qu'un identifiant auto-incrémenté est forcément unique à lui tout seul (ce qui est vrai) : toute autre colonne qui lui est rajoutée provoque donc une surclé et va alors à l'encontre du principe d'irréductibilité...
Par contre, si cet identifiant est relatif, c'est à dire une clé étrangère issue d'une autre table, il devient un INT sans auto_increment dans la table d'accueil où il participe à la clé primaire... donc pas de problème.
Pour moi, le problème peut plutôt venir du fait que c'est l'autre colonne locale qui ne peut pas être en auto_increment, non ?

[CinePhil]

Pourtant ça fonctionne :

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
CREATE TABLE `test_pere` (
 `per_id` int(11) NOT NULL AUTO_INCREMENT,
 `per_nom` varchar(30) NOT NULL,
 PRIMARY KEY (`per_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
 
INSERT INTO test_pere (per_nom)
VALUES ('Toto'), ('Titi');
 
CREATE TABLE `test_fils` (
 `fils_id_pere` int(11) NOT NULL,
 `fils_numero` int(11) NOT NULL AUTO_INCREMENT,
 `fils_nom` varchar(30) NOT NULL,
 PRIMARY KEY (`fils_id_pere`,`fils_numero`),
 UNIQUE KEY `xu_fils_numero` (`fils_numero`),
 CONSTRAINT `fk_fils_id_pere` FOREIGN KEY (`fils_id_pere`) REFERENCES `test_pere` (`per_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
 
INSERT INTO test_fils (fils_id_pere, fils_nom)
VALUES (1, 'Nifnif'), (1, 'Nafnaf'), (2, 'Bob), (2, 'Carole');
 
SELECT *
FROM test_fils;

fils_id_pere fils_numero fils_nom
1 1 Nifnif
1 2 Nafnaf
2 3 Bob
2 4 Carole

[escartefigue]

@CinePhil : ce script ne fonctionne pas pour moi, la table fils n'est pas créée (j'ai par ailleurs ajouté la quote manquante à "Bob" )
Essayé avec V5.7 et V8 sous DBfiddle, même résultat

https://dbfiddle.uk/?rdbms=mysql_5.7...b2702b81e6bdfe

[fsmrel]

Philippe, comme tu as codé :

CREATE TABLE test_pere (
...
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
 ;
INSERT INTO test_fils (fils_id_pere, fils_nom)
VALUES (1, 'Nifnif'), (1, 'Nafnaf'), (2, 'Bob'), (2, 'Carole'); 

ton INSERT devrait être le suivant :

INSERT INTO test_fils (fils_id_pere, fils_nom)
VALUES (3, 'Nifnif'), (3, 'Nafnaf'), (4, 'Bob'), (4, 'Carole');

A moins que ton AUTO_INCREMENT de la table test_pere ne démarre à 1.

Et pour éviter les ennuis, pour ma part j’utilise une clé alternative. Par exemple, si per_nom en est une, l’INSERT peut devenir :

INSERT INTO TEST_FILS (fils_id_pere, fils_nom)
VALUES 
    ((SELECT per_id FROM test_pere WHERE per_nom = 'Toto'), 'Raoul')
  , ((SELECT per_id FROM test_pere WHERE per_nom = 'Toto'), 'Paul')  
; 

[CinePhil]

C'est parce que j'ai tout créé et rempli les tables avec phpMyAdmin. Ensuite j'ai fait un SHOW CREATE TABLE de chaque table qui importe la valeur de l'auto-incrément. Pas fait attention à ce détail.

Je ne sais pas si ça plante (sans les valeurs forcées de l'auto-incrément) dans MySQL mais en tout cas j'ai pu créer ça sans problème dans MariaDB avec phpMyAdmin.
Et je viens de copier/coller mon script, corriger l'apostrophe manquante et supprimer les valeurs d'auto-incrément et ça passe crême via phpMyAdmin.

[fsmrel]

Philippe, j’ai testé ton code avec MySQL 5.7.23, et bien sûr j’ai eu droit au message :

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails

J’ai donc remplacé :

INSERT INTO test_fils (fils_id_pere, fils_nom)
VALUES (1, 'Nifnif'), (1, 'Nafnaf'), (2, 'Bob'), (2, 'Carole'); 

par :

INSERT INTO test_fils (fils_id_pere, fils_nom)
VALUES (3, 'Nifnif'), (3, 'Nafnaf'), (4, 'Bob'), (4, 'Carole');

Et cette fois-ci tout s’est évidemment bien passé.

Si pour toi ton INSERT inchangé fonctionne, c’est que l’intégrité référentielle n’est pas activée.

[CinePhil]

Comme dit plus haut, j'ai pris mon script, j'ai ajouté l'apostrophe manquante et j'ai supprimé des deux tables les instructions AUTO_INCREMENT=x. J'ai collé dans l'onglet sql de phpMyAdmin et ça a fonctionné.

Je redonne le script corrigé :

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
CREATE TABLE `test_pere` (
 `per_id` int(11) NOT NULL AUTO_INCREMENT,
 `per_nom` varchar(30) NOT NULL,
 PRIMARY KEY (`per_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO test_pere (per_nom)
VALUES ('Toto'), ('Titi');
 
CREATE TABLE `test_fils` (
 `fils_id_pere` int(11) NOT NULL,
 `fils_numero` int(11) NOT NULL AUTO_INCREMENT,
 `fils_nom` varchar(30) NOT NULL,
 PRIMARY KEY (`fils_id_pere`,`fils_numero`),
 UNIQUE KEY `xu_fils_numero` (`fils_numero`),
 CONSTRAINT `fk_fils_id_pere` FOREIGN KEY (`fils_id_pere`) REFERENCES `test_pere` (`per_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO test_fils (fils_id_pere, fils_nom)
VALUES (1, 'Nifnif'), (1, 'Nafnaf'), (2, 'Bob'), (2, 'Carole');
 
SELECT *
FROM test_fils;

Ça fonctionne sous "10.3.26-MariaDB - Mageia MariaDB Server".

[fsmrel]

Forcément, avec ta modification, ça ne peut que marcher puisque par défaut, pour la table test_pere, l’auto-incrémentation débute à 1 et pas à 3.