Unique index or primary key violation:

Question

Project has 5 package.

Domain package classes:

Category class

@Data
@Entity
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String description;

    @ManyToMany(mappedBy = "categories")
    private Set<Recipe> recipes;
}

Ingredient class

@Data
@Entity
public class Ingredient {
        
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            private Long id;
        
            private String description;
            private BigDecimal amount;
        
            @OneToOne(fetch = FetchType.EAGER)
            private UnitOfMeasure uom;
            @ManyToOne
            private Recipe recipe;
        
        
            public Ingredient() {
            }
        
            public Ingredient(String description, BigDecimal amount, UnitOfMeasure uom) {
                this.description = description;
                this.amount = amount;
                this.uom = uom;
            }
    }

Notes class

@Data
@Entity
public class Notes {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne
    private Recipe recipe;

    @Lob
    private String recipeNotes;


}

Recipe class

@Data
@Entity
public class Recipe {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String description;
    private Integer prepTime;
    private Integer cookTime;
    private Integer servings;
    private String source;
    private String url;

    @Lob
    private String directions;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "recipe")
    private Set<Ingredient> ingredients = new HashSet<>();

    @Lob
    private Byte[] image;

    @Enumerated(value = EnumType.STRING)
    private Difficulty difficulty;

    @OneToOne(cascade = CascadeType.ALL)
    private Notes notes;


    @ManyToMany
    @JoinTable(name = "recipe_category",
            joinColumns = @JoinColumn(name = "recipe_id"),
            inverseJoinColumns = @JoinColumn(name = "category_id"))
    private Set<Category> categories = new HashSet<>();
}

UnitOfMeasure class

@Data
@Entity
public class UnitOfMeasure {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String description;
}

Bootstrap package RecipeBootstrap class

@Component
public class RecipeBootstrap implements ApplicationListener<ContextRefreshedEvent> {

    private final RecipeRepository recipeRepository;
    private final CategoryRepository categoryRepository;
    private final UnitOfMeasureRepository unitOfMeasureRepository;

    public RecipeBootstrap(RecipeRepository recipeRepository, CategoryRepository categoryRepository, UnitOfMeasureRepository unitOfMeasureRepository) {
        this.categoryRepository = categoryRepository;
        this.unitOfMeasureRepository = unitOfMeasureRepository;
        this.recipeRepository = recipeRepository;
    }
    
    @Override
    public void onApplicationEvent(ContextRefreshedEvent event) {
        recipeRepository.saveAll(getRecipe());
    }
    
