Ok, so my problem is inserting the initial "base" record into my classes table. Here is the code along with the received error. It looks ok from what I see, and this error has been killing me. I want to keep the foreign key constraints. Any suggestions would be useful. Thanks.
drop table if exists classes;
create table classes
id int unsigned not null auto_increment,
name varchar(50) not null,
parent_class_id int unsigned not null default 0,
foreign key(parent_class_id) references classes(id),
This would bee the "base" class that all other classes inherit from by default.
The problem is creating this first record with the foreign key value referring to the same primary key value, and both being created at the same time.
insert into classes
values(0,'base','description blah blah....',0);
These are the following results
Query OK, 0 rows affected (0.34 sec)
Query OK, 0 rows affected (0.19 sec)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`proj`.`classes`, CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`parent_class_id`) REFERENCES `classes` (`id`))
Right, that's what I figured. It had something to do with referring to the 0 value that hadn't been committed yet. I just didn't know that the foreign key could be a null value. I thought it always had to make a valid reference to another record. Well, that simplified things a whole lot and will actually work out better. Thanks for your time and help.