(Native php) Foreign key affects on all of the values in the column
I have a problem with a foreign key on update cascade, shortly all functions and all sql queries I used to change the status of a role are working well and they do what I want exactly, they are totally functional, the issue is when I change a value in the roles table it affects on a whole column in the users table not only the value which references to the one I changed.
// user_role_status in users table is a fk refers to role_status in roles table
// and role_status in roles table is a fk refers to id in statuses table.
statuses_table
id is_active
0 no
1 ok
roles_table
id role role_status
1 Admins 1
2 Editors 1
3 Users 1
users_table
id user_name user_level user_role_status
1 Admin 1 1
2 Editor1 2 1
3 User1 3 1
4 Editor2 2 1
5 User2 3 1
6 User3 3 1
I think this will make it more clear, when I change the status of the Editors role (role_id='2') in roles table to '0' to block any one with the user_level '2' (user_id=2,4) in the users table, I should got only the users with the id=2 and 4 became with a user_role_status = '0' but instead of that I got the whole column of the user_role_status changed to '0' and that means all users are blocked. that's the problem I have. and this is the sql query I use
ALTER TABLE users ADD CONSTRAINT fk_user_role_status FOREIGN KEY (user_role_status) REFERENCES roles(role_status) ON UPDATE CASCADE;
COMMIT;
I don't have any problem with the functions because they are already execute the blocking for the users whose user_role_status is '0'.
Please or to participate in this conversation.