CODEWITHSUNDEEP
javamysqlspringhibernate
Davidto
Davidto

Reputation: 37

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

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);

Upvotes: 0

Views: 2816

Answers (3)

StackOverFlower
StackOverFlower

Reputation: 26

The accepted answer doesn't provide an optimal solution. The approach to use stock.STOCK_ID as the primary/foreign key of stock_detail should be kept for reasons outlined here. And in JPA, this approach is supported as well, just use the @PrimaryKeyJoinColumn and @MapsId annotations as outlined in section 4 of this tutorial

Upvotes: 0

Lovababu Padala
Lovababu Padala

Reputation: 2477

Agree with accepted answer.

Still if you want to stick with Foreign key as primary key in child table, Go with Foreign Key Generator on child table. This way we can reduce the overhead to DB engine for creating too many unique ids.

Refer: ForeignKeyGeneratorUsage

Upvotes: 1

shad
shad

Reputation: 124

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.

Upvotes: 0

Related Questions