Foreign Keys (Multiple Parents for Child)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Foreign Keys (Multiple Parents for Child)

Post by Ollie Saunders »

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:

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;
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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

I think you've got your table declarations a bit mixed up - you need to put the foreign key bits in the table containing the foreign key, not the table the foreign key refers to:

Code: Select all

CREATE TABLE Site ( 
    siteId          INT UNSIGNED NOT NULL AUTO_INCREMENT, 
                    PRIMARY KEY(siteId), 
    _msgSetId       INT UNSIGNED NULL, 
                    FOREIGN KEY(_msgSetId) REFERENCES 
                        MsgSet(msgSetId)
                    ON DELETE CASCADE, 
) ENGINE = InnoDB; 

CREATE TABLE Client ( 
    clientId        INT UNSIGNED NOT NULL AUTO_INCREMENT, 
                    PRIMARY KEY(clientId), 
    _msgSetId       INT UNSIGNED NULL, 
                    FOREIGN KEY(_msgSetId) REFERENCES 
                        MsgSet(msgSetId)
                    ON DELETE CASCADE, 
) ENGINE = InnoDB; 

CREATE TABLE Subclient ( 
    subclientId     INT UNSIGNED NOT NULL AUTO_INCREMENT, 
                    PRIMARY KEY(subclientId), 
    _msgSetId       INT UNSIGNED NULL, 
                    FOREIGN KEY(_msgSetId) REFERENCES 
                        MsgSet(msgSetId)
                    ON DELETE CASCADE, 
) ENGINE = InnoDB; 

CREATE TABLE MsgSet ( 
    msgSetId        INT UNSIGNED NOT NULL AUTO_INCREMENT, 
                    PRIMARY KEY(msgSetId), 
    stuff1          TEXT NULL 
    stuff2          TEXT NULL 
    stuff3          TEXT NULL 
    stuff4          TEXT NULL 
    stuff5          TEXT NULL 
) ENGINE = InnoDB; 
I think that this is the correct syntax but I haven't tested it
Post Reply