[SOLVED] Hibernate @OneToOne cannot add or update a child row: a foreign key constraint

Issue

I am using JPA, Hibernate, Spring and MySQL

Cannot add or update a child row: a foreign key constraint fails (db1.stock_detail, CONSTRAINT FK_STOCK_ID FOREIGN KEY (STOCK_ID) REFERENCES stock (STOCK_ID))

Create script

 CREATE TABLE IF NOT EXISTS `stock` (
  `STOCK_ID` int(10) unsigned NOT NULL,
  `STOCK_CODE` varchar(10) NOT NULL,
  `STOCK_NAME` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `stock_detail` (
  `STOCK_ID` int(10) unsigned NOT NULL,
  `COMP_NAME` varchar(100) NOT NULL,
  `COMP_DESC` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8;

ALTER TABLE `stock`
  ADD PRIMARY KEY (`STOCK_ID`) USING BTREE,
  ADD UNIQUE KEY `UNI_STOCK_NAME` (`STOCK_NAME`),
  ADD UNIQUE KEY `UNI_STOCK_CODE` (`STOCK_CODE`) USING BTREE;

ALTER TABLE `stock_detail`
  ADD PRIMARY KEY (`STOCK_ID`) USING BTREE;

ALTER TABLE `stock`
  MODIFY `STOCK_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=86;

ALTER TABLE `stock_detail`
  MODIFY `STOCK_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=72;

ALTER TABLE `stock_detail`
  ADD CONSTRAINT `FK_STOCK_ID` FOREIGN KEY (`STOCK_ID`) REFERENCES `stock` (`STOCK_ID`);

Stock entity

@Entity
@Table(name = "stock")
@XmlRootElement
@Component("astock")
@NamedQueries({
    @NamedQuery(name = "Stock.findAll", query = "SELECT s FROM Stock s"),
    @NamedQuery(name = "Stock.findByStockId", query = "SELECT s FROM Stock s WHERE s.stockId = :stockId"),
    @NamedQuery(name = "Stock.findByStockCode", query = "SELECT s FROM Stock s WHERE s.stockCode = :stockCode"),
    @NamedQuery(name = "Stock.findByStockName", query = "SELECT s FROM Stock s WHERE s.stockName = :stockName")})
public class Stock implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "STOCK_ID")
    private Integer stockId;
    @Basic(optional = false)

    @Size(min = 1, max = 10)
    @Column(name = "STOCK_CODE")
    private String stockCode;
    @Basic(optional = false)

    @Size(min = 1, max = 20)
    @Column(name = "STOCK_NAME")
    private String stockName;
    @OneToOne(cascade = CascadeType.ALL, mappedBy = "stock")
    private StockDetail stockDetail;

StockDetail entity

@Entity
@Table(name = "stock_detail")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "StockDetail.findAll", query = "SELECT s FROM StockDetail s"),
    @NamedQuery(name = "StockDetail.findByStockId", query = "SELECT s FROM StockDetail s WHERE s.stockId = :stockId"),
    @NamedQuery(name = "StockDetail.findByCompName", query = "SELECT s FROM StockDetail s WHERE s.compName = :compName"),
    @NamedQuery(name = "StockDetail.findByCompDesc", query = "SELECT s FROM StockDetail s WHERE s.compDesc = :compDesc")})
public class StockDetail implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "STOCK_ID")
    private Integer stockId;
    @Basic(optional = false)

    @Size(min = 1, max = 100)
    @Column(name = "COMP_NAME")
    private String compName;

    @Basic(optional = false)
    @Size(min = 1, max = 255)
    @Column(name = "COMP_DESC")
    private String compDesc;
    @JoinColumn(name = "STOCK_ID", referencedColumnName = "STOCK_ID")
    @OneToOne(cascade = CascadeType.ALL)
    private Stock stock;

How I am trying to save it.

    Stock StockServices = (Stock) applicationContext.getBean("astock");

    Stock stock = new Stock();
    stock.setStockCode("123");
    stock.setStockName("AAPL");

    StockDetail stockDetail = new StockDetail();
    stockDetail.setCompName("Apple");
    stockDetail.setCompDesc("A hardware and software company");

    stockDetail.setStock(stock);
    stock.setStockDetail(stockDetail);

    sessionFactory.getCurrentSession().saveOrUpdate(stock);

Solution

Your problem is that you try to use stock_detail.STOCK_ID both as PRIMARY KEY for stock_detail and as FOREIGN KEY from stock and you assign different auto-increment values for them.

When Hibernate saves them it tries to save stock with STOCK_ID =86 and stock_detail with STOCK_ID=72 which breaks the foreign key contract.

Instead make a separate primary key in your stock_detail table E.g stock_detail_id and leave stock_id as a separate column.

Answered By – shad

Answer Checked By – Mildred Charles (BugsFixing Admin)