自动删除联结数据以及记录的DELETE吗? [英] Automatically delete junction data along with DELETE of a record?

问题描述

我有用户，有要约和联结表users_offers.

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?

推荐答案

声明一个引用操作:ON DELETE CASCADE，例如:

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小提琴]

足够有趣的是，似乎在简写"外键语法中指定引用动作不起作用(在MySQL 5.5.30，5.6.6 m9下确认).将解析以下内容，但是删除 user 时，不会删除相应的 user_offer :

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)
);

这篇关于自动删除联结数据以及记录的DELETE吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！