Automatically delete junction data along with DELETE of a record?

Question

I have users, have offers, and a junction table users_offers.

Is there a setup I can carry out with foreign key relations that can ensure that junction data will be automatically deleted when I delete any users or offers?

Answer 1

Declare a referential action: ON DELETE CASCADE, for example:

CREATE TABLE user (
    user_id int PRIMARY KEY
);

CREATE TABLE offer (
    offer_id int PRIMARY KEY
);

CREATE TABLE user_offer (
    user_id int,
    offer_id int,
    PRIMARY KEY (user_id, offer_id),
    FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES offer (offer_id) ON DELETE CASCADE
);

[SQL Fiddle]

Interestingly enough, it seems that specifying referential action in "shorthand" foreign key syntax doesn't work (confirmed under MySQL 5.5.30, 5.6.6 m9). The following gets parsed, but when user is deleted, the corresponding user_offer doesn't get deleted:

CREATE TABLE user_offer (
    user_id int REFERENCES user (user_id) ON DELETE CASCADE,
    offer_id int REFERENCES offer (offer_id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, offer_id)
);

Answer 2

You can specify this within your model creation:

CREATE TABLE users_offers (user_id INT NOT NULL,
                           offer_id INT NOT NULL,
                           PRIMARY KEY (user_id, offer_id),
                           FOREIGN KEY (user_id) REFERENCES users(id)
                               ON DELETE CASCADE,
                           FOREIGN KEY (offer_id) REFERENCES offers(id)
                               ON DELETE CASCADE);

You can see a working example in this Fiddle.