[FIXED] Unable to add additional column to an existing table in SpringBoot Hibernate JPA

Issue

I have been trying to modify my schema for some time now; by making changes to the model definition in SpringBoot.

I have also commndLineRunner which would populate the table with some sample data. For some Reason I have not been able to add Columns to my Existing Table.

I did execute an alter table command which is not reasonable and practical most times;( this solved the problem once; but I don't want to resort to this any more ).

I get the feeling this has more to do with the definition of the JPA properties in my SpringBoot MicroService. This looks like this :

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.maximum-pool-size=30
spring.jpa.show-sql = true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update

And this is the error that I am seeing when I execute my SpringBoot Application:

Hibernate: alter table if exists public.package_definition add column no_of_email_review_requests int4 not null

Error executing DDL "alter table if exists public.package_definition add column no_of_email_review_requests int4 not null" via JDBC Statement

Caused by: org.postgresql.util.PSQLException: ERROR: column "no_of_email_review_requests" contains null values

This is the additional Column that I am trying to add into my Entity:

    private int noOfEmailReviewRequests;

Edit Sharing Entity Class:

@Entity
    @Data
    public class PackageDefinition {
    @Id
    private String id;
    private SubscriptionType type;
    private int noOfSMSReviewRequests;
    private int noOfFunnelPages;
    private Integer noOfEmailReviewRequests;
    }

Edit :

There seem to be 2 issues here, but both corresponding to the same table and the column addition/modification. I have renamed the following column in the table has been renamed:

no_review_requests to no_of_sms_review_requests --> this is throwing an error 

And the folloing column has been added :

private Integer noOfEmailReviewRequests;

Solution

At default configurations, You cannot add new column with @NotNull annotation, if you have any data at this table.

There are 2 solutions:

1. Add spring.jpa.properties.hibernate.validator.apply_to_ddl=false to your application.properties.
2. Add column and run without @NotNull annotation. After that, add @NotNull annotation to your entity.

I am not sure 2nd option but first one is totally working solution.

Edit: You can also define the new column with default value like @Column(columnDefinition = "int default 100").

Answered By - gokdeemir
Answer Checked By - Mildred Charles (JavaFixing Admin)