How can I insert new records into mysql database without foreigin key constraint fail..

Question

0.00/5 (No votes)

See more:

I inserted first record without error. After first record, I faced the error as follow.

"Cannot add or update a child row: a foreign key constraint fails (`cims`.`hra_prm_claim_common`, CONSTRAINT `hra_prm_claim_common_applicationId` FOREIGN KEY (`applicationId`) REFERENCES `hra_prm_claim_application` (`applicationId`) ON DELETE CASCADE ON UPDAT); nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`cims`.`hra_prm_claim_common`, CONSTRAINT `hra_prm_claim_common_applicationId` FOREIGN KEY (`applicationId`) REFERENCES `hra_prm_claim_application` (`applicationId`) ON DELETE CASCADE ON UPDAT)"

I have three tables : 1 main table and 2 sub tables. for main table, insert data is ok. for sub tables, i face this error. my code for inserting two sub tables are as below..
Thank you all.

    SimpleDateFormat timeSDF = new SimpleDateFormat(propertiesConstants.getSimpleDateFormat());
HraPrmClaimCommon commonInsert = new HraPrmClaimCommon();
HraPrmClaimTransportation transportationInsert = new HraPrmClaimTransportation();
for(int i=0; i<uiPrmClaimTransportation.getTransportationRecordList().size(); i++) {
                commonInsert.setApplicationId(applicationId);
                commonInsert.setSeqNo(i+1);
                commonInsert.setStatus(propertiesConstants.getHraPrmClaimStatusCodePending());
                commonInsert.setTransactionDate(timeSDF.parse(uiPrmClaimTransportation.getTransportationRecordList().get(i).getTranscationDateInString()));
                commonInsert.setType(uiPrmClaimTransportation.getTransportationRecordList().get(i).getType());
                commonInsert.setAmount(new BigDecimal(Double.parseDouble(uiPrmClaimTransportation.getTransportationRecordList().get(i).getAmountInString())));
                if (uiPrmClaimTransportation.getTransportationRecordList().get(i).getUploadFile() != null && uiPrmClaimTransportation.getTransportationRecordList().get(i).getUploadFile().getSize() > 0) {
                    if (!uiPrmClaimTransportation.getTransportationRecordList().get(i).getUploadFile().getOriginalFilename().toLowerCase().endsWith(propertiesConstants.getHraFileUploadExtension()))
                        throw new CoreKnownException(Constants.ERROR_CODE_ER0047, Constants.ERROR_CODE_ER0047);
                    commonInsert.setAttachedBlob(new SerialBlob(uiPrmClaimTransportation.getTransportationRecordList().get(i).getUploadFile().getBytes()));
                }
                hraPrmClaimCommonDao.insert(commonInsert);

                transportationInsert.setApplicationId(applicationId);
                transportationInsert.setSeqNo(i+1);
                transportationInsert.setStartLocation(uiPrmClaimTransportation.getTransportationRecordList().get(i).getStartLocation());
                transportationInsert.setEndLocation(uiPrmClaimTransportation.getTransportationRecordList().get(i).getEndLocation());
                transportationInsert.setReason(uiPrmClaimTransportation.getTransportationRecordList().get(i).getReason());
                hraPrmClaimTransportationDao.insert(transportationInsert);
        }

Java

<pre lang="java">

Posted 21-Jan-15 17:34pm

stitchypoohpika

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-01-21T17:42:00

Solution 1

Based on the error the record you're trying to add to table hra_prm_claim_common has a value in applicationId field that does not exist in hra_prm_claim_application table in applicationId field at the time of insertion.

Few things to check:
- are you doing things in the right order, first add parent row then children
- if the applicationid comes from the database, do you fetch the value
- if you assign the value, do you use the same value in both inserts
- that you run both inserts in the same transaction or the transaction for the parent table insert has already been committed

Posted 21-Jan-15 17:42pm

Wendelius

Updated 21-Jan-15 17:45pm

v3

Comments

stitchypoohpika 22-Jan-15 2:27am

I had the following coding for main table before adding these sub tables.

HraPrmClaimApplication claimApplication=new HraPrmClaimApplication();

claimApplication.setCreateBy(creatorId);
claimApplication.setCreateOn(new Date());
claimApplication.setApplicationDate(new Date());
claimApplication.setApplicant(creatorId);
claimApplication.setAo(uiPrmClaimTransportationRecord.getAo());
claimApplication.setVo(uiPrmClaimTransportationRecord.getVo());
claimApplication.setFo(uiPrmClaimTransportationRecord.getFo());
claimApplication.setVehicleNo(uiPrmClaimTransportationRecord.getVehicleNo());
Integer applicationId = hraPrmClaimApplicationDao.insertAndReturnPrimaryKey(claimApplication);
if (applicationId == null)
throw new CoreException("applicationId object is null", Constants.ERROR_CODE_ER0999);

Wendelius 22-Jan-15 3:51am

Without going through all your code it's quite impossible to say if that code excerpt fine or not. I suggest going thruogh the code using debugger and to see what are the values you're trying to add to the tables and in which order.

stitchypoohpika 23-Jan-15 22:07pm

thanks for your help,Mika. I had solved this problem :)

Wendelius 24-Jan-15 2:46am

Glad you got it solved :)