MySQL双向外键？

Question

我想知道这种事情是否可行:

假设我有两个表:COMPANIES 和 ADDRESSES。每个公司只能有一个地址。简化的架构如下所示。

COMPANIES
id
address_id
name
(...)

ADDRESSES
id
first_name
street
(...)

现在，我想在 COMPANIES.address_id -> ADDRESSES.id ON DELETE SET NULL ON UPDATE CASCADE 上添加外键。但是我还希望在删除公司时删除地址。因此，另一种方法是 ADDRESSES.id -> COMPANIES.address_id ON DELETE CASCADE。这安全且可行吗？

Answer 1

也许，使用触发器而不是外键可以解决您的问题。

create trigger addr_delete
after delete on companies for each row
begin
   delete from addresses where id=old.address_id
end