MySQL RSS MySQL RSS

Foreigb Key Constraint Failing

Thread Solved

•
•
Join Date: Feb 2008
Posts: 6
Reputation: bernardf is an unknown quantity at this point 
Solved Threads: 0
bernardf bernardf is offline Offline
Newbie Poster

Foreigb Key Constraint Failing

 
0
  #1
Apr 3rd, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 790
Reputation: darkagn has a spectacular aura about darkagn has a spectacular aura about darkagn has a spectacular aura about 
Solved Threads: 109
darkagn's Avatar
darkagn darkagn is offline Offline
Master Poster

Re: Foreigb Key Constraint Failing

 
0
  #2
Apr 3rd, 2009
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.
There are no stupid questions, only those too stupid to ask for help.
echo is a web developer's best friend.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 6
Reputation: bernardf is an unknown quantity at this point 
Solved Threads: 0
bernardf bernardf is offline Offline
Newbie Poster

Re: Foreigb Key Constraint Failing

 
0
  #3
Apr 3rd, 2009
•
•
•
•
Originally Posted by darkagn View Post
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 790
Reputation: darkagn has a spectacular aura about darkagn has a spectacular aura about darkagn has a spectacular aura about 
Solved Threads: 109
darkagn's Avatar
darkagn darkagn is offline Offline
Master Poster

Re: Foreigb Key Constraint Failing

 
0
  #4
Apr 3rd, 2009
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!
Last edited by darkagn; Apr 3rd, 2009 at 1:08 am.
There are no stupid questions, only those too stupid to ask for help.
echo is a web developer's best friend.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 6
Reputation: bernardf is an unknown quantity at this point 
Solved Threads: 0
bernardf bernardf is offline Offline
Newbie Poster

Re: Foreigb Key Constraint Failing

 
0
  #5
Apr 3rd, 2009
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:

MySQL Syntax (Toggle Plain Text) 
  1. INSERT INTO t1 (OpenDT) VALUES (NOW());

I get the above error.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 790
Reputation: darkagn has a spectacular aura about darkagn has a spectacular aura about darkagn has a spectacular aura about 
Solved Threads: 109
darkagn's Avatar
darkagn darkagn is offline Offline
Master Poster

Re: Foreigb Key Constraint Failing

 
0
  #6
Apr 3rd, 2009
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
There are no stupid questions, only those too stupid to ask for help.
echo is a web developer's best friend.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 6
Reputation: bernardf is an unknown quantity at this point 
Solved Threads: 0
bernardf bernardf is offline Offline
Newbie Poster

Re: Foreigb Key Constraint Failing

 
0
  #7
Apr 3rd, 2009
*************************** 1. row ***************************
Table: t1
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE: CREATE TABLE `t1` (
  2.   `ID` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `Processed` ENUM('Y','N') NOT NULL DEFAULT 'N',
  4.   `OpenDT` DATETIME NOT NULL,
  5.   `CloseDT` DATETIME DEFAULT NULL,
  6.   `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
  7.   PRIMARY KEY  (`ID`),
  8.   CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `t1` (`ID`)
  9. ) ENGINE=INNODB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ALSO:

*************************** 1. row ***************************
Table: t2
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE: CREATE TABLE `t2` (
  2.   `ID` INT(11) NOT NULL,
  3.   `FileID` INT(11) NOT NULL AUTO_INCREMENT,
  4.   `FileDir` VARCHAR(120) NOT NULL,
  5.   `FileName` VARCHAR(120) NOT NULL,
  6.   `ratRef` INT(11) DEFAULT NULL,
  7.   `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
  8.   PRIMARY KEY  (`FileID`),
  9.   KEY `ID` (`ID`),
  10.   CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `pdcBatch` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
  11. ) 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.
Last edited by peter_budo; Apr 5th, 2009 at 4:44 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2007
Posts: 790
Reputation: darkagn has a spectacular aura about darkagn has a spectacular aura about darkagn has a spectacular aura about 
Solved Threads: 109
darkagn's Avatar
darkagn darkagn is offline Offline
Master Poster

Re: Foreigb Key Constraint Failing

 
0
  #8
Apr 3rd, 2009
> 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.
Last edited by darkagn; Apr 3rd, 2009 at 1:59 am.
There are no stupid questions, only those too stupid to ask for help.
echo is a web developer's best friend.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the MySQL Forum
Thread Tools Search this Thread



agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal dui ec2 email enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop legal license licensing linux maintenance mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php priceupdating query referencedesign reorderingcolumns saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC