could not execute statement SQL constraint [id] nested exception is org.hibernate.exception.ConstraintViolationException

Question

I have two simple entities: user and account. Account can be related to one or many users. For instance: a bank account belongs to a single person or belong to a couple.

I am getting the error mentioned in the topic when I tried to post an account. I guess the issue is how I am formatting the json in postman. I don't see errors in my entities.

user:

@Entity
@Table(name = "bankuser")
public class User implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 6447416794596398975L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Long id;

    @Column(name = "firstname", length = 50)
    private String firstname;

    @Column(name = "lastname", length = 50)
    private String lastname;

    public User() {
    }

    public User(Long id) {
        this.id = id;
    }

    public User(Long id, String firstname, String lastname, String designation, Integer salary) {
        this.id = id;
        this.firstname = firstname;
        this.lastname = lastname;

    }

    public User(String firstname, String lastname, String designation, Integer salary) {
        this.firstname = firstname;
        this.lastname = lastname;
    }

    public Long getId() {
        return this.id;
    }

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

    public String getFirstname() {
        return this.firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return this.lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    @Override
    public String toString() {
        StringBuffer sb = new StringBuffer();
        sb.append("Id: ").append(this.id).append(", firstName: ").append(this.firstname).append(", lastName: ")
                .append(this.lastname);
        return sb.toString();
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (id == null || obj == null || getClass() != obj.getClass())
            return false;
        User toCompare = (User) obj;
        return id.equals(toCompare.id);
    }

    @Override
    public int hashCode() {
        return id == null ? 0 : id.hashCode();
    }

}

account:

@Entity
@Table(name = "accounts")
public class Account  implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 2612578813518671670L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Long id;

    @Column(name = "name", length = 50)
    private String name;

    @NotNull
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        StringBuffer sb = new StringBuffer();
        sb.append("Id: ").append(this.id).append(", Name: ").append(this.name);
        return sb.toString();
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (id == null || obj == null || getClass() != obj.getClass())
            return false;
        Account toCompare = (Account) obj;
        return id.equals(toCompare.id);
    }

    @Override
    public int hashCode() {
        return id == null ? 0 : id.hashCode();
    }
}

After added the first user, I was expecting this json posted by postman be correct:

{
"name": "some account purpose",
"user": 1
}

Other tentatives I tried just to help me narrow the error cause were:

{
"name": "some account purpose",
"user": "http://localhost:8080/basicbank/user/1"
}

Result: JsonMappingException: Can not construct instance of com.livingit.basicbank.model.User

and

{
"name": "some account purpose",
"user": {
    "id": 1,
    "firstname": "joao",
    "lastname": "pereira"
    }
}

Result: nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

*** edited (whole stacktrace of starting the app)

