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;