spring boot - Getting JPA error integrity constraint (FK_XXXXX) violated - parent key not found

Question

I have two tables

CREATE TABLE stripe_product (
    id NUMBER NOT NULL PRIMARY KEY,
    product_id VARCHAR2(256) NOT NULL,
    name VARCHAR2(256) NOT NULL,
    description VARCHAR2(256),
    active NUMBER(1,0),
    deleted NUMBER(1,0),
    created_at TIMESTAMP,
    created_by NUMBER,
    updated_at TIMESTAMP,
    updated_by NUMBER,
    deleted_at TIMESTAMP,
    CONSTRAINT UC_stripe_product_id_product_id UNIQUE (id, product_id),
    CONSTRAINT UC_stripe_product_product_id UNIQUE (product_id)
);

And

CREATE TABLE stripe_price (
    id NUMBER NOT NULL PRIMARY KEY,
    price_id VARCHAR2(256) NOT NULL,
    stripe_product_product_id VARCHAR2(256) NOT NULL,
    active NUMBER(1,0),
    deleted NUMBER(1,0),
    currency VARCHAR2(10) NOT NULL,
    billing_scheme VARCHAR2(50) NOT NULL,
    unit_amount NUMBER NOT NULL,
    type VARCHAR2(50) NOT NULL,
    recurring_aggregate_usage VARCHAR2(50),
    recurring_interval VARCHAR2(50),
    recurring_interval_count NUMBER,
    recurring_usage_type VARCHAR2(50),
    created_at TIMESTAMP,
    created_by NUMBER,
    updated_at TIMESTAMP,
    updated_by NUMBER,
    deleted_at TIMESTAMP,
    CONSTRAINT UC_stripe_price_id_price_id_stripe_product_product_id UNIQUE (id, price_id, stripe_product_product_id),
    CONSTRAINT UC_stripe_price_price_id UNIQUE (price_id),
    CONSTRAINT FK_stripe_price_stripe_product_product_id FOREIGN KEY (stripe_product_product_id) 
    REFERENCES stripe_product(product_id) ON DELETE CASCADE
);

I mapped these tables using the following classes

@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "stripe_product")
public class StripeProduct {
    @Id
    @SequenceGenerator(name = "stripe_product_seq", sequenceName = "stripe_product_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "stripe_product_seq")
    private Long id;

    @Column(name = "product_id", unique = true)
    private String productId;

    @Column(nullable = false)
    private String name;
    private String description;
    private Boolean active;
    private Boolean deleted;

    @Embedded
    private Audit audit = new Audit();

    @Column(name = "deleted_at")
    private Instant deletedAt;

    public StripeProduct() {

    }

    public StripeProduct(Product product) {
        this.productId = product.getId();
        this.name = product.getName();
        this.description = product.getDescription();
        this.active = product.getActive();
        this.deleted = product.getDeleted();
    }
    
    // getters and setter

}

Other one

@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "stripe_price")
public class StripePrice {
    @Id
    @SequenceGenerator(name = "stripe_price_seq", sequenceName = "stripe_price_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "stripe_price_seq")
    private Long id;

    @Column(name = "price_id", unique = true)
    private String priceId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "stripe_product_product_id")
    private StripeProduct stripeProduct;

    private Boolean active;
    private Boolean deleted;
    private String currency;

    ....

    @Embedded
    private Audit audit = new Audit();

    @Column(name = "deleted_at")
    private Instant deletedAt;

    public StripePrice() {
    
    }

    public StripePrice(Price price, StripeProduct stripeProduct) {
    
        Assert.notNull(price, "price cannot be null");
    
        this.priceId = price.getId();
        this.stripeProduct = stripeProduct;
        this.active = price.getActive();
        this.currency = price.getCurrency();
        this.billingScheme = price.getBillingScheme();
        this.unitAmount = price.getUnitAmount();
        this.type = price.getType();
        Recurring recurring = price.getRecurring();
        if (recurring != null) {
            this.recurringAggregateUsage = recurring.getAggregateUsage();
            this.recurringInterval = recurring.getInterval();
            this.recurringIntervalCount = recurring.getIntervalCount();
            this.recurringUsageType = recurring.getUsageType();
        }
    
        this.deleted = price.getDeleted();
    }
    // getters and setters
}

In the database I have the following records

Now if I directly insert the record in the database using the following sql it works

