SQL Error: 1452, SQLState: 23000 | Cannot add or update a child row: a foreign key constraint fails

Question

I've created a web application using spring, hibernate and mysql. I have two tables user, user_role. username in user table is unique and in user_role table it is a foreign key.

When I try to insert data in user_role table I am getting the below exception. But I am able to insert records manually in mysql.

It worked perfectly when I had username as primary key in user table. After changing the username to unique and user_id to primary I am getting this exception (See table structure below)

@Override
public boolean addProfile(User user) {
    UserRole userRole = new UserRole();
    userRole.setRole("ROLE_USER");
    userRole.setUser(user);

    boolean status = false;
    try {
        this.sessionFactory.getCurrentSession().save(user);
        this.sessionFactory.getCurrentSession().save(userRole); //getting exception here
        status = true;

I'm getting the following exception while saving the userRole. this.sessionFactory.getCurrentSession().save(userRole);

Exception

DEBUG: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - could not execute statement [n/a] com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (mydb.user_role, CONSTRAINT fk_user_user_role_username FOREIGN KEY (username) REFERENCES user (username) ON DELETE NO ACTION ON UPDATE NO ACTION)

WARN : org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1452, SQLState: 23000 ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Cannot add or update a child row: a foreign key constraint fails (mydb.user_role, CONSTRAINT fk_user_user_role_username FOREIGN KEY (username) REFERENCES user (username) ON DELETE NO ACTION ON UPDATE NO ACTION) ERROR: com.javaworkspace.nn.dao.impl.RegisterDAOImpl - org.hibernate.exception.ConstraintViolationException: could not execute statement DEBUG: org.hibernate.engine.transaction.spi.AbstractTransactionImpl - committing

user table

user_id int(11) NO  PRI     auto_increment
username    varchar(100)    NO  UNI     
password    varchar(60) NO

user_role table

user_role_id    int(11) NO  PRI     auto_increment
username    varchar(100)    NO  MUL     
role    varchar(45) NO

User.java

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "user_id", unique = true, nullable = false)
private String userId;

@Column(name = "USERNAME", unique = true, nullable = false, length = 100)
private String username;

UserRole.java

@Entity
@Table(name = "user_role", catalog = "mydb", uniqueConstraints = @UniqueConstraint(columnNames = {
        "role", "username" }))
public class UserRole {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "user_role_id", unique = true, nullable = false)
private Integer userRoleId;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "username", nullable = false)
private User user;

@Column(name = "role", nullable = false, length = 45)
private String role;

Thiagarajan Ramanathan Thiagarajan Ramanathan · Accepted Answer · 2015-09-11T02:26:07

I found the solution

Since I mapped a foreign key that points to a unique key. We need to tell Hibernate about the User columns you are referring to (if it's not a primary key). To do this, we have to add referencedColumnName in the second entity:

To resolve the issue I added referencedColumnName = "username" in UserRole.java

UserRole.java

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "username", nullable = false, referencedColumnName = "username")
private User user;

SQL Error: 1452, SQLState: 23000 | Cannot add or update a child row: a foreign key constraint fails

1 Answers