Problème d'insertion/update avec une clé étrangère

Résolu
Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention   -  
Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention   -
Bonjour,

Je n'arrive pas insérer ou modifier une donnée d'une colonne servant de clé étrangère, même avec ON DUPLICATE KEY UPDATE.

Savez-vous comment faire ?

Merci de vos réponses.
A voir également:

1 réponse

Réponse 1 / 1
yg_be Messages postés 23541 Date d'inscription   Statut Contributeur Dernière intervention   Ambassadeur 1 584
 
bonjour, peux-tu partager ce que tu fais, la définition de tes tables, et le message d'erreur que tu obtiens?
0
Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention   1
 
Bonjour yg_be,

Ce que j'essaye de faire, pour l'instant, c'est d'insérer l'id d'un avatar (de la table avatar) dans id_dbassobdm_avatar (de la table user). Pour l'instant, j'ai testé ma requête avec phpmyadmin.

Voici l'erreur :

#1452 - Cannot add or update a child row: a foreign key constraint fails (`BDM`.`dbassobdm_user`, CONSTRAINT `dbassobdm_user_id_dbassobdm_status_user` FOREIGN KEY (`id_dbassobdm_status_user`) REFERENCES `dbassobdm_status_user` (`id`))

Voici la définition de mes tables : 
TABLE dbassobdm_user(
        id                        int (11) Auto_increment  NOT NULL ,
        first_name                Varchar (25) NOT NULL ,
        last_name                 Varchar (25) NOT NULL ,
        mail                      Varchar (70) NOT NULL ,
        date_signup               Char (10) NOT NULL ,
        key_user                  Int NOT NULL ,
        password                  Varchar (70) NOT NULL ,
        id_dbassobdm_status_user Int NOT NULL ,
        id_dbassobdm_avatar      Int NULL ,
        PRIMARY KEY (id ) ,
        UNIQUE (mail )
);

