insert or update on table "products" violates foreign key (Postgresql spring boot) error

Question

I have an api in spring boot and I decided to create a model order, but I found some database errors and I decided to enter my pg admin and delete all the tables and start over, but now when I request my model product I find the error:

2023-03-27T15:00:48.189-03:00  WARN 31677 --- [nio-8011-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23503
2023-03-27T15:00:48.190-03:00 ERROR 31677 --- [nio-8011-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: insert or update on table "products" violates foreign key constraint "fkselc31gul05wygg2llkv0v3yb"
  Detail: Key (product_id)=(9c2655d0-c5a4-45f1-ba97-8b5fe1a385d8) is not present in table "orders".
2023-03-27T15:00:48.231-03:00  INFO 31677 --- [nio-8011-exec-6] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements

I already tried to remove all the tables, I also tried to create another database and delete the old one, but I still get the same error

orderModel

package com.api.business_manager_api.Models;

import jakarta.persistence.*;

import java.util.List;
import java.util.UUID;

@Entity
@Table(name = "ORDERS")
public class OrderModel {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID order_id;
    @ManyToOne
    @JoinColumn(name = "customer_id")
    private CustomerModel customer;
    @OneToMany
    @JoinColumn(name = "product_id")
    private List<ProductModel> products;
    @Column(nullable = false, length = 80)
    private Float totalAmount;

    public UUID getOrder_id() {
        return order_id;
    }

    public void setOrder_id(UUID order_id) {
        this.order_id = order_id;
    }

    public CustomerModel getCustomer() {
        return customer;
    }

    public void setCustomer(CustomerModel customer) {
        this.customer = customer;
    }

    public List<ProductModel> getProducts() {
        return products;
    }

    public void setProducts(List<ProductModel> products) {
        this.products = products;
    }

    public Float getTotalAmount() {
        return totalAmount;
    }

    public void setTotalAmount(Float totalAmount) {
        this.totalAmount = totalAmount;
    }
}

productModel

package com.api.business_manager_api.Models;

import com.fasterxml.jackson.annotation.JsonIgnore;
import jakarta.persistence.*;

import java.util.UUID;

@Entity
@Table(name = "PRODUCTS")
public class ProductModel {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID product_id;

    @Column(nullable = false, length = 80)
    private String product;
    @Column(nullable = true, length = 200)
    private String image_url;
    @Column(nullable = true, length = 80)
    private String brand;
    @Column(nullable = true, length = 80)
    private String description;
    @Column(nullable = true, length = 80)
    private Float price;

    @Column(nullable = true, length = 80)
    private Float extraPrice;

    @Column(nullable = false, length = 80)
    private Integer stock;
    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "category_id")
    private CategoryModel productCategory;

    public UUID getProduct_id() {
        return product_id;
    }

    public void setProduct_id(UUID product_id) {
        this.product_id = product_id;
    }

    public String getProduct() {
        return product;
    }

    public void setProduct(String product) {
        this.product = product;
    }

    public String getImage_url() {
        return image_url;
    }

    public void setImage_url(String image_url) {
        this.image_url = image_url;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Float getPrice() {
        return price;
    }

    public void setPrice(Float price) {
        this.price = price;
    }

    public Integer getStock() {
        return stock;
    }

    public void setStock(Integer stock) {
        this.stock = stock;
    }

    public CategoryModel getProductCategory() {
        return productCategory;
    }

    public void setProductCategory(CategoryModel productCategory) {
        this.productCategory = productCategory;
    }

    public Float getExtraPrice() {
        return extraPrice;
    }

    public void setExtraPrice(Float extraPrice) {
        this.extraPrice = extraPrice;
    }
}

I don't know what the reaction would be, because as you guys can see, my product doesn't depend on anything order to be created

update!

postman return:

{
    "order_id": "982f2270-28fa-4dcf-ba24-89b2c9f18bb5",
    "customer": {
        "customer_id": "c33d4cf7-a931-4818-8d86-94b6c56b9426",
        "name": "Antoni Ancelotti",
        "description": "italian",
        "cellphone": "21 8494944",
        "email": "ancelotti@email.com",
        "note": "ok"
    },
    "products": [
        {
            "product_id": "47b0a1ff-71e7-4b08-a243-9cf01ff55af7",
            "product": null,
            "image_url": null,
            "brand": null,
            "description": null,
            "price": null,
            "extraPrice": null,
            "stock": null
        },
        {
            "product_id": "47b0a1ff-71e7-4b08-a243-9cf01ff55af7",
            "product": "Galaxy A13 128 GB Slim",
            "image_url": "/products/images/e40989d2-66g8-4531-be26-00c124a8dfd6-SM-A136UZRAATT-8.webp",
            "brand": null,
            "description": "galaxy",
            "price": 50.0,
            "extraPrice": null,
            "stock": 50
        }
    ],
    "totalAmount": 1800.0
}

Are you sure?: `@OneToMany @JoinColumn(name = "product_id")` — Adrian Klaver, Mar 27 '23 at 18:17
I don't use spring-boot, but that looks like it is setting up a FK on `product_id` between `products` and `orders`. Use `psql` to look at the tables in the database to verify. — Adrian Klaver, Mar 27 '23 at 18:22
The question is do you really want to remove it? It seems logical that the products in an order refer via FK to a table of products. That way the order can only have products that exist. — Adrian Klaver, Mar 27 '23 at 18:36
Yes, but I can't create a product so that it can be called by orders in the future. I don't know why this is happening now as before it was working normally — Routfin, Mar 27 '23 at 18:37
Then where are you getting the product_id `9c2655d0-c5a4-45f1-ba97-8b5fe1a385d8` from? Bottom line you should not be able to sell an item that does not exist and that is what the FK is guarding against. Again use [psql](https://www.postgresql.org/docs/current/app-psql.html) to look at the tables in the database directly without all the ORM nonsense in the way. — Adrian Klaver, Mar 27 '23 at 19:48

score 1 · Accepted Answer · answered Mar 27 '23 at 19:03

1

I believe what you are trying to do here is to establish a unidirectional one-to-many relationship between ORDERS and PRODUCTS table i.e. one order may be having multiple products. So ideally your join column should be something like order_id rather than product_id since that would be the FK in PRODUCTS table referring to the order_id entries of ORDERS table. After, changing the Join column, you can also add cascading so that changes in ORDERS table are cascaded according to your use case to PRODUCTS table as well.

Here are some good references you can use for reference on creating unidirectional One-To-Many relationship using Hibernate and Springboot.

https://www.bezkoder.com/jpa-one-to-many-unidirectional/

Furthermore, if you want orders info in ProductModel Objects I would recommend you to create a OneToMany bidirectional relationship using OrderModel member in ProductModel Class.

answered Mar 27 '23 at 19:03

himanshu-pandey

101
3

then it would be " @JoinColumn(name = "order_id") private List products;" ? and how would that reference the product? – Routfin Mar 27 '23 at 19:32
Yes. It will create a column called order_id in PRODUCTS table(FK) which refers to order_id in ORDERS table(PK). – himanshu-pandey Mar 27 '23 at 19:35
But in my request won't I have to send the id of a product? – Routfin Mar 27 '23 at 19:42
Furthermore, since, one order has many product, why do want to refer product in Orders table. Rather, have FK in PRODUCTS table referring to PK of ORDERS table – himanshu-pandey Mar 27 '23 at 19:42
So what you would do is something like this. Create OrderModel object. Create multiple productmodel objects. Add those in the List called products in OrderModel class. Save the OrderModel object with cascading enabled. This will create an entry in ORDERS table with an order_id and multiple entries in PRODUCTS table with each record referring to the order_id of record inserted in ORDERS table. – himanshu-pandey Mar 27 '23 at 19:46
To get an OrderModel object from DB use Session.get(OrderModel.class, order_id) and then use order.getProducts to get associated products. To get ProductModel Object use Session.get(ProductModel.class, product_id). If you want to get associated order from a product object then I would suggest you use a bidirectional OneToMany relationship. For that refer this - https://www.javaguides.net/2019/08/jpa-hibernate-one-to-many-bidirectional-mapping-example.html – himanshu-pandey Mar 27 '23 at 19:51
now the error: " "message": "detached entity passed to persist: com.api.business_manager_api.Models.ProductModel", "suppressed": [], "localizedMessage": "detached entity passed to persist: com.api.business_manager_api.Models.ProductModel"" in order – Routfin Mar 27 '23 at 20:15
Are u using CASCADE Type MERGE and are setters correctly configured? Refer this for more details - https://stackoverflow.com/questions/13370221/persistentobjectexception-detached-entity-passed-to-persist-thrown-by-jpa-and-h . The point is that your use case is pretty straightforward so you should be fine using the references I shared. – himanshu-pandey Mar 27 '23 at 20:22
It worked, but when I make my request, postman returns the product with the correct information and the product with null information, as I showed in the updade (note that both have the same id). Can you tell me why this happens? – Routfin Mar 27 '23 at 20:33

score 0 · Answer 2 · answered Mar 27 '23 at 18:54

I think that you are trying to modify a product ID and that the error in what ever DB you are using is misleading:

ERROR: insert or update on table "products" violates foreign key constraint "fkselc31gul05wygg2llkv0v3yb"
Detail: Key (product_id)=(9c2655d0-c5a4-45f1-ba97-8b5fe1a385d8) is not present in table "orders".

I think that because orders is referencing products in List<ProductModel> products, it is telling you that in table "orders" the foreign key constraint is being violated by an attempted change to (product_id)=(9c2655d0-c5a4-45f1-ba97-8b5fe1a385d8).

If you want to change product IDs, then you also have to make those changes cascading.

insert or update on table "products" violates foreign key (Postgresql spring boot) error

2 Answers2