NaderH's avatar

(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'.

0 likes
0 replies

Please or to participate in this conversation.