Adding null value to NOT NULL table, org.hibernate.exception.ConstraintViolationException

Question

Using hibernate for data access.

I have a column in database:

varchar(40), default value is set to 0, NOT NULL

I have a case, that user sends null value, and getting error:

Error: org.hibernate.exception.ConstraintViolationException: Cannot insert the value
    NULL into column 'foo', table 'MyTable'; column does not allow nulls. INSERT fails.

And the column in hibernate is defined like this:

@Column(name = "foo")
private String foo;

Where could the problem be? Must I define the default in hibernate annotations or smthing? How? Or any other suggetsions?

If you want to put NULL to that column, why did you define the column as NOT NULL? was there any specific reason? — shan
– shan, Commented Sep 20, 2012 at 10:03
@shan I dont want to put null there, i want to put default value there, when user puts NULL — Jaanus
– Jaanus, Commented Sep 20, 2012 at 10:05
You need to look at the SQL generated though I suspect it is trying to put a NULL value in foo in which case the DB will not try to use the default value. Thus the failure, still don't know why default value of a varchar would be 0 though... — ramsinb
– ramsinb, Commented Sep 20, 2012 at 10:06
Default value for the column comes into effect than the column is NOT assigned, assigning NULL to the column is NOT the same as the above. If you want to specifically enforce NULL -> default value conversion than teh best way would be to remove the assignment -> NOT pass any value for the column. And, as mentioned by "ramsinb" 0 is very unusual default value for varchar column... — Germann Arlington
– Germann Arlington, Commented Sep 20, 2012 at 10:26

Frank Pavageau · Accepted Answer · 2012-09-20 11:41:31Z

2

What did you expect would happen when trying to insert NULL into a NOT NULL column? Either you want to enforce a NOT NULL constraint, and then you need to reject the user input, or you want to accept NULL values and need to specify that the column is nullable using, of course, @Column(nullable = true).

Since you actually want a default value in the column when the user doesn't provide that field, and your code explicitely sets the field value even when it's null (or empty, which is the same for Oracle, for example), I suggest having a smarter setter on the field:

private String foo = "0";

public String getFoo() {
    return foo;
}

public void setFoo(String foo) {
    if (foo != null && !foo.isEmpty()) { // Or StringUtils.isNotBlank(foo)
        this.foo = foo;
    }
}

edited Sep 20, 2012 at 11:41

answered Sep 20, 2012 at 10:03

Frank Pavageau

11.7k1 gold badge45 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jaanus Over a year ago

I dont want to put null there, i want to put default value in database, when user inserts NULL

Frank Pavageau Over a year ago

Then assign a default value on the field: private String foo = "default";?

Jaanus Over a year ago

But my code overwrites it and sets it to null, can i somehow send null to not null column, and in database, when I see null, it will be default value that is set in my DB. Doesn't the "Default" work that way?

Frank Pavageau Over a year ago

No, a default value on a table column doesn't work that way. The default value will be used if the column is not specified in the SQL update/insert, but Hibernate will generate a request with all the mapped fields, so foo will always be there. You have to programatically set the value on your POJO, or you can modify your setter to ignore null/empty values so the default value stays there.

Damian Leszczyński - Vash · Accepted Answer · 2012-09-20 10:06:48Z

0

If you table do not allow null value you sould respect that and adopt your model.

As you are using @Column annotation you should first set the property nullable to false, to support the default value you should also use the column definition.

@Column(name = "foo", nullable = false, columnDefinition = “varchar(40) default 0″)

answered Sep 20, 2012 at 10:06

Damian Leszczyński - Vash

31k9 gold badges64 silver badges95 bronze badges

1 Comment

Jaanus Over a year ago

I added this, still getting same error. When I have varchar, can I have default 0 or must do like default '0' ? Any suggetions?

Nandkumar Tekale · Accepted Answer · 2012-09-20 10:22:09Z

0

Default value for foo would be null as it is class field.

As it's default value you are going to save is 0. Make foo primitive type if possible i.e. int. If foo has to be String, then you may remove not-null constraint, because even if you don't assign value to foo, there is default value 0.

Suggestion : Consider Automatic POJO classes generation using Hibernate-Tools. This will auto generate your POJO classes from existing database.

edited Sep 20, 2012 at 10:22

answered Sep 20, 2012 at 10:05

Nandkumar Tekale

16.2k8 gold badges61 silver badges86 bronze badges

3 Comments

Jaanus Over a year ago

At the moment problem is that the database holds tons of null values in foo , but I want there to be 0 instead of null, and it doesnt do it by default.

Nandkumar Tekale Over a year ago

@Jaanus : Your DB is going to be in inconsistent state. write update query to modify null values to 0. Something like : update table set col=0 where col is null

Jaanus Over a year ago

It doesnt matter, i truncated my table and just need all future records to hold value 0 instead of null. How to be able to do it? Vash solution is close to what I need.

Collectives™ on Stack Overflow

Adding null value to NOT NULL table, org.hibernate.exception.ConstraintViolationException

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related