TABLE dbassobdm_status_user(
        id          int (11) Auto_increment  NOT NULL ,
        status_user Varchar (20) NOT NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_actuality(
        id                     int (11) Auto_increment  NOT NULL ,
        article                Text NOT NULL ,
        title                  Varchar (25) NOT NULL ,
        author_article         Varchar (25) NOT NULL ,
        id_dbassobdm_user     Int NULL ,
        id_dbassobdm_document Int NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_document(
        id                              int (11) Auto_increment  NOT NULL ,
        path_document                   Varchar (50) NOT NULL ,
        id_dbassobdm_user              Int NULL ,
        id_dbassobdm_actuality         Int NOT NULL ,
        id_dbassobdm_event_association Int NOT NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_task(
        id                        int (11) Auto_increment  NOT NULL ,
        suggested_task            Varchar (25) NOT NULL ,
        volunteer_assigned        Varchar (25) NOT NULL ,
        date_task                 Char (10) NOT NULL ,
        id_dbassobdm_user        Int NULL ,
        id_dbassobdm_status_task Int NOT NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_status_task(
        id          int (11) Auto_increment  NOT NULL ,
        status_task Varchar (20) NOT NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_event_association(
        id                         int (11) Auto_increment  NOT NULL ,
        description_event          Varchar (200) NOT NULL ,
        date_event                 Char (10) NOT NULL ,
        author_event               Varchar (25) NOT NULL ,
        id_dbassobdm_user         Int NULL ,
        id_dbassobdm_document     Int NULL ,
        id_dbassobdm_status_event Int NOT NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_status_event(
        id           int (11) Auto_increment  NOT NULL ,
        status_event Varchar (20) NOT NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_avatar(
        id                 int (11) Auto_increment  NOT NULL ,
        path_avatar        Varchar (50) NOT NULL ,
        id_dbassobdm_user Int NULL ,
        PRIMARY KEY (id )
);

TABLE dbassobdm_comment_article(
        first_name              Varchar (25) NOT NULL ,
        comment_article         Varchar (200) NOT NULL ,
        id                      Int NOT NULL ,
        id_dbassobdm_actuality Int NOT NULL ,
        PRIMARY KEY (id ,id_dbassobdm_actuality )
);

TABLE dbassobdm_assigned(
        volunteer          Varchar (25) NOT NULL ,
        id                 Int NOT NULL ,
        id_dbassobdm_task Int NOT NULL ,
        PRIMARY KEY (id ,id_dbassobdm_task )
);

TABLE dbassobdm_participation(
        volunteer_present               Varchar (25) NOT NULL ,
        id                              Int NOT NULL ,
        id_dbassobdm_event_association Int NOT NULL ,
        PRIMARY KEY (id ,id_dbassobdm_event_association )
);
0
yg_be Messages postés 23541 Date d'inscription   Statut Contributeur Dernière intervention   1 584 > Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention  
 
peux-tu partager ta requête, ainsi que les définitions de tes contraintes?
0
Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention   1
 
Voila la requête : INSERT INTO `dbassobdm_avatar`(`path_avatar`, `id_dbassobdm_user`) VALUES ('test', 1) ON DUPLICATE KEY UPDATE `id` = 1

Comme solution au problème j'ai essayé d'insérer l'avatar dans la table avatar et modifier id_dbassobdm_avatar dfe la table user. Ça fonctionne. Mais je ne suis pas sûr que ce soit la solution idéale.

Re voila la définition de mes tables et contraintes :

TABLE dbassobdm_user(
id int (11) Auto_increment NOT NULL ,
first_name Varchar (25) NOT NULL ,
last_name Varchar (25) NOT NULL ,
mail Varchar (70) NOT NULL ,
date_signup Char (10) NOT NULL ,
key_user Int NOT NULL ,
password Varchar (70) NOT NULL ,
id_dbassobdm_status_user Int NOT NULL ,
id_dbassobdm_avatar Int NULL ,
PRIMARY KEY (id ) ,
UNIQUE (mail )
)ENGINE=InnoDB;

TABLE dbassobdm_status_user(
id int (11) Auto_increment NOT NULL ,
status_user Varchar (20) NOT NULL ,
id_dbassobdm_user Int NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_actuality(
id int (11) Auto_increment NOT NULL ,
article Text NOT NULL ,
title Varchar (25) NOT NULL ,
author_article Varchar (25) NOT NULL ,
id_dbassobdm_user Int NULL ,
id_dbassobdm_document Int NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_document(
id int (11) Auto_increment NOT NULL ,
path_document Varchar (50) NOT NULL ,
id_dbassobdm_user Int NULL ,
id_dbassobdm_actuality Int NULL ,
id_dbassobdm_event_association Int NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_task(
id int (11) Auto_increment NOT NULL ,
suggested_task Varchar (25) NOT NULL ,
volunteer_assigned Varchar (25) NOT NULL ,
date_task Char (10) NOT NULL ,
id_dbassobdm_user Int NULL ,
id_dbassobdm_status_task Int NOT NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_status_task(
id int (11) Auto_increment NOT NULL ,
status_task Varchar (20) NOT NULL ,
id_dbassobdm_task Int NOT NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_event_association(
id int (11) Auto_increment NOT NULL ,
description_event Varchar (200) NOT NULL ,
date_event Char (10) NOT NULL ,
author_event Varchar (25) NOT NULL ,
id_dbassobdm_user Int NULL ,
id_dbassobdm_document Int NULL ,
id_dbassobdm_status_event Int NOT NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_status_event(
id int (11) Auto_increment NOT NULL ,
status_event Varchar (20) NOT NULL ,
id_dbassobdm_event_association Int NOT NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_avatar(
id int (11) Auto_increment NOT NULL ,
path_avatar Varchar (50) NOT NULL ,
id_dbassobdm_user Int NULL ,
PRIMARY KEY (id )
)ENGINE=InnoDB;

TABLE dbassobdm_comment_article(
first_name Varchar (25) NOT NULL ,
comment_article Varchar (200) NOT NULL ,
id Int NOT NULL ,
id_dbassobdm_actuality Int NOT NULL ,
PRIMARY KEY (id ,id_dbassobdm_actuality )
)ENGINE=InnoDB;

TABLE dbassobdm_assigned(
volunteer Varchar (25) NOT NULL ,
id Int NOT NULL ,
id_dbassobdm_task Int NOT NULL ,
PRIMARY KEY (id ,id_dbassobdm_task )
)ENGINE=InnoDB;

TABLE dbassobdm_participation(
volunteer_present Varchar (25) NOT NULL ,
id Int NOT NULL ,
id_dbassobdm_event_association Int NOT NULL ,
PRIMARY KEY (id ,id_dbassobdm_event_association )
)ENGINE=InnoDB;

ALTER TABLE dbassobdm_user ADD CONSTRAINT dbassobdm_user_id_dbassobdm_status_user FOREIGN KEY (id_dbassobdm_status_user) REFERENCES dbassobdm_status_user(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_user ADD CONSTRAINT dbassobdm_user_id_dbassobdm_avatar FOREIGN KEY (id_dbassobdm_avatar) REFERENCES dbassobdm_avatar(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_status_user ADD CONSTRAINT dbassobdm_status_user_id_dbassobdm_user FOREIGN KEY (id_dbassobdm_user) REFERENCES dbassobdm_user(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_actuality ADD CONSTRAINT dbassobdm_actuality_id_dbassobdm_user FOREIGN KEY (id_dbassobdm_user) REFERENCES dbassobdm_user(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_actuality ADD CONSTRAINT dbassobdm_actuality_id_dbassobdm_document FOREIGN KEY (id_dbassobdm_document) REFERENCES dbassobdm_document(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_document ADD CONSTRAINT dbassobdm_document_id_dbassobdm_user FOREIGN KEY (id_dbassobdm_user) REFERENCES dbassobdm_user(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_document ADD CONSTRAINT dbassobdm_document_id_dbassobdm_actuality FOREIGN KEY (id_dbassobdm_actuality) REFERENCES dbassobdm_actuality(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_document ADD CONSTRAINT dbassobdm_document_id_dbassobdm_event_association FOREIGN KEY (id_dbassobdm_event_association) REFERENCES dbassobdm_event_association(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_task ADD CONSTRAINT dbassobdm_task_id_dbassobdm_user FOREIGN KEY (id_dbassobdm_user) REFERENCES dbassobdm_user(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_task ADD CONSTRAINT dbassobdm_task_id_dbassobdm_status_task FOREIGN KEY (id_dbassobdm_status_task) REFERENCES dbassobdm_status_task(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_status_task ADD CONSTRAINT dbassobdm_status_task_id_dbassobdm_task FOREIGN KEY (id_dbassobdm_task) REFERENCES dbassobdm_task(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_event_association ADD CONSTRAINT dbassobdm_event_association_id_dbassobdm_user FOREIGN KEY (id_dbassobdm_user) REFERENCES dbassobdm_user(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_event_association ADD CONSTRAINT dbassobdm_event_association_id_dbassobdm_document FOREIGN KEY (id_dbassobdm_document) REFERENCES dbassobdm_document(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_event_association ADD CONSTRAINT dbassobdm_event_association_id_dbassobdm_status_event FOREIGN KEY (id_dbassobdm_status_event) REFERENCES dbassobdm_status_event(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_status_event ADD CONSTRAINT dbassobdm_status_event_id_dbassobdm_event_association FOREIGN KEY (id_dbassobdm_event_association) REFERENCES dbassobdm_event_association(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_avatar ADD CONSTRAINT dbassobdm_avatar_id_dbassobdm_user FOREIGN KEY (id_dbassobdm_user) REFERENCES dbassobdm_user(id) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE dbassobdm_comment_article ADD CONSTRAINT dbassobdm_comment_article_id FOREIGN KEY (id) REFERENCES dbassobdm_user(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_comment_article ADD CONSTRAINT dbassobdm_comment_article_id_dbassobdm_actuality FOREIGN KEY (id_dbassobdm_actuality) REFERENCES dbassobdm_actuality(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_assigned ADD CONSTRAINT dbassobdm_assigned_id FOREIGN KEY (id) REFERENCES dbassobdm_user(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_assigned ADD CONSTRAINT dbassobdm_assigned_id_dbassobdm_task FOREIGN KEY (id_dbassobdm_task) REFERENCES dbassobdm_task(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_participation ADD CONSTRAINT dbassobdm_participation_id FOREIGN KEY (id) REFERENCES dbassobdm_user(id) ON UPDATE CASCADE;
ALTER TABLE dbassobdm_participation ADD CONSTRAINT dbassobdm_participation_id_dbassobdm_event_association FOREIGN KEY (id_dbassobdm_event_association) REFERENCES dbassobdm_event_association(id) ON UPDATE CASCADE;
0
yg_be Messages postés 23541 Date d'inscription   Statut Contributeur Dernière intervention   1 584 > Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention  
 
au lieu de: 
INSERT INTO `dbassobdm_avatar`(`path_avatar`, `id_dbassobdm_user`) VALUES ('test', 1) ON DUPLICATE KEY UPDATE `id` = 1

ne devrais-tu pas faire: 
INSERT INTO `dbassobdm_avatar`(`path_avatar`, `id_dbassobdm_user`) VALUES ('test', 1) ON DUPLICATE KEY UPDATE `id_dbassobdm_user` = 1

peux-tu partager la requête qui fonctionne (insérer l'avatar dans la table avatar et modifier id_dbassobdm_avatar de la table user)?
si je comprends bien, tu as dans avatar un lien vers user et vers user un lien vers avatar. pourquoi fais-tu cela? pourquoi ne pas fusionner les deux tables?
0
Mephistole Messages postés 55 Date d'inscription   Statut Membre Dernière intervention   1
 
J'ai fais une table avatar parce que ça m'a sembler représenter une entité à part entière. Pour fusionner les tables avatar et user j'y réfléchis.

Ce que j'avais oublié de préciser c'est que pour insérer la clé primaire de avatar dans id_dbassobdm_avatar de la table user.

Voila les requêtes : 
INSERT INTO `dbassobdm_avatar`(`path_avatar`, `id_dbassobdm_user`) VALUES (:path_avatar, :id)
UPDATE `dbassobdm_user` SET `id_dbassobdm_avatar` = (SELECT `id` FROM `dbassobdm_avatar` WHERE `id_dbassobdm_user` = :id)
0