Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2.1.(trouble with tutorial,eclipselink2.1.2,jpa2.0,mysql)
Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2.1. [message #656553] Sat, 26 February 2011 17:47 Go to next message
No real name is currently offline No real nameFriend
Messages: 6
Registered: February 2011
Junior Member
Hi all,

I'm encountering a 1452 error on MySQL 5.5.9 while running some JPA tutorial code that I pulled off the EclipseLink website ( http://wiki.eclipse.org/EclipseLink/Examples/JPA/EmployeeXML), and I'm hoping someone has some insight.

I'm using the latest stable build for EclipseLink as of today from the main website: eclipselink-jpa-modelgen_2.1.2.v20101206-r8635.jar and javax.persistence_2.0.1.v201006031150.jar.

When I populate the database via CreateDatabase.java, I get the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViol ationException: Cannot add or update a child row: a foreign key constraint fails (jpatutorial.proj_emp, CONSTRAINT FK_PROJ_EMP_PROJ_ID FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID)) Error Code: 1452

The relevant models here are an Employee table and a Phone table. There is many-to-one relationship between Phones and Employees. Part of the orm.xml is below:

<entity class="model.PhoneNumber">
<table name="PHONE" />
<attributes>
.
.
<many-to-one name="owner">
<join-column name="EMP_PH_ID" />
</many-to-one>
</attributes>
</entity>

<entity class="model.Employee">
<!-- secondary-table name="SALARY" /-->
<attributes>
<id name="id">
<column name="EMP_ID" />
<generated-value />
</id>
.
.
<one-to-many name="phoneNumbers" mapped-by="owner">
<cascade>
<cascade-all />
</cascade>
<private-owned />
</one-to-many>
.
.
</attributes>
</entity>

The code successfully creates the tables, creates address records for the employees, creates all the employee records, but then fails on inserting a phone record even though it has a valid FK reference to a employee record. I simplified the code so that it executes, persists and commits everything up to the point of the failure, but whether I execute the insert through JPA or manually by running a SQL script, I end up with the same 1452 error.

Interestingly, I found that if I dropped all the related Employee entities, and recreate them off create table definitions reversed engineered by MySQL workbench, I am able to successfully insert records into all the tables through JPA and manually via scripts. So, this suggests Eclipselink is creating the tables in some way that is producing a problem, and MySQL Workbench isn't capturing these problematic definitions in the reverse engineering. At this point, I'm out of ideas.
Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #656786 is a reply to message #656553] Mon, 28 February 2011 15:30 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello,

In the sample app, Address doesn't have an Employee reference. The problem seems to be with the employee you are associating to the Address - specifically with that Employee and its Project's. How are you creating the Address and associating the Employee? And what does the Employee->Projects relationship look like?

You might also turn logging on using the property:
<property name="eclipselink.logging.level" value="FINE"/>
with values of FINE or FINEST to have the DDL and other SQL logged:
http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging

Best Regards,
Chris
Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #656939 is a reply to message #656553] Tue, 01 March 2011 06:05 Go to previous messageGo to next message
No real name is currently offline No real nameFriend
Messages: 6
Registered: February 2011
Junior Member
Hi Chris,

I pasted in the wrong error, which may have caused some problems. I removed the phone object inserts in the code earlier.... The error that I'm seeing looks like:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViol ationException: Cannot add or update a child row: a foreign key constraint fails (`jpatutorial`.`phone`, CONSTRAINT `FK_PHONE_EMP_PH_ID` FOREIGN KEY (`EMP_PH_ID`) REFERENCES `EMPLOYEE` (`EMP_ID`))
Error Code: 1452
Call: INSERT IGNORE INTO PHONE (PH_ID, TYPE, AREA_CODE, P_NUMBER, EMP_PH_ID) VALUES (?, ?, ?, ?, ?)
bind => [25, Work, 613, 5558812, 23]


The insert is valid as, the logging on finest also indicates that employee with id=23 was inserted.

