Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.CAR(ID) : While calling the POST service

Question

CarController.java

package com.mytaxi.controller;

import java.util.List;

import javax.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

import com.mytaxi.controller.mapper.CarMapper;
import com.mytaxi.datatransferobject.CarDTO;
import com.mytaxi.datatransferobject.CarDTO;
import com.mytaxi.domainobject.CarDO;
import com.mytaxi.domainvalue.Type;
import com.mytaxi.exception.ConstraintsViolationException;
import com.mytaxi.exception.EntityNotFoundException;
import com.mytaxi.service.car.CarService;


@RestController
@RequestMapping("v1/cars")
public class CarController
{

private final CarService carService;

@Autowired
public CarController(final CarService carService)
{
    this.carService = carService;
}


@PostMapping
@ResponseStatus(HttpStatus.CREATED)
public CarDTO createCar(@Valid @RequestBody CarDTO carDTO) throws ConstraintsViolationException
{
    CarDO carDO = CarMapper.makeCarDO(carDTO);
    carDTO = CarMapper.makeCarDTO(carDO);
    carService.create(carDO);
    return carDTO;
}

}

CarDO.java

package com.mytaxi.domainobject;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
import javax.validation.constraints.Max;
import com.mytaxi.domainvalue.Type;

@Entity
@Table(
    name = "car",
    uniqueConstraints = @UniqueConstraint(name = "uc_licensePlate", columnNames = {"licensePlate"})
)
public class CarDO
{
@Id
@Column(nullable = false)
@GeneratedValue
private Long id;

@Column(nullable = false)
@Enumerated(EnumType.STRING)
private Type manufacturer;

@Column(nullable = false)
private String licensePlate;

@Column(nullable = false)
private Integer seatCount;

@Column(nullable = false)
private String engineType;

@Column(nullable = false)
@org.hibernate.annotations.Type(type="yes_no")
private Boolean convertible;

@Column
@Max(5)
private Integer rating;

@Column(nullable = false)
@org.hibernate.annotations.Type(type="yes_no")
private Boolean isFunctioning = true;

@Column(nullable = false)
@org.hibernate.annotations.Type(type="yes_no")
private Boolean isBooked = false;


public Boolean getIsFunctioning()
{
    return isFunctioning;
}

public void setIsFunctioning(Boolean isFunctioning)
{
    this.isFunctioning = isFunctioning;
}

public CarDO(Type manufacturer, String licensePlate, Integer seatCount, 
    String engineType, Boolean convertible)
{
    this.manufacturer = manufacturer;
    this.licensePlate = licensePlate;
    this.seatCount = seatCount;
    this.engineType = engineType;
    this.convertible = convertible;
}

public Long getId()
{
    return id;
}

public void setId(Long id)
{
    this.id = id;
}

public Type getManufacturer()
{
    return manufacturer;
}

public void setManufacturer(Type manufacturer)
{
    this.manufacturer = manufacturer;
}

public String getLicensePlate()
{
    return licensePlate;
}

public void setLicensePlate(String licensePlate)
{
    this.licensePlate = licensePlate;
}

public Integer getSeatCount()
{
    return seatCount;
}

public void setSeatCount(Integer seatCount)
{
    this.seatCount = seatCount;
}

public String getEngineType()
{
    return engineType;
}

public void setEngineType(String engineType)
{
    this.engineType = engineType;
}

public Boolean getConvertible()
{
    return convertible;
}

public void setConvertible(Boolean convertible)
{
    this.convertible = convertible;
}

public Integer getRating()
{
    return rating;
}

public void setRating(Integer rating)
{
    this.rating = rating;
}

}

CarDTO.java

package com.mytaxi.datatransferobject;

import javax.validation.constraints.NotNull;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.mytaxi.domainvalue.Type;

@JsonInclude(JsonInclude.Include.NON_NULL)
public class CarDTO

{
@JsonIgnore
private Long id;

@NotNull(message = "license plate can not be null!")
private String licensePlate;

@NotNull(message = "cartype can not be null!")
private Type carType;

@NotNull(message = "seatCount can not be null!")
private Integer seatCount;

@NotNull(message = "engineType can not be null!")
private String engineType;

private Boolean convertible;


private CarDTO()
{
}


public CarDTO(Long id, String licensePlate, Type carType, Integer seatCount, String engineType, Boolean convertible)
{
    this.id = id;
    this.licensePlate = licensePlate;
    this.carType = carType;
    this.seatCount = seatCount;
    this.engineType = engineType;
    this.convertible = convertible;
}


public static CarDTOBuilder newBuilder()
{
    return new CarDTOBuilder();
}


@JsonProperty
public Long getId()
{
    return id;
}


public String getLicensePlate()
{
    return licensePlate;
}


public Type getCarType()
{
    return carType;
}


public Integer getSeatCount()
{
    return seatCount;
}


public String getEngineType()
{
    return engineType;
}


public Boolean getConvertible()
{
    return convertible;
}

public static class CarDTOBuilder
{
    private Long id;
    private String licensePlate;
    private Type carType;
    private Integer seatCount;
    private String engineType;
    private Boolean convertible;


    public CarDTOBuilder setId(Long id)
    {
        this.id = id;
        return this;
    }


