I am trying to do the simplest of tasks, adding a single relationship to my table and having no luck in the process.
I have two tables, one users
and the other user_business
.
user_business
is additional, optional details that a users
can have.
For this situation, I was going to make it a 1-1 identifying relationship
since you can’t have additional details without a user.
In the tool I am using, MySQL Workbench, I clicked on the 1 - - - 1
icon and selected both tables. I tried selecting them both in the opposite order to rule that out.
Once the relationship has been created, I try to update a record that is in user_business
with a value of 0
on the userid
to a value of 1
which will match the id of the user in the users
table.
When I do this, I get the following error:
Operation failed: std::exception Executing: UPDATE crowd
.user_business
SET userid
=’1′ WHERE userid
=’0′;
ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails (`crowd`.`users`, CONSTRAINT `fk_users_user_business` FOREIGN KEY (`user_business_userid`) REFERENCES `user_business` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION) SQL Statement: UPDATE `crowd`.`user_business` SET `userid`='1' WHERE `userid`='0'
ID
in users
is userid
in user_business
. What step am I missing here?
UPDATE
After hovering over every icon, I found one that allows me to create relationships between existing columns. I think that was what I needed this whole time. Will update if this is not the case.