MySQL foreign keys with non-identifying relationships

All I need is to create 2 tabeles with next structure:

The SQL:

CREATE TABLE IF NOT EXISTS `ds_cats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `module_news_cats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_news_cats_module_news_cats` (`parent`),
  KEY `fk_module_news_cats_ds_cats1` (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `module_news_cats`
  ADD CONSTRAINT `fk_module_news_cats_ds_cats1` FOREIGN KEY (`cat_id`) REFERENCES `ds_cats` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  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;

But when I try to insert first row to my table "module_news_cats", I recive next error:

#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)

Question: How I can create table which will have an index with non-identifying relationship to the anther index in the same table? Some rows will have parents, and some not.

---

I think you just need to allow NULLs in module_news_cats.parent:

CREATE TABLE IF NOT EXISTS `module_news_cats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NULL,               -- Change this
  `cat_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_module_news_cats_module_news_cats` (`parent`),
  KEY `fk_module_news_cats_ds_cats1` (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

and then if there isn't a parent, create the row with a NULL in parent.

---

Your 'parent' field cannot be empty (NULL) if you insert a record, which means that every record you insert should refer to a parent ID (which is impossible if there are no entries in your table yet).

If you make the 'parent' field in the module_news_cats table nullable:

ALTER TABLE `module_news_cats` CHANGE `parent` `parent` INT( 11 ) NULL DEFAULT NULL

you should be able to insert records that have no parent ID associated (just supply NULL instead of a value).

---

You could make the parent column in the module_news_cats table nullable.

Then for rows that have no parents populate the parent column with null.