I need fix this query:
DELETE t1, t2, t3
FROM roles as t1
JOIN user_role as t2 on t1.ROLEID = t2.ROLEID
JOIN role_perm as t3 on t1.ROLEID = t3.ROLEID
WHERE t1.ROLEID = $role_id"
"user_role" and "role_perm" are in relation with roles, and they have DELETE ON CASCADE.
But when I tried to delete a row, if I have not added data in user_role or role_perm, row cannot be deleted, only when I have added data in three tables, the row is deleted.
Your joins are inner joins, so only when data is in all three tables will you have a result to delete (the result set of your joins).
Assuming you want to do the following:
roles.ROLEID = <some_role_id>user_role records with user_role.ROLEID = <some_role_id>role_perm records with user_role.ROLEID = <some_role_id>Convert them to left outer joins, with the first table the main table all others depend on.
DELETE t1, t2, t3
FROM roles as t1
LEFT JOIN user_role as t2 on t1.ROLEID = t2.ROLEID
LEFT JOIN role_perm as t3 on t1.ROLEID = t3.ROLEID
WHERE t1.ROLEID = $role_id
The above will work without the cascade foreign key constraint. If you've the cascade and assuming it's on Foreign keys tied to roles.ROLEID you'll only need DELETE t1 FROM... as the delete will cascade to all other tables.
Thus if both user_role and role_perm have cascading FK's on roles.ROLEID:
DELETE FROM roles WHERE t1.ROLEID = $role_id;
Now, I run like the plague from cascading FK's and prefer the explicit delete of the first query. Have had too many unexpected cascading deletes in my life I guess.
From the logical point of view, the join between the three tables creates a LOGICAL table that then is acted on by your command.
Since you have no data in all three tables, the joins return an empty result and hence nothing to delete.
From the programming point of view, what you are trying to do is not very recommendable.
You don't need to delete the rows from child tables, "user_role" and "role_perm" as you said there is DELETE ON CASCADE.
All you need to do is, delete the rows from main/parent table, then child table rows automatically get deleted due to DELETE ON CASCADE.
Try below SQL:
DELETE roles
WHERE ROLEID = $role_id
You are using inner join to delete data from Table. If the data do not exist in child table inner join ignore the row that's why was row not deleted.
Try to delete using left join or in only parent table if delete cascade active on all tables
