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?


Popular posts from this blog

c# - ODP.NET Oracle.ManagedDataAccess causes ORA-12537 network session end of file -

matlab - Compression and Decompression of ECG Signal using HUFFMAN ALGORITHM -

utf 8 - split utf-8 string into bytes in python -