Foreigb Key Constraint Failing

[bernardf]

HI there

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`database/t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `t1` (`ID`))

The field ID is an auto-increment field and I altered the field to a lower number and now I can't insert any rows into the table without getting the above error. I did not build this database so if anyone has any idea on what steps to correct this issue it would be greatly appreciated.

Thanks, Bernie.

[darkagn]

A foreign key constraint is used to stop you from inserting data into the table without a corresponding record existing in another table. The ID field in the table you are inserting into must match the ID field of a record in the table t1.

[bernardf]

A foreign key constraint is used to stop you from inserting data into the table without a corresponding record existing in another table. The ID field in the table you are inserting into must match the ID field of a record in the table t1.

This is what I find weird. The way I read the constraint is that the constraint is on it's own table and not on another table. In this case t1.ID

[darkagn]

So you are trying to insert to t1 and you get that error? I must admit I didn't think it was possible to have such a foreign key constraint as one that references itself. I think if that's the case then it would probably be best to drop the FK constraint from the table, just make sure you don't drop the primary key constraint if it is also the PK of the table.

EDIT: And remember to backup the database beforehand so you can go back if this is the wrong move!

[bernardf]

This may help:

+-----------+---------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Processed | enum('Y','N') | NO | | N | |
| OpenDT | datetime | NO | | | |
| CloseDT | datetime | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------+---------------+------+-----+-------------------+----------------+

This is the table with the constraint.

When I try:

insert into t1 (OpenDT) values (NOW());

I get the above error.

[darkagn]

Are you able to check the FK on that table? You can do this at the mysql prompt like so:

mysql > SHOW CREATE TABLE t1\G

[bernardf]

*************************** 1. row ***************************
Table: t1

Create Table: CREATE TABLE `t1` (
  `ID` int(11) NOT NULL auto_increment,
  `Processed` enum('Y','N') NOT NULL default 'N',
  `OpenDT` datetime NOT NULL,
  `CloseDT` datetime default NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `t1` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ALSO:

*************************** 1. row ***************************
Table: t2

Create Table: CREATE TABLE `t2` (
  `ID` int(11) NOT NULL,
  `FileID` int(11) NOT NULL auto_increment,
  `FileDir` varchar(120) NOT NULL,
  `FileName` varchar(120) NOT NULL,
  `ratRef` int(11) default NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`FileID`),
  KEY `ID` (`ID`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `pdcBatch` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

I just saw this other table referencing the ID field in a foreign key. Sorry about this, I really am at a loss with all this.

[darkagn]

> Sorry about this, I really am at a loss with all this.
No need to apologise, that's why this forum is here :)

The foreign key in table t1 is definately referencing itself. And the field in question is also the PK of the table. This is very very very bad. What this says is "before you insert a new record, make sure that this record already exists!" Of course this is impossible.

My normal course of action here would be to drop the foreign key constraint from the table. Are you able to speak with whoever built the database to find out their intention with this foreign key? They might be able to tell you why they created it as they might have meant something else. Maybe they actually wanted to reference table t2's ID or something...

As I said earlier, backup the database, drop the constraint and see if this fixes your problem. Good luck :)

EDIT: And btw, table t2's FK constraint is ok.