Foreign key violation error on row insert

Question

While a am trying to insert values to following table via jdbc i am getting error

Database schema

CREATE TABLE student_contact_details_t(
    student_contact_details_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    student_id MEDIUMINT NOT NULL ,
    contact_relation_type_id MEDIUMINT NOT NULL,
    name varchar(255),
    addresss varchar(255),
    phone_no varchar(255),
    created_by varchar(255),
    created_date DATE,
    modified_by varchar(255),
    modified_date DATE,
    PRIMARY KEY(student_contact_details_id),
    FOREIGN KEY(student_id) REFERENCES student_details_t(student_id),
    FOREIGN KEY(contact_relation_type_id) REFERENCES contact_relation_type_t(contact_relation_type_id)
);

Code

public void saveStudentContactDetails(
        StudentContactDetailsTO studentContactDetails)
        throws ClassNotFoundException, SQLException, IOException {
    try {
        int autoId = 0;
        String Insertquery = "insert into student_contact_details_t(student_id,contact_relation_type_id,name,addresss,phone_no,created_by,created_date,modified_by,modified_date)values(?,?,?,?,?,?,?,?,?)";
        PreparedStatement prepareStatement = JDBCConnectionUtil
                .getConnection().prepareStatement(Insertquery,
                        Statement.RETURN_GENERATED_KEYS);
        ResultSet studentId = prepareStatement.getGeneratedKeys();

        prepareStatement.setInt(1, studentContactDetails.getStudentId());

        // prepareStatement.setInt(2,studentId.getInt(1));
        prepareStatement.setInt(2, autoId);
        prepareStatement.setString(3, studentContactDetails.getName());
        prepareStatement.setString(4, studentContactDetails.getAddress());
        prepareStatement.setString(5, studentContactDetails.getPhoneNo());
        prepareStatement.setString(6, "sysadmin");
        prepareStatement.setString(7, DateUtil.getDate().format(date));
        prepareStatement.setString(8, "sysadmin");
        prepareStatement.setString(9, DateUtil.getDate().format(date));
        prepareStatement.executeUpdate();
        while (studentId.next()) {
            autoId = studentId.getInt(1);
            System.out.println(autoId);
        }
    } catch (ClassNotFoundException | SQLException | IOException e) {
        throw e;
    }
}

Exception

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`studentregistration`.`student_contact_details_t`, CONSTRAINT `student_contact_details_t_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student_details_t` (`student_id`))
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)

I'm confused. Your title says that you are getting an error loading the database config, but the exception says you are getting a constraint violation, which means that you are connected to your database OK. Which is it? — NickJ, Feb 19 '15 at 10:08
i 'm connecting with my data base .while trying use result set generated key to get parent table primary key and insert data to child table.i am getting this exception. — Murugesan Era, Feb 19 '15 at 11:43

singhakash · Answer 1 · 2015-02-19T10:41:09.137

1

The error says that you are inserting student_id in table student_contact_details_t that does not exist in student_details_t table voilating the foreign key constraint that you set up during student_contact_details_t table creation.

So you have to first insert in table student_details_t then insert in student_contact_details_t with student_id

edited Feb 19 '15 at 10:41

answered Feb 19 '15 at 10:27

singhakash

7,891
6
31
65

score 0 · Answer 2 · answered Feb 19 '15 at 10:30

You have to validate that the student id exist in the student table student_details_t.

If the student doesn't exist in student_details_t then you can't insert in the related table student_contact_details_t.

In this scenario you should tell the user that "student XXX doesn't exist".

Foreign key violation error on row insert

2 Answers2