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开发者_StackOverflow")
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
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.
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)
)
Triggers. Since you haven't specified which database, it's kind of hard to give a more detailed answer.
精彩评论