2018-01-28 09:27:04.010  INFO 6136 --- [           main] com.mycompany.basicbank.App               : Starting App on win10-cha with PID 6136 (C:\demecarv\_exercicios\wssts\basicbank\target\classes started by dca in C:\demecarv\_exercicios\wssts\basicbank)
2018-01-28 09:27:04.013  INFO 6136 --- [           main] com.mycompany.basicbank.App               : No active profile set, falling back to default profiles: default
2018-01-28 09:27:04.056  INFO 6136 --- [           main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@45dd4eda: startup date [Sun Jan 28 09:27:04 CET 2018]; root of context hierarchy
2018-01-28 09:27:05.319  INFO 6136 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [class org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$36aa5503] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2018-01-28 09:27:05.687  INFO 6136 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-01-28 09:27:05.695  INFO 6136 --- [           main] o.apache.catalina.core.StandardService   : Starting service Tomcat
2018-01-28 09:27:05.696  INFO 6136 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.5
2018-01-28 09:27:05.792  INFO 6136 --- [ost-startStop-1] o.a.c.c.C.[.[localhost].[/basicbank]     : Initializing Spring embedded WebApplicationContext
2018-01-28 09:27:05.792  INFO 6136 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1739 ms
2018-01-28 09:27:05.958  INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'dispatcherServlet' to [/]
2018-01-28 09:27:05.965  INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2018-01-28 09:27:05.966  INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2018-01-28 09:27:05.966  INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2018-01-28 09:27:05.967  INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
2018-01-28 09:27:06.186  INFO 6136 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2018-01-28 09:27:06.196  INFO 6136 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2018-01-28 09:27:06.250  INFO 6136 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.11.Final}
2018-01-28 09:27:06.252  INFO 6136 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2018-01-28 09:27:06.253  INFO 6136 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2018-01-28 09:27:06.293  INFO 6136 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-01-28 09:27:06.936  INFO 6136 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
2018-01-28 09:27:07.059  INFO 6136 --- [           main] o.h.e.j.e.i.LobCreatorBuilderImpl        : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
2018-01-28 09:27:07.061  INFO 6136 --- [           main] org.hibernate.type.BasicTypeRegistry     : HHH000270: Type registration [java.util.UUID] overrides previous : org.hibernate.type.UUIDBinaryType@7d42542
2018-01-28 09:27:07.177  WARN 6136 --- [           main] org.hibernate.orm.deprecation            : HHH90000014: Found use of deprecated [org.hibernate.id.SequenceGenerator] sequence-based id generator; use org.hibernate.id.enhanced.SequenceStyleGenerator instead.  See Hibernate Domain Model Mapping Guide for details.
2018-01-28 09:27:07.400  INFO 6136 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
Hibernate: alter table account_sources drop constraint FKeauonjew5oxigofghc32p2qpk
Hibernate: alter table account_sources drop constraint FKa48g9fvepp3kkbvswmrhoenbo
Hibernate: alter table account_targets drop constraint FKtlh5cw6fckuj0ijvujad745si
Hibernate: alter table account_targets drop constraint FKlrql4tsjy8unw6t22lm1o6xwt
Hibernate: alter table accounts drop constraint FK4xofdnttbbgk99exx2wpccl0s
Hibernate: drop table if exists account_sources cascade
Hibernate: drop table if exists account_targets cascade
Hibernate: drop table if exists accounts cascade
Hibernate: drop table if exists bankuser cascade
Hibernate: drop table if exists transactions cascade
Hibernate: drop sequence hibernate_sequence
Hibernate: create sequence hibernate_sequence start 1 increment 1
Hibernate: create table account_sources (sources_id int8, id int8 not null, primary key (id))
Hibernate: create table account_targets (targets_id int8, id int8 not null, primary key (id))
Hibernate: create table accounts (id  bigserial not null, name varchar(50), user_id int8 not null, primary key (id))
Hibernate: create table bankuser (id  bigserial not null, firstname varchar(50), lastname varchar(50), primary key (id))
Hibernate: create table transactions (id int8 not null, amount numeric(19, 2), primary key (id))
Hibernate: alter table account_sources add constraint FKeauonjew5oxigofghc32p2qpk foreign key (sources_id) references accounts
Hibernate: alter table account_sources add constraint FKa48g9fvepp3kkbvswmrhoenbo foreign key (id) references transactions
Hibernate: alter table account_targets add constraint FKtlh5cw6fckuj0ijvujad745si foreign key (targets_id) references accounts
Hibernate: alter table account_targets add constraint FKlrql4tsjy8unw6t22lm1o6xwt foreign key (id) references transactions
Hibernate: alter table accounts add constraint FK4xofdnttbbgk99exx2wpccl0s foreign key (user_id) references bankuser
2018-01-28 09:27:07.580  INFO 6136 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete
2018-01-28 09:27:07.610  INFO 6136 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-01-28 09:27:08.152  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@45dd4eda: startup date [Sun Jan 28 09:27:04 CET 2018]; root of context hierarchy
2018-01-28 09:27:08.216  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account],methods=[PUT]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.AccountController.updateEmployee(com.mycompany.basicbank.model.Account)
2018-01-28 09:27:08.217  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account],methods=[POST]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.Account> com.mycompany.basicbank.controller.AccountController.addEmployee(com.mycompany.basicbank.model.Account)
2018-01-28 09:27:08.217  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account/{id}],methods=[GET]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.Account> com.mycompany.basicbank.controller.AccountController.getUser(java.lang.Long)
2018-01-28 09:27:08.218  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account],methods=[GET]}" onto public org.springframework.http.ResponseEntity<java.util.List<com.mycompany.basicbank.model.Account>> com.mycompany.basicbank.controller.AccountController.getAllEmployees()
2018-01-28 09:27:08.218  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account/{id}],methods=[DELETE]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.AccountController.deleteEmployee(java.lang.Long)
2018-01-28 09:27:08.219  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user],methods=[PUT]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.UserController.updateEmployee(com.mycompany.basicbank.model.User)
2018-01-28 09:27:08.220  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user],methods=[POST]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.User> com.mycompany.basicbank.controller.UserController.addEmployee(com.mycompany.basicbank.model.User)
2018-01-28 09:27:08.220  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user/{id}],methods=[GET]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.User> com.mycompany.basicbank.controller.UserController.getUser(java.lang.Long)
2018-01-28 09:27:08.220  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user],methods=[GET]}" onto public org.springframework.http.ResponseEntity<java.util.List<com.mycompany.basicbank.model.User>> com.mycompany.basicbank.controller.UserController.getAllEmployees()
2018-01-28 09:27:08.220  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user/{id}],methods=[DELETE]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.UserController.deleteEmployee(java.lang.Long)
2018-01-28 09:27:08.222  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2018-01-28 09:27:08.223  INFO 6136 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2018-01-28 09:27:08.250  INFO 6136 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-01-28 09:27:08.250  INFO 6136 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-01-28 09:27:08.283  INFO 6136 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-01-28 09:27:08.520  INFO 6136 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-01-28 09:27:08.572  INFO 6136 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-01-28 09:27:08.577  INFO 6136 --- [           main] com.mycompany.basicbank.App               : Started App in 4.872 seconds (JVM running for 5.477)

