Menu
I have a table in SQL Server with two numeric columns. At least one of these numeric fields must be filled. How do I write a check constraint to verify this?
206
A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL) . ); In the case we are looking at, a CHECK CONSTRAINT can enforce non NULL values.
Use a CHECK constraint. CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to UNKNOWN and the row is accepted. Only when the condition evaluates to FALSE , the row is rejected.
You can define CHECK constraints at the column level, where the constraint applies only to a single column, and at the table level. You can also add CHECK constraints to a table using ADD CONSTRAINT .
This can be done with a check constraint that verifies null value and matches the result with or
create table #t (i int , j int , constraint chk_null check (i is not null or j is not null)) The following are the test cases
insert into #t values (null, null) --> error insert into #t values (1, null) --> ok insert into #t values (null, 1) --> ok insert into #t values (1, 1) --> ok late answer, but here is a solution for Sql Server for any number of columns to check:
CONSTRAINT CK_one_is_not_null CHECK (COALESCE(col1, col2, col3) IS NOT NULL ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
