CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
Sylwester Kardziejonek
Sylwester Kardziejonek

Reputation: 592

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?

Upvotes: 2

Views: 1134

Answers (1)

Karan Punamiya
Karan Punamiya

Reputation: 8863

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

Upvotes: 2

Related Questions