how do you enforce conditional not null check across sql columns

Question

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:

1. We would like to support the following databases to start with H2, MySQL, Postgres
2. We would also like to express the constraints via JPA annotations instead of a database specific syntax
3. The underlying ORM layer is Hibernate 3.3.x

Answer 1

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.

Answer 2

You can use a constraint that checks col_a is not null or col_b is not null. This will give you an error if both columns are null or if both columns are not null. It will only allow records with either a or b.

In SQL Server would be like:

ALTER TABLE [dbo].[A]  
WITH CHECK ADD  CONSTRAINT [CK_test] 
   CHECK  (([col_a] IS NOT NULL and [col_b] IS NULL)
        or ([col_a] IS NULL and [col_b] IS NOT NULL)
        )