I want to have a relational data between two table with DELETE query.
I don't know what keyword should I search and such confusing for me to learn relational database.
So in this case, I want to delete one of the user data in the user table.
So the buy_product table which contains the user_id column will also be deleted along with the rows in it.
This is user table:
| user_id | name |
|---|---|
| 1 | John |
| 2 | Doe |
This is buy_product table:
| id_product | name | user_id |
|---|---|---|
| 1 | Cookies | 2 |
| 2 | Pizza | 2 |
| 3 | Burger | 1 |
For example, if I run the DELETE FROM user WHERE user_id = 2 query, the result is:
| user_id | name |
|---|---|
| 1 | John |
And buy_product table with user_id = 2 also deleted the data that I want without run DELETE FROM buy_product WHERE user_id = 2 query like this:
| id_product | name | user_id |
|---|---|---|
| 3 | Burger | 1 |
I think for all understandable answers means a lot to me. Thanks!
CodePudding user response：
If you defined a foreign key constraint with ON DELETE CASCADE between buy_product and user then deleting a row from the parent table user will automatically delete the related rows in the child table buy_product.
For example, buy_product could be created as:
create table buy_product (
id_product int,
name varchar(50),
user_id int references user (user_id) on delete cascade
);
CodePudding user response：
Ideally you should configure cascading deletion such that when a record is deleted from the user table all dependent records are also deleted from the buy_product table. In lieu of that, we can try doing a delete join here:
DELETE u, bp
FROM user u
INNER JOIN buy_product bp
ON bp.user_id = u.user_id
WHERE u.user_id = 2;