MySQL Constraint is not adding correctly

Question

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.

mvp · Accepted Answer · 2013-05-24 07:47:34Z

1

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.

answered May 24, 2013 at 7:47

mvp

118k15 gold badges132 silver badges155 bronze badges

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

4 Comments

pogeybait Over a year ago

Thanks @mvp. I will give that a shot and report back.

pogeybait Over a year ago

Now I'm getting the dreaded #1005 - Can't create table 'xxxxxx.#sql-43df_22068' (errno: 150). Suggestions?

mvp Over a year ago

This error may happen if you create FK to table which does not exist yet, or index that FK is referring to is not created yet. I suggest editing your question to provide more info - we cannot read your mind

pogeybait Over a year ago

I wont split hairs but I gave you everything above in my original question. Thanks for your help and I solved the issue with the 1005. Apparently some tables had unsigned ints for the id while others did not. Have to match those up.

Collectives™ on Stack Overflow

MySQL Constraint is not adding correctly

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related