how do you enforce conditional not null check across sql columns

database-design, hibernate, jpa, sql

Solution

You need to define a table level check constraint. The following uses Oracle syntax but most DBMS products will have something pretty similar....

alter table A add constraint a_or_b
    check (
        ( a is not null and b is null ) 
    or
        ( a is null and b is not null ) 
    )
/

edit

In response to your comment I guess it would be

@org.hibernate.annotations.Check(

constraints = “(a is not null and b is null) or (a is null and b is not null)”

)

But as a data modeller and a DBA this is really the sort of thing I would want enforced in the database. I don't think the syntax will really vary that much, if at all, across the different flavours of RDBMS.

Problem

``` create table A (id, col_A, col_B, col_C) ``` id = unique id for every row being persisted either col_A or col_B will have a valid value, but both columns will not have a value for each persisted row at the same time. e.g. ``` insert into A (id, col_A, col_C) values (1, "a", "c") insert into A (id, col_B, col_C) values (1, "b", "c") insert into A (id, col_A, col_C) values (1, "aa", "cc") insert into A (id, col_B, col_C) values (1, "bb", "cc") ``` note: col_A and col_B cannot be merged into a single column as per design. I would like to enforce conditional not null check across col_A and col_B based on the above restriction (i.e. for each row atleast col_A or col_B should be present). How do I acheive that? EDIT: - We would like to support the following databases to start with H2, MySQL, Postgres - We would also like to express the constraints via JPA annotations instead of a database specific syntax - The underlying ORM layer is Hibernate 3.3.x

Original source