maxday
maxday

Reputation: 1332

How to handle association table and cascade constraints in MYSQL

I think this is a common problem but I was not able to find correct keywords to perform a search on StackOverflow.

In mysql, I have two entites : organization and scale. One organization can only have one scale but a scale can belong to many organizations.

So I created a third entity organization_scale.

for instance :

organization
------------
org_id | name
     1 | Mickey
     2 | Donald
     3 | Dingo

scale
----------
 sc_id | name
     1 | miniScale
     2 | maxiScale 

organization_scale
--------------
org_id | sc_id
     1 | 1
     2 | 1
     3 | 2

Both fields of the organization_scale entity are foreign keys + cascade on update and delete

The problem is the following:

If I delete from organization where org_id = 3,the third line from organization_scale are properly deleted (since org_id = 3 does not exist anymore)

BUT

the scale with sc_id = 2 is not deleted. And I do not understand why. No one references to this scale, and this should be removed otherwise, with a big amount of data, a lot of "scales" will be orphean.

(same thing happens if I do not use an extra table, and I add a scale_id column directly in organization)

Upvotes: 3

Views: 1018

Answers (1)

nCessity
nCessity

Reputation: 765

I think the problem is this: The ON DELTE CASCADE in the definition of the organization_scale table only works in one direction: when deleting something in scale or organization then don't ask and delete the rows in organization_scale.

But you seem to want the other direction: When deleting something in organization_scale delete the row in scale if there is no reference to it anymore.

This works for me: I added a trigger on organization.

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;


CREATE TABLE organization (org_id INT UNSIGNED PRIMARY KEY, name VARCHAR(255));
CREATE TABLE scale (sc_id INT UNSIGNED PRIMARY KEY, name VARCHAR(255));
CREATE TABLE organization_scale (org_id INT UNSIGNED, sc_id INT UNSIGNED,
    FOREIGN KEY (org_id)
        REFERENCES organization(org_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (sc_id)
        REFERENCES scale(sc_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TRIGGER delete_unused_sc_id AFTER DELETE ON organization FOR EACH ROW DELETE FROM scale WHERE NOT EXISTS (SELECT * FROM organization_scale WHERE organization_scale.sc_id = scale.sc_id);

INSERT INTO organization VALUES
(1,"Mickey"),
(2,"Donald"),
(3,"Dingo");

INSERT INTO scale VALUES
(1,"miniScale"),
(2,"maxiScale");

INSERT INTO organization_scale VALUES
(1,1),
(2,1),
(3,2);

SELECT * FROM organization_scale;
SELECT * FROM scale;

DELETE FROM organization WHERE org_id=3;

SELECT * FROM organization_scale;
SELECT * FROM scale;


DROP DATABASE test;

Upvotes: 1

Related Questions