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

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 )
);

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;

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)