Foreign Keys (Multiple Parents for Child)
Posted: Sun Jul 30, 2006 12:02 pm
Using MySQL version 4
I have three tables (Site, Client and SubClient). All of these have a _msgSetId field that references to a row in a table called MsgSet. Each table has a one to one relationship with MsgSet. Site, Client and Subclient don't share data with each other via MsgSet. They just all happen to all need to store the same stuff, so I separated it out in to a separate table.
What I want to do is be able to delete a row from Site, Client or Subclient and have the row in MsgSet belonging to it also be deleted at the same time via a cascade.
Kinda like this:
I've been digging around in the mysql manual but it seems you can't do this directly. Can you do it with ADD CONSTRAINT syntax perhaps? I thought about triggers as well but you don't have them in MySQL 4.
I have three tables (Site, Client and SubClient). All of these have a _msgSetId field that references to a row in a table called MsgSet. Each table has a one to one relationship with MsgSet. Site, Client and Subclient don't share data with each other via MsgSet. They just all happen to all need to store the same stuff, so I separated it out in to a separate table.
What I want to do is be able to delete a row from Site, Client or Subclient and have the row in MsgSet belonging to it also be deleted at the same time via a cascade.
Kinda like this:
Code: Select all
CREATE TABLE Site (
siteId INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(siteId),
_msgSetId INT UNSIGNED NULL
) ENGINE = InnoDB;
CREATE TABLE Client (
clientId INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(clientId),
_msgSetId INT UNSIGNED NULL
) ENGINE = InnoDB;
CREATE TABLE Subclient (
subclientId INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(subclientId),
_msgSetId INT UNSIGNED NULL
) ENGINE = InnoDB;
CREATE TABLE MsgSet (
msgSetId INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(msgSetId),
FOREIGN KEY(msgSetId) REFERENCES
Site(_msgSetId) AND
Client(_msgSetId) AND
Subclient(_msgSetId)
ON DELETE CASCADE,
stuff1 TEXT NULL
stuff2 TEXT NULL
stuff3 TEXT NULL
stuff4 TEXT NULL
stuff5 TEXT NULL
) ENGINE = InnoDB;