Problem with a Case of PKEY AND FKEY within the same table

I have a table which has a field ID as a primary key and another field PID as the Foreign Key to the ID Field. Both are of long DataTypes.

So this is my table struct

CREATE TABLE `myobj` (
`ID` BIGINT(100) NOT NULL AUTO_INCREMENT,
`FRIENDLY_NAME` VARCHAR(100) DEFAULT NULL,
`PARENT_ID` BIGINT(100) DEFAULT NULL,
`PARENT` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `PARENT_ID` (`PARENT_ID`),
CO开发者_运维百科NSTRAINT `myobj_ibfk_1` FOREIGN KEY (`PARENT_ID`) REFERENCES `myobj` (`ID`) ON DELETE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

I use Hibernate as ORM to insert into the MySQL DB. The problem is

When One or more of the records which will be top level with no parent id, an error occurs with insert top-level records. It looks like in case of no parent id it will accept only NULL.

But from Java Application code perspective, the datatype is long and hence when i insert an object through Hibernate, with default value as 0 ,it looks like a constraint violation

Cannot add or update a child row: a foreign key constraint fails (genericdb.myobj, CONSTRAINT myobj_ibfk_1 FOREIGN KEY (PARENT_ID) REFERENCES myobj (ID) ON DELETE CASCADE)

This problem occurs also when setting 0 as the value thru sql CLI.

Second Secnario:

Also tried setting the Default Value for PARENT_ID as 0.

CREATE TABLE `myobj` (
`ID` BIGINT(100) NOT NULL AUTO_INCREMENT,
`FRIENDLY_NAME` VARCHAR(100) DEFAULT NULL,
`PARENT_ID` BIGINT(100) DEFAULT '0',
`PARENT` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `PARENT_ID` (`PARENT_ID`),
CONSTRAINT `myobj_ibfk_1` FOREIGN KEY (`PARENT_ID`) REFERENCES `myobj` (`ID`) ON DELETE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

SO when i insert 0 into Parent_ID field, still error

Cannot add or update a child row: a foreign key constraint fails (genericdb.myobj, CONSTRAINT myobj_ibfk_1 FOREIGN KEY (PARENT_ID) REFERENCES myobj (ID) ON DELETE CASCADE)

Kindly let me know if anything is wrong here.

---

The problem is that you're trying to point to row in myobj with the id 0, which doesn't exist. The default for that column should be null.

I'm quite sure that if you run select * from myobj where id = 0, you won't get any results back.