vB9Quupr

1MySQL foreign keys with non-identifying relationships
2CREATE TABLE IF NOT EXISTS `ds_cats` (
3  `id` int(11) NOT NULL AUTO_INCREMENT,
4  PRIMARY KEY (`id`)
5) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
6
7CREATE TABLE IF NOT EXISTS `module_news_cats` (
8  `id` int(11) NOT NULL AUTO_INCREMENT,
9  `parent` int(11) NOT NULL,
10  `cat_id` int(11) NOT NULL,
11  PRIMARY KEY (`id`),
12  KEY `fk_module_news_cats_module_news_cats` (`parent`),
13  KEY `fk_module_news_cats_ds_cats1` (`cat_id`)
14) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
15
16ALTER TABLE `module_news_cats`
17  ADD CONSTRAINT `fk_module_news_cats_ds_cats1` FOREIGN KEY (`cat_id`) REFERENCES `ds_cats` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
18  ADD CONSTRAINT `fk_module_news_cats_module_news_cats` FOREIGN KEY (`parent`) REFERENCES `module_news_cats` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
19	
20#1452 - Cannot add or update a child row: a foreign key constraint fails (`empty`.`module_news_cats`, CONSTRAINT `fk_module_news_cats_module_news_cats` FOREIGN KEY (`parent`) REFERENCES `module_news_cats` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
21	
22CREATE TABLE IF NOT EXISTS `module_news_cats` (
23  `id` int(11) NOT NULL AUTO_INCREMENT,
24  `parent` int(11) NULL,               -- Change this
25  `cat_id` int(11) NOT NULL,
26  PRIMARY KEY (`id`),
27  KEY `fk_module_news_cats_module_news_cats` (`parent`),
28  KEY `fk_module_news_cats_ds_cats1` (`cat_id`)
29) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
30	
31ALTER TABLE `module_news_cats` CHANGE `parent` `parent` INT( 11 ) NULL DEFAULT NULL