i have workflow in application admin organization can add user organization. once form submission, first insert new record user table users email , organization id associated admin. after user created, member record connects user organization created users email , users organization id , user_id. issue running creating record on member table created users user_id. while have no issue storing organization_id present, reason passing user_id throws error related foreign key:
cannot add or update child row: foreign key constraint fails (`work`.`member`, constraint `member_ibfk_2` foreign key (`user_id`) references `organization` (`organization_id`) on delete cascade on update cascade)] why can't pass user_id on record creation. because column associated foreign key?
here inserts:
user insert (no error):
insert `user` (`user_id`,`email`,`organization_id`,`updatedat`,`createdat`) values (default,'test+email@gmail.com','1','2016-06-07 11:51:54','2016-06-07 11:51:54'); member insert (error user_id):
insert `member` (`member_id`,`member_email`,`organization_id`,`user_id`,`updatedat`,`createdat`) values (default,'test+email@gmail.com','1',8,'2016-06-07 11:51:54','2016-06-07 11:51:54'); user table:
`user` ( `user_id` int(11) not null auto_increment, `first_name` varchar(255) default null, `last_name` varchar(255) default null, `email` varchar(255) default null, `password` varchar(255) default null, `organization_id` int(11) default null, `authentication_token` varchar(255) default null, `reset_password_token` varchar(255) default null, `reset_password_expires` datetime default null, `createdat` datetime not null, `updatedat` datetime not null, primary key (`user_id`), unique key `email` (`email`) ) engine=innodb auto_increment=10 default charset=utf8; organization table:
`organization` ( `organization_id` int(11) not null auto_increment, `organization_name` varchar(255) default null, `admin` varchar(255) default null, `createdat` datetime not null, `updatedat` datetime not null, primary key (`organization_id`) ) engine=innodb auto_increment=2 default charset=utf8; member table:
`member` ( `member_id` int(11) not null auto_increment, `member_email` varchar(255) default null, `organization_id` int(11) default null, `user_id` int(11) default null, `createdat` datetime not null, `updatedat` datetime not null, primary key (`member_id`), unique key `member_email` (`member_email`), unique key `member_user_id_organizationid_unique` (`organization_id`,`user_id`), key `user_id` (`user_id`), constraint `member_ibfk_1` foreign key (`organization_id`) references `user` (`user_id`) on delete cascade on update cascade, constraint `member_ibfk_2` foreign key (`user_id`) references `organization` (`organization_id`) on delete cascade on update cascade ) engine=innodb auto_increment=11 default charset=utf8;
seem me got constraints mixed up.
i created tables using sql , reverse engineered mysqlworkbench eer containing tables.
on first sight can see organization_id in table organization linked user_id in members table.
if change constraints following, work:
constraint `member_ibfk_1` foreign key (`organization_id`) references `organization` (`organization_id`) on delete cascade on update cascade, constraint `member_ibfk_2` foreign key (`user_id`) references `user` (`user_id`) on delete cascade on update cascade 