*** edited, the whole stacktrace when trying to add an account

{
"name": "some account purpose",
"user": 1
}

    2018-01-28 13:14:29.047  INFO 6136 --- [nio-8080-exec-1] o.a.c.c.C.[.[localhost].[/basicbank]     : Initializing Spring FrameworkServlet 'dispatcherServlet'
    2018-01-28 13:14:29.047  INFO 6136 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
    2018-01-28 13:14:29.121  INFO 6136 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 74 ms
    Hibernate: insert into accounts (name, user_id) values (?, ?)
    2018-01-28 13:14:29.387  WARN 6136 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23503
    2018-01-28 13:14:29.387 ERROR 6136 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: insert or update on table "accounts" violates foreign key constraint "fk4xofdnttbbgk99exx2wpccl0s"
      Detail: Key (user_id)=(1) is not present in table "bankuser".
    2018-01-28 13:14:29.434 ERROR 6136 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/basicbank] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [fk4xofdnttbbgk99exx2wpccl0s]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

    org.postgresql.util.PSQLException: ERROR: insert or update on table "accounts" violates foreign key constraint "fk4xofdnttbbgk99exx2wpccl0s"
      Detail: Key (user_id)=(1) is not present in table "bankuser".
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458) ~[postgresql-9.4.1211.jre7.jar:9.4.1211.jre7]
        at ...

org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.5.jar:8.5.5]
        at java.lang.Thread.run(Unknown Source) [na:1.8.0_112]

*** After Madushan Perera suggestion I still don't see progress:

Account.java

...
    @NotNull
    @ManyToOne(fetch = FetchType.LAZY, targetEntity = User.class)
    @JoinColumn(name = "user_id", insertable = false, updatable = false)
    private User user;
...

And I tried to post via PostMan

{
"name": "some account purpose",
"user_id": 1
}

And the result is:

"exception": "javax.validation.ConstraintViolationException",
"message": "Validation failed for classes [com.livingit.basicbank.model.Account] during persist time for groups [javax.validation.groups.Default, ]\nList of constraint violations:[\n\tConstraintViolationImpl{interpolatedMessage='may not be null', propertyPath=user, rootBeanClass=class com.livingit.basicbank.model.Account, messageTemplate='{javax.validation.constraints.NotNull.message}'}\n]",

*** edited

*** edited

In order to narrow the error I coded

...
    @Autowired
    private UserService userService;

    @Override
    public Account save(Account entity) {
        entity.setUser(userService.getById(entity.getUser().getId()));
        return accountRepository.save(entity);
...

and I could check that the entity is perfectly filled in. The error is always complaining about user_id is null

org.postgresql.util.PSQLException: ERROR: null value in column "user_id" violates not-null constraint

Then I am assuming it is some problem regard how I create the relationship ManyToOne.

Answer 1

I fixed my issue by making the column id from user matches in both Account and User. Here are the solution:

User.java

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "iduser", unique = true, nullable = false)
private Long iduser;

Account.java

@ManyToOne(fetch = FetchType.LAZY, targetEntity = User.class)
@JoinColumn(name = "iduser", insertable = false, updatable = false)
private User user;

The significant change was before, in Account I had user_id and in User I had id, now both have the same alias.

Answer 2

Also from the SQL side, you have to consider that PostgreSQL has a different syntax if you want to use auto-generated with IDENTITY.

CREATE TABLE user (
    id INT GENERATED ALWAYS AS IDENTITY,
    ...
);

Postgresql IDENTITY details.