CODEWITHSUNDEEP
mysqlconstraints
pogeybait
pogeybait

Reputation: 3145

MySQL Constraint is not adding correctly

I never have used constraints like I should have been over the years and I am trying to start doing that. I have a simple lookup table that relates garment id's and color id's. I obviously want the associations to be deleted if either the garment or the the color is deleted. Here is my table structures:

CREATE TABLE IF NOT EXISTS `colors` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `image` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

CREATE TABLE IF NOT EXISTS `garments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `image` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

CREATE TABLE IF NOT EXISTS `garments_colors` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `garment_id` int(11) unsigned NOT NULL,
    `color_id` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100 ;

The constraint I am trying to add is the following:

ALTER TABLE `garments_colors`
    ADD CONSTRAINT `garments_colors_ibfk_1` FOREIGN KEY (`garment_id`) REFERENCES `garments` (`id`) ON DELETE CASCADE,
    ADD CONSTRAINT `garments_colors_ibfk_2` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`) ON DELETE CASCADE;

The query runs successfully but when I do a visual export of all my tables near the bottom I do not see my new constraint(just existing ones) and the garments_colors table has the two fk indexes showing (which isnt typical when there is a constraint in place). Here is what the garments color table looks like after I run the alter table query to try to add the constraints:

CREATE TABLE IF NOT EXISTS `garments_colors` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `garment_id` int(11) unsigned NOT NULL,
    `color_id` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `garments_colors_ibfk_1` (`garment_id`),
    KEY `garments_colors_ibfk_2` (`color_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100 ;

Can someone please tell me what I'm doing wrong and why it wont add the constraint? Thanks in advance.

Upvotes: 0

Views: 65

Answers (1)

mvp
mvp

Reputation: 116177

In order for foreign keys to work, all tables involved must have Innodb engine. Only one of your tables is Innodb, while others are MyISAM - and this will not work (read more about this in MySQL documentation).

Simple solution for you is to change all your tables to Innodb format.

Upvotes: 1

Related Questions