Sylwester Kardziejonek Sylwester Kardziejonek - 1 month ago 17
MySQL Question

MySQL Double way Foreign Keys?

I was wondering if this kind of thing will work:

Let's say I have two tables: COMPANIES and ADDRESSES. Each company can have only one address. Simplified schema would look like this.

COMPANIES
id
address_id
name
(...)

ADDRESSES
id
first_name
street
(...)


Now, I want to add foreign key on COMPANIES.address_id -> ADDRESSES.id ON DELETE SET NULL ON UPDATE CASCADE.
But I also want the address to be deleted when the company is deleted. So the other way around it would be ADDRESSES.id -> COMPANIES.address_id ON DELETE CASCADE. Is this safe and possible?

Answer

Maybe, instead of foreign keys, using triggers could be a solution to your problem.

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