MySQL innoDB foreign key delete cascade from three tables

Question

I have a MySQL database with several tables, connected with linking tables. My problem was that DELETE statements were very complicated. So in an attempt to make them simpler I tried setting up foreign keys with cascade deletes.

Here is my table structure

--------------
-n_size_class-
--------------
-s_id        -
-s_name      -
--------------

-------------
-n_size_rows-
-------------
-sr_id      -
-sr_name    -
-sr_value   -
-------------

----------------
-n_size_columns-
----------------
-sc_id         -
-sc_name       -
-sc_value      -
----------------

-----------------
-n_size_row_link-
-----------------
-srl_id         -
-srl_size       -
-srl_row        -
-----------------

-----------------
-n_size_col_link-
-----------------
-scl_id         -
-scl_size       -
-scl_col        -
-----------------

The idea is that the n_size_class table is the primary object (a size class) and then the rows and columns are children of the size class. The linking tables are then used to tie rows and columns to the size class. Previously, my inserts worked fine and deletes were a problem. Now that I tried setting up delete cascades, my inserts are broken (but my deletes work fine). My goal is to make inserts work properly, where size_class is inserted, then for each row, it is inserted into size_row and then also inserted into size_row_link, and the same for columns. Then when you delete just the size_class, all links and rows and columns are deleted as well.

What is the proper way to foreign key/cascade these tables? Right now, I get a foreign key constraint error when I try to insert anything into the columns or row table.

As per request, the create statements:

