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

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.

Answer 2

I got this error, slightly different from actual question which is:  org.springframework.dao.dataintegrityviolationexception: could not execute statement; sql [n/a]; constraint [null]; nested exception is org.hibernate.exception.constraintviolationexception: could not execute statement

Public Account MethodName(@RequestBody Account account){        
    account.setName("some account purpose");  
          
    User user = new User();   
    user = userService.getById(Long.parseLong(account.getUserId()));
    account.setUser(user);  //because user is User type of Account Model Class
    
    Account accountObject= AccoundService.save(account);
     
    return accountObject;
    
    }

Answer 3

In my case , I had a password varchar(30) field in user table in postgres sql and I was saving encrypted password into the user table in the database. e.g. using user.setPassword(passwordEncoder.encode(user.getPassword())); where after password encryption ,password exceeded the limt of 30 characters. Please make sure that you have correct data types in your model classes and in sql table schema,and sql table columns have correct length for the corresponding fields. Hope this helps.

Answer 4

you can try

@Query(value="select c from table c where c.name = ?1",nativeQuery = true)

for your native queries

Answer 5

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.