Adding a NotNull Column to existing Table - best Practice

Santonian · **Joined:** Mon Aug 21, 2006 12:44 pm **Posts:** 12

Hi,

we have a project we creating an update for at the moment. We have several database enhancements for our project.
Some columns that are added to the existing (and already filled) database should be @NotNull. Is there a hibernate sollution to aquirre this?

The Update itself works pretty well. The problem of couse is, that the columns are added to the existing table but they contain null values. Can I provide some kind of Default Value or something?

Whats the best way to do this?

cheers
Santo.

ps. we are working with annotations.

Simpor · **Joined:** Wed Oct 11, 2006 4:54 am **Posts:** 5

I'm having the same question and since no one has responed yet I'm writing here.

Is this possible??

Santonian · **Joined:** Mon Aug 21, 2006 12:44 pm **Posts:** 12

I still don't know if there is a hibernate sollution.

But I can tell you how we did it.
We created the column with a SQL script and filled it with a default value before we applied our program patch.

It seems that that's the way to do it.

hope it helps

cheers
Santo.

maciejziaja · **Joined:** Thu May 17, 2007 9:49 am **Posts:** 9

Santonian wrote:

Some columns that are added to the existing (and already filled) database should be @NotNull. Is there a hibernate sollution to aquirre this?

Code:

@Column(nullable = false)

Santonian wrote:

The Update itself works pretty well. The problem of couse is, that the columns are added to the existing table but they contain null values. Can I provide some kind of Default Value or something?

In hibernate annotations you can not specify default value for a column. You can use this feature if you use xml files for mapping. You can use some walkaround, it is not perfect, but it works for me. You can put default values in the constructor.

Code:

public MyClass() {
    this.value = 3;
}

Maciej

Santonian · **Joined:** Mon Aug 21, 2006 12:44 pm **Posts:** 12

I think you misunderstood the problem.

The Problem is to run an update on an existing and filled database where hibernate automatically adds new columns to existing and filled tables.
And when hibernate tries to add a @NotNull column, it will fail.

Like I said before, there ist most likely no hibernate way to do it.

s.

maciejziaja · **Joined:** Thu May 17, 2007 9:49 am **Posts:** 9

Ok, now I think i know what you want to achieve and it is not possible to get it with hibernate schema update.

Quote:

The hibernate schema update will:
* create a new table
* add a new column
The hibernate schema update will not:
* drop a table
* drop a column
* change a constraint on a column
* add a column with a not-null constraint to an existing table

Santonian · **Joined:** Mon Aug 21, 2006 12:44 pm **Posts:** 12

maciejziaja wrote:

Ok, now I think i know what you want to achieve and it is not possible to get it with hibernate schema update.

Quote:

The hibernate schema update will:
* create a new table
* add a new column
The hibernate schema update will not:
* drop a table
* drop a column
* change a constraint on a column
* add a column with a not-null constraint to an existing table

yep, thats what I figured

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

why can't it do that? That would be awesome and solve alot of work that everyone does for database updates? Then I can add new notnull columns and tell it what to fill that column with..that would rock!!!

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

has anyone tried this ...

@Column(nullable=false, columnDefinition = "DATE DEFAULT CURRENT_DATE")

I think it might run that aafter adding the column to do what we all want.

jtheory · **Joined:** Thu Jun 16, 2011 5:38 am **Posts:** 1

Sorry to resurrect an old thread, but this post is right at the top of the Google results when I searched for this problem, with no definitive answer included.

I needed to add a new, not null boolean column to an existing table with data (and we use annotations for configuration).

This worked:

Code:

@Column(nullable=false, columnDefinition = "BOOLEAN DEFAULT FALSE")

The column was added, and the value was "false" for all pre-existing rows.