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  |
 No real name
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 #656939 is a reply to message #656553] |
Tue, 01 March 2011 06:05  |
No real name
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 #657239 is a reply to message #657094] |
Wed, 02 March 2011 05:27 |
No real name
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.
|
|
| | | |
Goto Forum:
Current Time: Fri Apr 19 20:01:17 GMT 2024
Powered by FUDForum. Page generated in 0.03709 seconds |
] 
Search
Help
Register
Login
Home
