Sever Error: Cannot update child

[Luis Villamarin]

Hello,

I'm trying to store data to from a registration form (html). everything runs smoothly until I try to store the the Foreign Keys. When I test the form I get the following error:

SEVERE: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ebooksalesdb_schema`.`user`, CONSTRAINT `fk_user_account_type1` FOREIGN KEY (`account_type_account_name`) REFERENCES `account_type` (`account_name`) ON DELETE NO ACTION ON UPDATE NO ACTION)

After running the application the stack trace indicates that the error is located at:
at eBooks.data.UserDB.insert(UserDB.java:47)
at eBooks.controller.RegisterUserServlet.doPost(RegisterUserServlet.java:71)


This is the code for the RegisterUserServlet:

package eBooks.controller;

import com.sun.xml.registry.uddi.bindings_v2_2.Phone;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.RequestDispatcher;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.Cookie;

import eBooks.business.User;
import eBooks.business.Account;
import eBooks.data.UserDB;
import java.util.Date;


/**
 * @author L10
 */

public class RegisterUserServlet extends HttpServlet
{

    public void doPost(HttpServletRequest request,
                       HttpServletResponse response)
                       throws IOException, ServletException
    {
        HttpSession session = request.getSession();
        
        String fName = request.getParameter("fName");
        String lName = request.getParameter("lName");
        String emailAddress = request.getParameter("emailAddress");
        String password = request.getParameter("password");
        String dateOfBirth = request.getParameter("dateOfBirth");
        String phone = request.getParameter("phone");
        String address = request.getParameter("address");   
        String city = request.getParameter("city");
        String country = request.getParameter("country");
        String state = request.getParameter("state"); 
        String zip = request.getParameter("zip");
        String userRole = "User";
        String accountName = request.getParameter("accountType");
        
        User user = new User();
        
            user.setfName(fName);
            user.setlName(lName);
            user.setEmailAddress(emailAddress);
            user.setPassword(password);
            user.setDateOfBirth(dateOfBirth);
            user.setPhone(phone);
            user.setAddress(address);
            user.setCity(city);
            user.setCountry(country);
            user.setState(state);
            user.setZipcode(zip);
        
            user.setUserRole(userRole);
        
        Account account = new Account();
        
            account.setAccountName(accountName);
        
        // Add information to the database
        
        if(UserDB.emailExists(emailAddress))
            UserDB.update(user, account);
        else
            UserDB.insert(user, account);      
      
        session.setAttribute("User", user);
        
        Cookie emailCookie = new Cookie("emailCookie", emailAddress);
        emailCookie.setMaxAge(60*60*24*365*2);
        emailCookie.setPath("/");
        response.addCookie(emailCookie);
        
        String url = "/WEB-INF/view/registration_confirmation.jsp";
        RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(url);
        dispatcher.forward(request, response);
       
        
    }
            
    
}

This is the code for the UserDB class:

package eBooks.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import eBooks.business.User;
import eBooks.business.Account;
import eBooks.util.DBUtil;

/**
 * @author L10
 */

public class UserDB {
    
    public static int insert(User user, Account account)
    {
        
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        
         String query = 
                "INSERT INTO user (fName,lName, emailAddress, password, dateOfBirth, "
                +"phone, address, city, state, country, zip,  account_type_account_name, role_userRole) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                                         
         try
         {
             ps = connection.prepareStatement(query);
             ps.setString(1, user.getfName());
             ps.setString(2, user.getlName());
             ps.setString(3, user.getEmailAddress());
             ps.setString(4, user.getPassword());
             ps.setString(5, user.getDateOfBirth());
             ps.setString(6, user.getPhone());
             ps.setString(7, user.getAddress());
             ps.setString(8, user.getCity());
             ps.setString(9, user.getState());
             ps.setString(10, user.getCountry());
             ps.setString(11, user.getZipcode());
             ps.setString(12, account.getAccountName());
             ps.setString(13, user.getUserRole());
             
             return ps.executeUpdate();
            
         }
         catch(SQLException e)
         {
             e.printStackTrace();
             return 0;
         }
         finally
         {
             DBUtil.closePreparedStatement(ps);
             pool.freeConnection(connection);
         }
        
    }
    
    
    public static int update(User user, Account account)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        
        String query = "UPDATE user SET " 
                        + "fName = ?, "
                        + "lName = ?, "
                        + "password = ?, "
                        + "dateOfBirth = ?, "
                        + "phone = ?, "
                        + "address = ?, "
                        + "city = ?, "
                        + "state = ?, "
                        + "country = ?, "
                        + "zip = ?, "
                        + "account_type_account_name = ?, "
                        + "role_userRole = ? "
                        + "WHERE emailAddress = ?";   
                   
        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, user.getfName());
            ps.setString(2, user.getlName());
            ps.setString(3, user.getEmailAddress());
            ps.setString(4, user.getPassword());
            ps.setString(5, user.getDateOfBirth());
            ps.setString(6, user.getPhone());
            ps.setString(7, user.getAddress());
            ps.setString(8, user.getCity());
            ps.setString(9, user.getState());
            ps.setString(10, user.getCountry());
            ps.setString(11, user.getZipcode());
            ps.setString(12, account.getAccountName());
            ps.setString(13, user.getUserRole());
                        
            return ps.executeUpdate();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return 0;
        }
        finally
        {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
                
    }
    
    
    public static int delete(User user, Account account)
    {
       ConnectionPool pool = ConnectionPool.getInstance();
       Connection connection = pool.getConnection();
       PreparedStatement ps = null;
       
       String query = "DELETE FROM user " +
                      "WHERE emailAddress = ?";
       try
       {
           ps = connection.prepareStatement(query);
           ps.setString(1, user.getEmailAddress());
           ps.setString(2, account.getAccountName());
           ps.setString(3, user.getUserRole());
           
           
           return ps.executeUpdate();
           
       }
       catch(SQLException e)
       {
           e.printStackTrace();
           return 0;
       }
       finally
       {
           DBUtil.closePreparedStatement(ps);
           pool.freeConnection(connection);
       }
    }
    
    
    public static boolean emailExists(String emailAddress)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        String query = "SELECT emailAddress FROM user "+
                       "WHERE emailAddress = ?";
        
        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();
            return rs.next();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return false;            
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }
    
    public static User selectUser(String emailAddress)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        String query = "SELECT * FROM  user "+
                       "WHERE emailAddress = ?";
        
        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();
            
            User user = null;
            Account account = null;
            
            if (rs.next())
            {
                user = new User();
                account = new Account();
                                
                user.setfName(rs.getString("fName"));
                user.setlName(rs.getString("lName"));
                user.setEmailAddress(rs.getString("emailAddress"));
                user.setPassword(rs.getString("password"));
                user.setDateOfBirth(rs.getString("dateOfBirth"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setCity(rs.getString("city"));
                user.setState(rs.getString("state"));
                user.setCountry(rs.getString("country"));
                user.setZipcode(rs.getString("zip"));
                account.setAccountName(rs.getString("account_type_account_name"));
                user.setUserRole(rs.getString("account_role_userRole"));
                
                
                
            }
            return user;            
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
            
    }
    
}

[Rob Spoor]

Luis Villamil wrote:CONSTRAINT `fk_user_account_type1` FOREIGN KEY (`account_type_account_name`) REFERENCES `account_type` (`account_name`)

That part means that each value that you put into the account_type_account_name column of this table, must have a matching value in the account_name column of the account_type table. Make sure that's the case.

[Luis Villamarin]

Could this be that the FK was not properly created when the DB/Table was created?

[Wendy L Gibbons]

Luis Villamil wrote:Could this be that the FK was not properly created when the DB/Table was created?

quite likely, we have given you the name and the contents of the index, go and investigate your database see if that is what was wanted on it.

[Luis Villamarin]

Hi I worked on the database and effectively there was a relationship that was not correctly set. However, what I did, didn't fully fixed the problem this what I'm getting now as an error:


SEVERE: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ebooksalesdb_schema`.`user`, CONSTRAINT `fk_user_account1` FOREIGN KEY (`account_account_id`, `account_account_type_account_name`) REFERENCES `account` (`account_id`, `account_type_account_na)

I noticed that at the end of the error it says `account_type_account_na, instead of `account_type_account_name'). I checked for the name inconsistency and couldn't find anywhere that `account_type_account_na. Could this be the reason ?


I rebuilt the database and I'm still getting the same error.

Do you have idea why this keeps on happening?