Spring Boot/Hibernate插入数据报错：违反唯一约束

问题场景 #

使用Java Spring Boot结合Hibernate操作PostgreSQL数据库，尝试为同一用户（ID=2）添加第二只股票的投资组合记录时，触发唯一约束冲突错误。数据库中已存在该用户的一条PortfolioStock记录，新记录的股票与已有记录不同，但仍报错。

相关代码 #

用户实体类UserEntity #

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "users")
public class UserEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_id_seq")
    private Long id;

    private String name;

    private String username;

    private String password;

}

投资组合股票实体类PortfolioStockEntity #

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "portfolioStocks")
public class PortfolioStockEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "portfolioStock_id_seq")
    private Long id;

    @ManyToOne()
    @JoinColumn(name = "stockId")
    private StockEntity stock;

    @ManyToOne()
    @JoinColumn(name = "userId")
    private UserEntity user;

    private Number Quantity;

}

PortfolioStockRepository #

@Repository
public interface PortfolioStockRepository extends CrudRepository<PortfolioStockEntity, Long> {

    List<PortfolioStockEntity> findByUser(UserEntity userEntity);

}

PortfolioStockService相关方法 #

@Service
public class PortfolioStockServiceImpl implements PortfolioStockService {
....

    @Override
    public PortfolioStockEntity createUpdatePortfolioStock(Long id, PortfolioStockEntity portfolioStockEntity) {
        portfolioStockEntity.setId(id);
        return portfolioStockRepository.save(portfolioStockEntity);
    }

....
}

PortfolioStockController添加接口 #

@RestController
public class PortfolioStockController {
....

    @NoArgsConstructor
    @AllArgsConstructor
    private static class AddStockRequest {
        public UserDto user;
        public StockDto stock;
        public Number quantity;
    }

    @PostMapping(path = "/portfolio/stock/add")
    public PortfolioStockDto addPortfolioStock(@RequestBody AddStockRequest requestBody) {
        UserEntity userEntity = userMapper.mapFrom(requestBody.user);
        userEntity = userService.checkAuthentication(userEntity);

        StockEntity stockEntity = stockMapper.mapFrom(requestBody.stock);
        Optional<StockEntity> findStockEntity = stockService.findStock(stockEntity.getSymbol());
        if (findStockEntity.isEmpty())
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "Stock does not exist");
        else
            stockEntity = findStockEntity.get();

        PortfolioStockEntity portfolioStockEntity = PortfolioStockEntity.builder()
                .stock(stockEntity)
                .user(userEntity)
                .Quantity(requestBody.quantity)
                .build();

        System.out.println(portfolioStockEntity);

        portfolioStockEntity = portfolioStockService.createUpdatePortfolioStock(
                portfolioStockEntity.getId(),
                portfolioStockEntity);

        return portfolioStockMapper.mapTo(portfolioStockEntity);
    }

....
}

已有记录与待插入记录 #

• 数据库中已存在的记录：

{ PortfolioStockEntity(id=2, stock=StockEntity(id=3, name=Intel Corporation, symbol=InNTC, price=95.91, lastUpdated=2025-01-14), user=UserEntity(id=2, name=test, username=test, password=test), Quantity=5) }

• 尝试插入的新记录：

{ PortfolioStockEntity(id=null, stock=StockEntity(id=2, name=NVIDIA Corporation, symbol=NVDA, price=135.91, lastUpdated=2025-01-14), user=UserEntity(id=2, name=test, username=test, password=test), Quantity=5) }

错误信息 #

duplicate key value violates unique constraint "ukfrklsf57yxxy0h95vk4dxre5a"   Detail: Key (user_id)=(2) already exists.] [insert into portfolio_stocks (quantity,stock_id,user_id,id) values (?,?,?,?)]; SQL [insert into portfolio_stocks (quantity,stock_id,user_id,id) values (?,?,?,?)]; constraint [ukfrklsf57yxxy0h95vk4dxre5a]]

解决方法 #

排查后确认问题出在PostgreSQL的portfolio_stocks表结构上，重建该表后错误解决。

内容的提问来源于stack exchange，提问作者Striker