自动删除连接数据以及删除记录?

2022-01-20 00:00:00 sql foreign-keys foreign-key-relationship mysql junction-table

我有用户，有优惠，还有一个联结表 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)
);

相关文章