MySQL - foreign key constrained by primary key in same table, error #1452 -
i created table cat_parent_id
foreign key constrained primary key cat_id
, using this:
create table categories ( cat_id smallint not null auto_increment primary key, cat_parent_id smallint, cat_name varchar(40) index cat_id(cat_id), foreign key(cat_id) references categories(cat_id), );
when try insert record cat_parent_id
null
, error:
#1452 - cannot add or update child row: foreign key constraint fails (`mydatabase`.`categories`, constraint `categories_ibfk_1` foreign key (`cat_id`) references `categories` (`cat_id`))
how can foreign key constraint fail when there no foreign key begin with? constraint possible if null not allowed?
i can insert records if disable constraint, not want. need parent_id
optional, , if has value existing cat_id
only
this create circular foreign key, prevent insertion table.
foreign key(cat_id) references categories(cat_id),
edit: perhaps meant put pk on cat_parent_id
?