MySQL remove rows related to while deleting with foreign key

Question

Let’s say I have 3 SQL tables

Users (id, name) Couples (id, title) UsersInCouples (id, user_id, couple_id)

When user is deleted I need to delete not only all rows with this user from UsersInCouples table, but also all other rows where couples are now left with only one user in it.

Answer 1

use on delete cascade

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
    
);

    create table UsersInCouples (
   id, 
  user_id,
  couple_id,
  FOREIGN KEY (user_id)
        REFERENCES users (user_id)
        ON DELETE CASCADE
  )

delete from users