CODEWITHSUNDEEP
mysqldatabaseforeign-keys
Diego Vega
Diego Vega

Reputation: 223

mysql foreign key references same table field

I have this table

   CREATE TABLE `product_category` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `category_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `category_id` FOREIGN KEY (`category_id`) REFERENCES `product_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

What I'm trying to get is to delete on cascade every record which category_id fields equals to id field, but I cant insert any record, it gives me an error

cannot add or update a child row: a foreign key constraint fails('database/product_category', CONSTRAINT 'category_id FOREIGN KEY('category_id') REFERENCES product_category('id') ON DELETE CASCADE ON UPDATE CASCADE

Upvotes: 0

Views: 483

Answers (1)

Yuriy Novikov
Yuriy Novikov

Reputation: 321

Your foreigh key value must point to existing product_category (id), but there are no allowable rows in table. So make

category_id int(11) NULL,

instead your definition and insert first row like

INSERT INTO product_category SET name='test1';

Upvotes: 2

Related Questions