CODEWITHSUNDEEP
mysqlforeign-keys
user4035
user4035

Reputation: 23729

MySQL Multiple cascade foreign keys in 1 table

I use MySQL 5.1. Have a table with 2 foreign keys, referencing 2 different tables:

CREATE TABLE `words` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `words_groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `words_in_group` (
  `group_id` int(10) unsigned DEFAULT NULL,
  `word_id` int(10) unsigned DEFAULT NULL,
  KEY `word_id` (`word_id`) USING BTREE,
  KEY `group_id` (`group_id`) USING BTREE,
  CONSTRAINT `group_id` FOREIGN KEY (`group_id`) REFERENCES `words_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `word_id_fk` FOREIGN KEY (`word_id`) REFERENCES `words` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But for some reason the CASCADE property for one of the keys in words_in_group table is unset when I use phpMyAdmin or Navicat and check this table structure.

What can it be? How to solve? Maybe, use triggers?

Upvotes: 0

Views: 1156

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

Test the cascade by inserting some test data, then deleting a row from words.

You could also dump the database with mysqldump, and read what the database server thinks it's supposed to do.

I'm pretty sure you'll find that the cascades work as written, not as shown in the graphical interfaces.

Upvotes: 1

Related Questions