    public List<Recipe> getRecipe(){

        List<Recipe> recipes = new ArrayList<>(2);

        Optional<Category> americanCategoryOptional = categoryRepository.findByDescription("American");
        Optional<Category> mexicanCategoryOptional = categoryRepository.findByDescription("Mexican");
        Optional<Category> italianCategoryOptional = categoryRepository.findByDescription("Italian");
        Optional<Category> fastFoodCategoryOptional = categoryRepository.findByDescription("Fast Food");

        Category americanCategory = americanCategoryOptional.get();
        Category mexicanCategory = mexicanCategoryOptional.get();
        Category italianCategory = italianCategoryOptional.get();
        Category fastFoodCategory = fastFoodCategoryOptional.get();
        
        Optional<UnitOfMeasure> teaspoonUomOptional = unitOfMeasureRepository.findByDescription("Teaspoon");
        Optional<UnitOfMeasure> tablespoonUomOptional = unitOfMeasureRepository.findByDescription("Tablespoon");
        Optional<UnitOfMeasure> cupUomOptional = unitOfMeasureRepository.findByDescription("Cup");
        Optional<UnitOfMeasure> pinchUomOptional = unitOfMeasureRepository.findByDescription("Pinch");
        Optional<UnitOfMeasure> ounceUomOptional = unitOfMeasureRepository.findByDescription("Ounce");
        Optional<UnitOfMeasure> eachUomOptional = unitOfMeasureRepository.findByDescription("Each");
        Optional<UnitOfMeasure> dashUomOptional = unitOfMeasureRepository.findByDescription("Dash");

        UnitOfMeasure teaspoonUom = teaspoonUomOptional.get();
        UnitOfMeasure tablespoonUom = tablespoonUomOptional.get();
        UnitOfMeasure cupUom = cupUomOptional.get();
        UnitOfMeasure pinchUom = pinchUomOptional.get();
        UnitOfMeasure ounceUom = ounceUomOptional.get();
        UnitOfMeasure eachUom = eachUomOptional.get();
        UnitOfMeasure dashUom = dashUomOptional.get();

        Recipe guacRecipe = new Recipe();
        guacRecipe.setDescription("Perfect Guacamole");
        guacRecipe.setPrepTime(10);
        guacRecipe.setCookTime(0);
        guacRecipe.setDifficulty(Difficulty.EASY);
        guacRecipe.setDirections("The trick to making perfect guacamole is using avocados that are just the right amount of ripeness. Not ripe enough and the avocado will be hard and flavorless. Too ripe and the taste will be off.\n" +
                "\n" +
                "Check for ripeness by gently pressing the outside of the avocado. If there is no give, the avocado is not ripe yet. If there is a little give, the avocado is ripe. If there is a lot of give, the avocado may be too ripe and not good. In this case, taste test first before using.");

        Notes guacNotes = new Notes();
        guacNotes.setRecipeNotes("Guacamole has a role in the kitchen beyond a party dip. It's great scooped on top of nachos and also makes an excellent topping or side for enchiladas, tacos, grilled salmon, or oven-baked chicken.\n" +
                "\n" +
                "Guacamole is great in foods, as well. Try mixing some into a tuna sandwich or your next batch of deviled eggs.");

        guacNotes.setRecipe(guacRecipe);
        guacRecipe.setNotes(guacNotes);

        guacRecipe.getCategories().add(americanCategory);
        guacRecipe.getCategories().add(mexicanCategory);

        guacRecipe.getIngredients().add(new Ingredient("ripe avocados", new BigDecimal(5),teaspoonUom));
        guacRecipe.getIngredients().add(new Ingredient("Kosher salt", new BigDecimal(".5"),eachUom));
        guacRecipe.getIngredients().add(new Ingredient("fresh lime juice and lemon juice", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("minced red onion or thinly sliced green onion", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("serrano chiles, stems and seeds removed, minced", new BigDecimal(2),eachUom));
        guacRecipe.getIngredients().add(new Ingredient("Cilantro", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("freshly grated black pepper", new BigDecimal(2),dashUom));
        guacRecipe.getIngredients().add(new Ingredient("ripe tomato, seeds and pulp removed, chopped", new BigDecimal(".5"),eachUom));

        recipes.add(guacRecipe);
        
        return recipes;
    }
}

This is data.sql

INSERT INTO category (description) VALUES ('American');
INSERT INTO category (description) VALUES ('Italian');
INSERT INTO category (description) VALUES ('Mexican');
INSERT INTO category (description) VALUES ('Fast Food');
INSERT INTO unit_of_measure (description) VALUES ('Teaspoon');
INSERT INTO unit_of_measure (description) VALUES ('Tablespoon');
INSERT INTO unit_of_measure (description) VALUES ('Cup');
INSERT INTO unit_of_measure (description) VALUES ('Pinch');
INSERT INTO unit_of_measure (description) VALUES ('Ounce');
INSERT INTO unit_of_measure (description) VALUES ('Each');
INSERT INTO unit_of_measure (description) VALUES ('Dash');
INSERT INTO unit_of_measure (description) VALUES ('Pint');

when I run program there is error.

org.springframework.dao.DataIntegrityViolationException: could not execute statement [Unique index or primary key violation: "PUBLIC.CONSTRAINT_INDEX_1 ON PUBLIC.INGREDIENT(UOM_ID NULLS FIRST) VALUES ( /* 1 */ CAST(2 AS BIGINT) )"; SQL statement:
insert into ingredient (amount,description,recipe_id,uom_id,id) values (?,?,?,?,default) [23505-214]] [insert into ingredient (amount,description,recipe_id,uom_id,id) values (?,?,?,?,default)]; SQL [insert into ingredient (amount,description,recipe_id,uom_id,id) values (?,?,?,?,default)]; constraint ["PUBLIC.CONSTRAINT_INDEX_1 ON PUBLIC.INGREDIENT(UOM_ID NULLS FIRST) VALUES ( /* 1 */ CAST(2 AS BIGINT) )"; SQL statement:
insert into ingredient (amount,description,recipe_id,uom_id,id) values (?,?,?,?,default) [23505-214]]

I found cause. Its here.

guacRecipe.getIngredients().add(new Ingredient("ripe avocados", new BigDecimal(5),teaspoonUom));
        guacRecipe.getIngredients().add(new Ingredient("Kosher salt", new BigDecimal(".5"),eachUom));
        guacRecipe.getIngredients().add(new Ingredient("fresh lime juice and lemon juice", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("minced red onion or thinly sliced green onion", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("serrano chiles, stems and seeds removed, minced", new BigDecimal(2),eachUom));
        guacRecipe.getIngredients().add(new Ingredient("Cilantro", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("freshly grated black pepper", new BigDecimal(2),dashUom));
        guacRecipe.getIngredients().add(new Ingredient("ripe tomato, seeds and pulp removed, chopped", new BigDecimal(".5"),eachUom));

When create new Ingredient with same unitOfMeasure exception is thrown. For example, if I write

guacRecipe.getIngredients().add(new Ingredient("ripe avocados", new BigDecimal(5),teaspoonUom));
        guacRecipe.getIngredients().add(new Ingredient("Kosher salt", new BigDecimal(".5"),eachUom));
       guacRecipe.getIngredients().add(new Ingredient("Cilantro", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("freshly grated black pepper", new BigDecimal(2),dashUom));

it runs without problem. If I add same unitOfMeasure, exception is thrown. For example:

guacRecipe.getIngredients().add(new Ingredient("ripe avocados", new BigDecimal(5),teaspoonUom));
        guacRecipe.getIngredients().add(new Ingredient("Kosher salt", new BigDecimal(".5"),eachUom));
      guacRecipe.getIngredients().add(new Ingredient("Cilantro", new BigDecimal(2),tablespoonUom));
        guacRecipe.getIngredients().add(new Ingredient("freshly grated black pepper", new BigDecimal(2),dashUom));
        guacRecipe.getIngredients().add(new Ingredient("ripe tomato, seeds and pulp removed, chopped", new BigDecimal(".5"),eachUom));

eachUom is written twice. I can't find correct answer for this problem.

Answer 1

The issue is coming because you have @OneToOne mapping between Ingredient and UnitOfMeasure table but you are trying to add primary key of unitOfMeasure table twice as a foreign key in your ingredient table.

The exception is thrown because you have used One to One relationship but you are adding same unitOfMeasure for multiple ingredient.

If you want to get rid of this error you can probably use @OneToMany relationship in the ingredient table as one ingredient can have multiple unitOfMeasure.