Error when trying to add a new boolean column to a table which already has data

Question

I tried adding a new attribute to an entity Customer which already has data stored in the database:

private boolean isArchived;

This gives me the following error when i start spring and try to update the tables:

Error executing DDL "alter table customer add column is_archived boolean not null" via JDBC Statement

Is that because he tries to initialize isArchived with null for the already existing rows and the primitive type cant be null? Is there a way to just initialize them with false instead or do i have to use Boolean instead of boolean to allow null values(which i would like not to allow)?

I think it's related to config which related to updating schema `spring.jpa.hibernate.ddl-auto = create` — Rajat, Dec 18 '19 at 13:16
Fast tip: try to **not use** ddl-auto on production: https://stackoverflow.com/questions/221379/hibernate-hbm2ddl-auto-update-in-production/36317695#36317695 — Dherik, Dec 18 '19 at 13:59

score 7 · Answer 1 · answered Dec 18 '19 at 13:18

7

It's because existing rows can't have null values, with the new non-null column. The way to do is to provide a default value. You can do it like below:

@Column(nullable=false, columnDefinition = "BOOLEAN DEFAULT FALSE")
private boolean isArchived;

answered Dec 18 '19 at 13:18

Shafiul

1,452
14
21

So that way every value was default false(as it should be), but it was still nullable. So `nullable = false` didnt do anything. – M.Dietz Dec 18 '19 at 13:59
How do you generate the schema? If using hibernate, then it should be non-null at schema level. If you are using hibernate in pre-existing schema, then this annotation is hint for the persistent provider for bean validation, but at db level, it won't change anything. @M.Dietz – Shafiul Dec 19 '19 at 17:03

score 3 · Answer 2 · answered Dec 18 '19 at 13:22

3

Existing rows cannot have null values so you can set the default value with one of the following options:

@Column(columnDefinition="tinyint(1) default 0")
private boolean isArchived;

private boolean isArchived = false;

answered Dec 18 '19 at 13:22

Asim Irshad

96
3

score 0 · Answer 3 · answered Dec 18 '19 at 16:01

Is that because he tries to initialize isArchived with null for the already existing rows and the primitive type cant be null?

No, it's because apparently, the database already has NULL values for some rows, and so the RDBMS cannot enforce the constraint you're trying to implicitly create by using a primitive type.

The solution is, simply, to apply a db migration to existing data that will populate the is_archived column for the problematic rows before the DDL gets executed.

Error when trying to add a new boolean column to a table which already has data

3 Answers3