我使用的是JPA、Hibernate、Spring和MySQL
无法添加或更新子行:外键约束失败(
db1
.stock_detail
,constraintFK_STOCK_ID
foreign key(STOCK_ID
(REFERENCESstock
(STOCK_ID
((
创建脚本
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`);
股票实体
@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;
库存详细信息实体
@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;
我是如何试图保存它的。
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);
同意接受的答案。
不过,如果您想在子表中使用外键作为主键,请在子表上使用Foreign Key Generator
。通过这种方式,我们可以减少数据库引擎创建过多唯一id的开销。
参考:ForeignKeyGeneratorUsage
您的问题是,您试图将stock_detail.STOCK_ID
同时用作stock_detail
的PRIMARY KEY
和库存中的FOREIGN KEY
,并为它们分配不同的自动增量值。
当Hibernate保存它们时,它试图用STOCK_ID =86
保存库存,用STOCK_ID=72
保存stock_detail
,这破坏了外键契约。
相反,在stock_detail
表中制作一个单独的主键,例如stock_detail_id
,并将stock_id
保留为一个单独列。
接受的答案并不能提供最佳解决方案。应保留使用stock.STOCK_ID
作为stock_detail
的主/外键的方法,原因如下。在JPA中,也支持这种方法,只需使用本教程第4节中概述的@PrimaryKeyJoinColumn
和@MapsId
注释即可。