insert into stripe_price (active, created_by, created_at, updated_by, updated_at, billing_scheme, 
currency, deleted, deleted_at, price_id, recurring_aggregate_usage, recurring_interval, 
recurring_interval_count, recurring_usage_type, stripe_product_product_id, type, unit_amount, id) 
values (1, 0, SYSDATE, 0, SYSDATE, 'Billing scheme', 'usd', 0, null, 'adsad', 'hjgjh', 'sfsad', 1, 
'asdsad', 'prod_Io2qV0NPORZhnX', 'adsad', 100, 33);

insert into stripe_price (active, created_by, created_at, updated_by, updated_at, billing_scheme, 
currency, deleted, deleted_at, price_id, recurring_aggregate_usage, recurring_interval, 
recurring_interval_count, recurring_usage_type, stripe_product_product_id, type, unit_amount, id) 
values (1, 0, SYSDATE, 0, SYSDATE, 'Billing scheme', 'usd', 0, null, 'price_id-2', 'hjgjh', 'sfsad', 
1, 'asdsadxzcxzc', 'prod_Io2qV0NPORZhnX', 'asd1234', 100, 34)

But now using JPA I am getting error

Caused by: java.sql.BatchUpdateException: ORA-02291: integrity constraint (BUILDADMIN.FK_STRIPE_PRICE_STRIPE_PRODUCT_PRODUCT_ID) violated - parent key not found

Here is my code

List<Price> prices = priceCollection.getData();
if (!CollectionUtils.isEmpty(prices)) {
    prices.forEach(price -> {
        String productId = price.getProduct();
        StripeProduct managedStripeProduct =  stripeProductRepository.findByProductId(productId).orElse(null);
        if (managedStripeProduct != null) {
                StripePrice newStripePrice = new StripePrice(price, managedStripeProduct);
                StripePrice managedStripePrice =  stripePriceRepository.save(newStripePrice);
            }
        });
}

In debug I found that the following SQL is making

Hibernate: select stripe_price_seq.nextval from dual
Hibernate: insert into stripe_price (active, created_by, created_at, updated_by, updated_at, billing_scheme, currency, deleted, deleted_at, price_id, recurring_aggregate_usage, recurring_interval, recurring_interval_count, recurring_usage_type, stripe_product_product_id, type, unit_amount, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 binding parameter [1] as [BIT] - [true]
binding parameter [2] as [BIGINT] - [0]
binding parameter [3] as [TIMESTAMP] - [2021-01-25T23:18:11.104Z]
binding parameter [4] as [BIGINT] - [0]
binding parameter [5] as [TIMESTAMP] - [2021-01-25T23:18:11.104Z]
binding parameter [6] as [VARCHAR] - [per_unit]
binding parameter [7] as [VARCHAR] - [usd]
binding parameter [8] as [BIT] - [null]
binding parameter [9] as [TIMESTAMP] - [null]
binding parameter [10] as [VARCHAR] - [price_1ICQl8JOji9YLkEKmju4jUmu]
binding parameter [11] as [VARCHAR] - [null]
binding parameter [12] as [VARCHAR] - [month]
binding parameter [13] as [BIGINT] - [1]
binding parameter [14] as [VARCHAR] - [licensed]
binding parameter [15] as [BIGINT] - [30]
binding parameter [16] as [VARCHAR] - [recurring]
binding parameter [17] as [BIGINT] - [100000]
binding parameter [18] as [BIGINT] - [80]

As you can notice that there is no stripe_product_product_id when hibernate is making SQL. I think that's why it is generating error.

Although I am setting it on StripePrice but unable to find why I am betting error. Can anyone please explain what I am doing wrong ? And how I can I resolve this issue?

Thanks

question from:https://stackoverflow.com/questions/65893862/getting-jpa-error-integrity-constraint-fk-xxxxx-violated-parent-key-not-foun

Answer 1

I have solved the problem. Actually the problem was I have different name for column. In stripe_product table column name is product_id. While in stripe_price table column name is stripe_product_product_id. So I have to use the following in my mapping

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "stripe_product_product_id", referencedColumnName = "product_id", nullable = false)
private StripeProduct stripeProduct;

So basically referencedColumnName = "product_id" was missing that's why JPA unable to find the product_id value from stripe_product table. Hopefully it will be useful for others too.