10

I have a table with three fields, say a, b, c. I would like to add a constraint ensuring that if a is not null, then also b and c are not null. I have done that using following SQL

ALTER TABLE sample 
ADD CONSTRAINT no_nulls
CHECK (CASE WHEN a IS NOT NULL THEN b IS NOT NULL AND c IS NOT NULL END)

Is there a way to achieve same effect using hibernate annotation @Check?

I can't find a helpful example with that annotation, do developers tend not to use it at all?

blahblah
  • 1,161
  • 2
  • 14
  • 30

2 Answers2

9

Yes it is possible if @Check is used at class level like this:

@Entity
@Check(constraints = "COL_A IS NULL OR (COL_B IS NOT NULL and COL_C IS NOT NULL)")
public class Sample {

    @Column(name = "COL_A")
    private Long a;

    @Column(name = "COL_B")
    private Long b;

    @Column(name = "COL_C")
    private Long c;

}

(Note that I rewrote your condition using @jarlh comment.). The constraints clause of @Check annotation needs to refer to the name attribute of @Column (it must be pure SQL).

@Check annotation needs to be at class level because of a Hibernate bug.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
  • Suppose i have: //////////// @Column(name = "sample") private Long a; ////////// Which one should I use in check constraint? 'a' or 'sample'? I guess the question is: Is there HQL or SQL used? – blahblah Aug 13 '15 at 09:17
  • 4
    Apparently `@Check` can only be used once, at class level. How can we add multiple constraints? – Stefan Falk Apr 03 '18 at 18:46
  • Any idea if this is possible, I have multiple named check constraints but no way to add them – BrendanM Oct 22 '21 at 11:18
0

Use next annotation at Class level to check all three columns, e.g.:

@Check(constraints = "COL_A >= 15 AND COL_B < 200 AND COL_C > 100")

being COL_A, COL_B and COL_C the column names to be checked

Do not check nullable columns. @NotNull is preferred over @Check.

Jesus Marin
  • 1
  • 2
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 30 '21 at 05:13