How do you force conditional rather than null checks on sql columns

Question

create table A (id, col_A, col_B, col_C)

id = a unique identifier for each row is saved either col_A or col_B will have a valid value, but both columns will not have values ​​for each stored row at the same time.

eg.

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 combined into one column according to the design.

I would like to force a conditional rather than null check through col_A and col_B based on the above restriction (that is, for each line atleast col_A or col_B should be present). How to do it?

EDIT: 1. We would like to support the following databases, to start with H2, MySQL, Postgres 2. We would also like to express limitations through JPA annotations instead of the specific database syntax 3. The basis of the ORM layer is Hibernate 3.3.x

Answer 1

You need to define a table level check restriction. 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 ) 
    )
/

change

In response to your comment, I assume it will be

@org.hibernate.annotations.Check(

constraints = "(a is not null and b is null) or (a is null and b is not null)"

)

, , . , , , RDBMS.

Answer 2

, , col_a null col_b null. , . a b.

SQL Server :

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)
        )

Answer 3

Triggers Since you did not specify which database, it is difficult for you to give a more detailed answer.