Connection(876256661)--INSERT IGNORE INTO EMPLOYEE (EMP_ID, L_NAME, END_TIME, VERSION, START_TIME, GENDER, salary, F_NAME, MANAGER_ID, ADDR_ID, START_DATE, END_DATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
bind => [23, Saunders, null, 1, null, M, 0.0, Marcus, 4, 26, 1995-01-12, 2001-12-31]

According to the log, all the address are inserted successfully:

[EL Finer]: Connection(876256661)--begin transaction
[EL Finest]: Execute query InsertObjectQuery(Address(41:: 1111 Mooseland Rd., Calgary, AB, Canada, J5J2B5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [41, J5J2B5, 1111 Mooseland Rd., AB, Canada, Calgary]
[EL Finest]: Execute query InsertObjectQuery(Address(37:: 89 Chocolate Drive, Smith Falls, ONT, Canada, C6C6C6))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [37, C6C6C6, 89 Chocolate Drive, ONT, Canada, Smith Falls]
[EL Finest]: Execute query InsertObjectQuery(Address(11:: 1 Canadien Place, Montreal, QUE, Canada, Q2S5Z5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [11, Q2S5Z5, 1 Canadien Place, QUE, Canada, Montreal]
[EL Finest]: Execute query InsertObjectQuery(Address(17:: 20 Mountain Blvd., Floor 53, Suite 6, Vancouver, BC, Canada, N5J2N5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [17, N5J2N5, 20 Mountain Blvd., Floor 53, Suite 6, BC, Canada, Vancouver]
[EL Finest]: Execute query InsertObjectQuery(Address(26:: 234 Caledonia Lane, Perth, ONT, Canada, Y3Q2N9))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [26, Y3Q2N9, 234 Caledonia Lane, ONT, Canada, Perth]
[EL Finest]: Execute query InsertObjectQuery(Address(33:: 382 Hyde Park Blvd., Victoria, BC, Canada, Z5J2N5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [33, Z5J2N5, 382 Hyde Park Blvd., BC, Canada, Victoria]
[EL Finest]: Execute query InsertObjectQuery(Address(7:: 12 Merivale Rd., Suite 5, Ottawa, ONT, Canada, K5J2B5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [7, K5J2B5, 12 Merivale Rd., Suite 5, ONT, Canada, Ottawa]
[EL Finest]: Execute query InsertObjectQuery(Address(3:: 1450 Acme Cr., Suite 4, Toronto, ONT, Canada, L5J2B5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [3, L5J2B5, 1450 Acme Cr., Suite 4, ONT, Canada, Toronto]
[EL Finest]: Execute query InsertObjectQuery(Address(50:: 1112 Gold Rush Rd., Yellowknife, YK, Canada, Y5J2N5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [50, Y5J2N5, 1112 Gold Rush Rd., YK, Canada, Yellowknife]
[EL Finest]: Execute query InsertObjectQuery(Address(22:: 3254 Parkway Place, Prince Rupert, BC, Canada, K3K5D5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [22, K3K5D5, 3254 Parkway Place, BC, Canada, Prince Rupert]
[EL Finest]: Execute query InsertObjectQuery(Address(29:: 2 Anderson Rd., Metcalfe, ONT, Canada, Y4F7V6))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [29, Y4F7V6, 2 Anderson Rd., ONT, Canada, Metcalfe]
[EL Finest]: Execute query InsertObjectQuery(Address(46:: 1 Hawthorne Drive, Arnprior, ONT, Canada, W1A2B5))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO ADDRESS (ADDRESS_ID, P_CODE, STREET, PROVINCE, COUNTRY, CITY) VALUES (?, ?, ?, ?, ?, ?)
bind => [46, W1A2B5, 1 Hawthorne Drive, ONT, Canada, Arnprior]


The relationships involved here are:

one-to-one between Address and Employees
many-to-many between Projects and Employees
many-to-one between Phones and Employees (and vice versa)
many-to-one between Employees (managers and subordinates)
Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #657094 is a reply to message #656553] Tue, 01 March 2011 15:32 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Very odd. Can your include the SQL up to the error.

Also include the DDL. Ensure the Employee id is not created as an auto incrementing id.

If you don't insert the phones does it work? What if you insert a phone in a separate transaction?

What type of table are you using ISAM or InnoDB?


James : Wiki : Book : Blog : Twitter
Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #657239 is a reply to message #657094] Wed, 02 March 2011 05:27 Go to previous messageGo to next message
No real name is currently offline No real nameFriend
Messages: 6
Registered: February 2011
Junior Member
DDL for the Employee and Phone table is below:

CREATE TABLE `employee` (
`EMP_ID` int(11) NOT NULL,
`L_NAME` varchar(255) DEFAULT NULL,
`END_TIME` time DEFAULT NULL,
`VERSION` bigint(20) DEFAULT NULL,
`START_TIME` time DEFAULT NULL,
`GENDER` varchar(255) DEFAULT NULL,
`salary` double DEFAULT NULL,
`F_NAME` varchar(255) DEFAULT NULL,
`MANAGER_ID` int(11) DEFAULT NULL,
`ADDR_ID` int(11) DEFAULT NULL,
`START_DATE` date DEFAULT NULL,
`END_DATE` date DEFAULT NULL,
PRIMARY KEY (`EMP_ID`),
KEY `FK_EMPLOYEE_MANAGER_ID` (`MANAGER_ID`),
KEY `FK_EMPLOYEE_ADDR_ID` (`ADDR_ID`),
CONSTRAINT `FK_EMPLOYEE_ADDR_ID` FOREIGN KEY (`ADDR_ID`) REFERENCES `ADDRESS` (`ADDRESS_ID`),
CONSTRAINT `FK_EMPLOYEE_MANAGER_ID` FOREIGN KEY (`MANAGER_ID`) REFERENCES `EMPLOYEE` (`EMP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$


CREATE TABLE `phone` (
`PH_ID` int(11) NOT NULL,
`TYPE` varchar(255) NOT NULL,
`AREA_CODE` varchar(255) DEFAULT NULL,
`P_NUMBER` varchar(255) DEFAULT NULL,
`EMP_PH_ID` int(11) DEFAULT NULL,
PRIMARY KEY (`PH_ID`,`TYPE`),
KEY `FK_PHONE_EMP_PH_ID` (`EMP_PH_ID`),
CONSTRAINT `FK_PHONE_EMP_PH_ID` FOREIGN KEY (`EMP_PH_ID`) REFERENCES `EMPLOYEE` (`EMP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

As you can see I'm using InnoDB. I did modify the code, and try submitting the phone record in another transaction after committing the transaction that inserted the addresses and employees.

The SQL that results in the error is as follows. It occurs as the first phone record is being submitted:

L Finest]: Execute query InsertObjectQuery(Phone([Work] 613 - 5558812))
[EL Finest]: Execute query WriteObjectQuery(Employee(23:: Saunders, Marcus))
[EL Fine]: Connection(876256661)--INSERT IGNORE INTO PHONE (PH_ID, TYPE, AREA_CODE, P_NUMBER, EMP_PH_ID) VALUES (?, ?, ?, ?, ?)
bind => [25, Work, 613, 5558812, 23]
[EL Fine]: SELECT 1
[EL Warning]: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.2.v20101206-r8635): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViol ationException: Cannot add or update a child row: a foreign key constraint fails (`jpatutorial`.`phone`, CONSTRAINT `FK_PHONE_EMP_PH_ID` FOREIGN KEY (`EMP_PH_ID`) REFERENCES `EMPLOYEE` (`EMP_ID`))
Error Code: 1452
Call: INSERT IGNORE INTO PHONE (PH_ID, TYPE, AREA_CODE, P_NUMBER, EMP_PH_ID) VALUES (?, ?, ?, ?, ?)
bind => [25, Work, 613, 5558812, 23]
Query: InsertObjectQuery(Phone([Work] 613 - 5558812))
[EL Finer]: Connection(876256661)--rollback transaction
[EL Finer]: release unit of work
[EL Finer]: client released

When I remove the phone inserts, the code fails on inserting project objects.

Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #657240 is a reply to message #657239] Wed, 02 March 2011 05:38 Go to previous messageGo to next message
No real name is currently offline No real nameFriend
Messages: 6
Registered: February 2011
Junior Member
I'm currently running everything on Mac OS X 10.6.4 leopard, with java 1.6.0 update 20.

Running within the Eclipse Helios IDE Build id: 20100917-0705.

I haven't tried running this in another environment yet.

Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #657372 is a reply to message #657240] Wed, 02 March 2011 15:22 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello,

Is this DDL statement the Eclipse generated statement? The DDL posted looks like it might have been generated by hand - as the table names do not match the case used in the orm.xml mapping you posted. ie "TABLE `phone`" would have been "TABLE `PHONE`" in EclipseLink generated DDL.

If this was just not a cut and paste error, can you check if the EclipseLink generated DDL includes the ENGINE=InnoDB? If not already, it can be set using the
<property name="eclipselink.ddl.default-table-suffix" value="engine=InnoDB"/>
property to default on all tables, as described:
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_J PA_Development/Schema_Generation/Appending_strings_to_CREATE _TABLE_statements

Best Regards,
Chris

Re: Foreign Key Constraint Issue Involving One-To-Many and Many-To-One Relationship in EcliplseLink2 [message #657672 is a reply to message #657372] Thu, 03 March 2011 15:51 Go to previous message
No real name is currently offline No real nameFriend
Messages: 6
Registered: February 2011
Junior Member
The DDL that I pasted in earlier was generated by MySQL workbench. The DDL generated by the eclipselink logging is as follows:


CREATE TABLE PHONE (PH_ID INTEGER NOT NULL, TYPE VARCHAR(255) NOT NULL, AREA_CODE VARCHAR(255), P_NUMBER VARCHAR(255), EMP_PH_ID INTEGER, PRIMARY KEY (PH_ID, TYPE))

There is innodb suffix. I explicitly added the property

<property name="eclipselink.ddl.default-table-suffix" value="engine=InnoDB"/>

In the persistence.xml, but it doesn't seem to have any effect according to the logging which is set on Finest.
Previous Topic:generate entity classes from DB with TableGenerator
Next Topic:Dynamic Moxy accessing enum values
Goto Forum:
  


Current Time: Sun Dec 21 15:25:18 GMT 2014

Powered by FUDForum. Page generated in 0.01785 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software