CREATE TABLE `n_size_class` (
  `s_id` int(11) NOT NULL auto_increment,
  `s_name` text NOT NULL,
  PRIMARY KEY  (`s_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE `n_size_columns` (
  `sc_id` int(11) NOT NULL auto_increment,
  `sc_name` text NOT NULL,
  `sc_value` text NOT NULL,
  PRIMARY KEY  (`sc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE `n_size_rows` (
  `sr_id` int(11) NOT NULL auto_increment,
  `sr_name` text NOT NULL,
  `sr_value` text NOT NULL,
  PRIMARY KEY  (`sr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE `n_size_row_link` (
  `srl_id` int(11) NOT NULL auto_increment,
  `srl_size` int(11) NOT NULL,
  `srl_row` int(11) NOT NULL,
  PRIMARY KEY  (`srl_id`),
  KEY `srl_row` (`srl_row`),
  KEY `srl_size` (`srl_size`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE `n_size_col_link` (
  `scl_id` int(11) NOT NULL auto_increment,
  `scl_size` int(11) NOT NULL,
  `scl_col` int(11) NOT NULL,
  PRIMARY KEY  (`scl_id`),
  KEY `scl_col` (`scl_col`),
  KEY `scl_size` (`scl_size`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Please provide the CREATE syntax for the 3 tables

Danny Kopping
– Danny Kopping

2014-02-24 19:58:15 +00:00
Commented Feb 24, 2014 at 19:58 — Danny Kopping
– Danny Kopping, Commented Feb 24, 2014 at 19:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

The way I see it you have 3 objects in play: Sizes, Rows, and Columns.

I'm assuming:

That the linking tables are because a Size can be assigned to many Rows/Columns, and Rows/Columns can have many Sizes.
That when a Size is deleted the assignments should be deleted, no the Rows/Columns themselves.
That when a Row/Column is deleted the assignments should be deleted, not the Sizes themselves.

So:

CREATE TABLE sizes (
  size_id INTEGER UNSIGNED AUTO_INCREMENT,
  size_name VARCHAR(255),
  ...
  PRIMARY KEY(size_id)
);

CREATE TABLE rows (
  row_id INTEGER UNSIGNED AUTO_INCREMENT,
  ...
  PRIMARY KEY(row_id)
);

CREATE TABLE columns (
  col_id INTEGER UNSIGNED AUTO_INCREMENT,
  ...
  PRIMARY KEY(col_id)
);

CREATE TABLE l_row_size (
  row_id INTEGER UNSIGNED,
  size_id INTEGER UNSIGNED,
  PRIMARY KEY(row_id, size_id),
  CONSTRAINT FOREIGN KEY l_row_size-row_id (row_id) REFERENCES rows (row_id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY l_row_size-size_id (size_id) REFERENCES sizes (size_id) ON UPDATE CASCADE ON DELETE CASCADE
);


CREATE TABLE l_col_size (
  col_id INTEGER UNSIGNED,
  size_id INTEGER UNSIGNED,
  PRIMARY KEY(col_id, size_id),
  CONSTRAINT FOREIGN KEY l_col_size-row_id (col_id) REFERENCES rows (col_id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY l_col_size-size_id (size_id) REFERENCES sizes (size_id) ON UPDATE CASCADE ON DELETE CASCADE
);

Then:

foreach( $sizes as $size ) {
  $dbh->query("INSERT INTO sizes (size_name) VALUES ('{$size['name']}')");
  $size_id = $dbh->last_insert_id();

  foreach( $rows as $row ) {
    $dbh->query("INSERT INTO l_row_size (size_id, row_id) VALUES ($size_id, {$row['id']})");
  }

  foreach( $cols as $col ) {
    $dbh->query("INSERT INTO l_col_size (size_id, col_id) VALUES ($size_id, {$col['id']})");
  }
}

edit

Sharf commented:

Each Size Class can have many rows and many columns, but each row and column can only belong to one size class.

Well then you only need 3 tables:

CREATE TABLE sizes (
  size_id INTEGER UNSIGNED AUTO_INCREMENT,
  ...
  PRIMARY KEY(size_id)
);

CREATE TABLE rows (
  row_id INTEGER UNSIGNED AUTO_INCREMENT,
  size_id INTEGER UNSIGNED,
  ...
  PRIMARY KEY(row_id)
  CONSTRAINT FOREIGN KEY rows-size_id (size_id) REFERENCES sizes (size_id) ON UPDATE CASCADE ON DELETE CASCADE,
);

CREATE TABLE columns (
  col_id INTEGER UNSIGNED AUTO_INCREMENT,
  size_id INTEGER UNSIGNED,
  ...
  PRIMARY KEY(col_id),
  CONSTRAINT FOREIGN KEY cols-size_id (size_id) REFERENCES sizes (size_id) ON UPDATE CASCADE ON DELETE CASCADE,
);

Linking tables are only truly useful in the case of n:m relationships, and this is a case of two 1:n relationships. Each Row/Column object need only store a reference to the Size to which they belong.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 24, 2014 at 20:01

Sammitch

32.5k7 gold badges58 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sharf Over a year ago

That's not quite the way it works. Each Size Class can have many rows and many columns, but each row and column can only belong to one size class.

sharf Over a year ago

I may end up switching to that, but I'd still like to know how to set up the foreign keys properly.

Sammitch Over a year ago

@sharf think of it like a parent-child relationship. The parent table operates independent of the child table. The child table defines a foreign key constraint to "attach" to the referenced key in the parent. When a row in the child is updated/deleted, nothing happens to the parent. When a row in the parent is updated/deleted the change propagates to the child table according to the ON UPDATE/DELETE rules set by the foreign key. The original set of tables I had given more or less match the 5 you have defined, as do their foreign keys. This schema will work, but it is needlessly complex.

sharf Over a year ago

I understand that, but I can't seem to get that to work. I tried making the link tables children of the size class table, and the row/col tables children of the link tables. Unfortunately that caused errors when inserting into the row/col tables. There is likely to be expansion on this functionality in the near future, which is why I'd like to keep the link tables separate.

Sammitch Over a year ago

@sharf think about the relationship. When the Sizes table is changed that should chain down to the linking tables, and then that should chain down to the Rows/Columns tables. So the Rows/Columns tables should have FKs that reference their linking table, and the linking tables should have FKs that reference the Sizes table.

|

Collectives™ on Stack Overflow

MySQL innoDB foreign key delete cascade from three tables

1 Answer 1

edit

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

edit

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related