    public CarDTOBuilder licensePlate(String licensePlate)
    {
        this.licensePlate = licensePlate;
        return this;
    }


    public CarDTOBuilder setLicensePlate(String licensePlate)
    {
        this.licensePlate = licensePlate;
        return this;
    }


    public CarDTOBuilder setCarType(Type carType)
    {
        this.carType = carType;
        return this;
    }


    public CarDTOBuilder setSeatCount(Integer seatCount)
    {
        this.seatCount = seatCount;
        return this;
    }


    public CarDTOBuilder setEngineType(String engineType)
    {
        this.engineType = engineType;
        return this;
    }


    public CarDTOBuilder setConvertible(Boolean convertible)
    {
        this.convertible = convertible;
        return this;
    }

    public CarDTO createCarDTO()
    {
        return new CarDTO(id, licensePlate, carType, seatCount, engineType, convertible);

    }
}

}

CarMapper.java

package com.mytaxi.controller.mapper;

import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;

import com.mytaxi.datatransferobject.CarDTO;
import com.mytaxi.domainobject.CarDO;

public class CarMapper
{
    public static CarDO makeCarDO(CarDTO carDTO)
    {
        return new CarDO(carDTO.getCarType(), carDTO.getLicensePlate(),
            carDTO.getSeatCount(), carDTO.getEngineType(), carDTO.getConvertible());
    }

    public static CarDTO makeCarDTO(CarDO carDO)
    {  
        CarDTO.CarDTOBuilder carDTOBuilder = CarDTO.newBuilder()
            .setId(carDO.getId())
            .setCarType(carDO.getManufacturer())
            .licensePlate(carDO.getLicensePlate())
            .setSeatCount(carDO.getSeatCount())
            .setEngineType(carDO.getEngineType())
            .setConvertible(carDO.getConvertible());

        return carDTOBuilder.createCarDTO();
    }


    public static List<CarDTO> makeCarDTOList(Collection<CarDO> cars)
    {
        return cars.stream()
            .map(CarMapper::makeCarDTO)
            .collect(Collectors.toList());
    }
}

CarService.java

    package com.mytaxi.service.car;

    import java.util.List;

    import com.mytaxi.domainobject.CarDO;
    import com.mytaxi.domainvalue.Type;
    import com.mytaxi.exception.ConstraintsViolationException;
    import com.mytaxi.exception.EntityNotFoundException;

    public interface CarService
    {


        CarDO create(CarDO carDO) throws ConstraintsViolationException;


    }

DefaultCarService.java

 @Override
    public CarDO create(CarDO carDO) throws ConstraintsViolationException
    {
        CarDO car;
        try
        {
            car = carRepository.save(carDO);
        }
        catch (DataIntegrityViolationException e)
        {
            LOG.warn("ConstraintsViolationException while creating a driver: {}", carDO, e.getCause());
            throw new ConstraintsViolationException(e.getMessage());
        }
        return car;
    }

CarRepository.java

package com.mytaxi.dataaccessobject;

import java.util.List;

import org.springframework.data.repository.CrudRepository;

import com.mytaxi.domainobject.CarDO;
import com.mytaxi.domainvalue.Type;


public interface CarRepository extends CrudRepository<CarDO, Long>
{

    List<CarDO> findByIsFunctioning(Boolean isFunctioning);

    CarDO findByLicensePlate(String licensePlate);

    List<CarDO> findByManufacturer(Type type);
}

When I hit the RESTAPI post service, I get the below exception.

Unique index or primary key violation: aused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.CAR(ID)"; SQL statement: insert into car (convertible, engine_type, is_booked, is_functioning, license_plate, manufacturer, rating, seat_count, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-197] at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) ~[h2-1.4.197.jar:1.4.197] at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.197.jar:1.4.197] at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.197.jar:1.4.197] at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:123) ~[h2-1.4.197.jar:1.4.197] at org.h2.mvstore.db.MVTable.addRow(MVTable.java:732) ~[h2-1.4.197.jar:1.4.197] at org.h2.command.dml.Insert.insertRows(Insert.java:182) ~[h2-1.4.197.jar:1.4.197] at org.h2.command.dml.Insert.update(Insert.java:134) ~[h2-1.4.197.jar:1.4.197] at org.h2.command.CommandContainer.update(CommandContainer.java:102) ~[h2-1.4.197.jar:1.4.197] at org.h2.command.Command.executeUpdate(Command.java:261) ~[h2-1.4.197.jar:1.4.197] at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:199) ~[h2-1.4.197.jar:1.4.197] at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:153) ~[h2-1.4.197.jar:1.4.197] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-2.7.9.jar:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:na] at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] ... 93 common frames omitted"PRIMARY KEY ON PUBLIC.CAR(ID) :

Any idea what is wrong in the code?

Answer 1

I was encountering the same problem for an @Entity class. My data.sql ids were conflicting with the auto-generated ids from H2.
My solution was to change:

@GeneratedValue(strategy = GenerationType.AUTO)

to

@GeneratedValue(strategy = GenerationType.IDENTITY)

This way I could keep my data.sql file.
The javadoc here indicates that GenerationType.IDENTITY "Indicates that the persistence provider must assign primary keys for the entity using a database identity column."