Help with constraint

Hi,
I have a table where I want a certain condition with 3 of
its fields. I want only 1 of them not to be null.
For example, if the fields are A,B,C these combinations are ok:
A B C
NULL 3432 NULL
NULL NULL 554
333 NULL NULL
Howere these are not:
A B C
NULL NULL NULL
NULL 5454 554
333 545 6858
I'm not very familiar with the syntax of constraints, and I did this:
check(
case when A is null then 0 else 1 end+
case when B is null then 0 else 1 end+
case when C is null then 0 else 1 end
=1)
but I get a syntax error.
Any ideas?
Thanks!You could do something like this.
check(
A IS NOT NULL
OR B IS NOT NULL
OR C IS NOT NULL)
"Star" wrote:

> Hi,
> I have a table where I want a certain condition with 3 of
> its fields. I want only 1 of them not to be null.
> For example, if the fields are A,B,C these combinations are ok:
> A B C
> NULL 3432 NULL
> NULL NULL 554
> 333 NULL NULL
> Howere these are not:
> A B C
> NULL NULL NULL
> NULL 5454 554
> 333 545 6858
> I'm not very familiar with the syntax of constraints, and I did this:
> check(
> case when A is null then 0 else 1 end+
> case when B is null then 0 else 1 end+
> case when C is null then 0 else 1 end
> =1)
> but I get a syntax error.
> Any ideas?
> Thanks!
>|||That doesn′t work but I appreciate your help.
Even with that condition I can have something like this:
A B C
NULL 5454 554
I want only one of them to be populated.
Patrik wrote:
> You could do something like this.
> check(
> A IS NOT NULL
> OR B IS NOT NULL
> OR C IS NOT NULL)
>
> "Star" wrote:
>|||drop table aa
go
create table aa(a int, b int, c int)
go
alter table aa add constraint chk_abc check(
(a is null and b is null and c is not null) or
(a is null and b is not null and c is null) or
(a is not null and b is null and c is null)
)
go
insert into aa values(1,null,null)
insert into aa values(null,1,null)
insert into aa values(null,null,1)
insert into aa values(1,1,null)
insert into aa values(null,1,1)
insert into aa values(1,null,1)
insert into aa values(1,1,1)
insert into aa values(null,null,null)
go
select * from aa|||I think I got it to work.
Just in case someone is interested:
(case when ([A] is null) then 0 else 1 end + case when ([B] is null)
then 0 else 1 end + case when ([C] is null) then 0 else 1 end = 1)|||Ok, misunderstood you.
Try this
check(
(case isnull(a,-1) when -1 then 0 else 1 end + case isnull(b,-1) when -1
then 0 else 1 end + case isnull(c,-1) when -1 then 0 else 1 end) = 1
)
"Star" wrote:

> That doesn′t work but I appreciate your help.
> Even with that condition I can have something like this:
> A B C
> NULL 5454 554
> I want only one of them to be populated.
>
>
> Patrik wrote:
>|||drop table aa
go
create table aa(a int, b int, c int)
go
alter table aa add constraint chk_abc check(
(a is null and b is null and c is not null) or
(a is null and b is not null and c is null) or
(a is not null and b is null and c is null)
)
go
insert into aa values(1,null,null)
insert into aa values(null,1,null)
insert into aa values(null,null,1)
insert into aa values(1,1,null)
insert into aa values(null,1,1)
insert into aa values(1,null,1)
insert into aa values(1,1,1)
insert into aa values(null,null,null)
go
select * from aa|||On Thu, 26 Jan 2006 13:51:41 -0500, Star wrote:

>Hi,
>I have a table where I want a certain condition with 3 of
>its fields. I want only 1 of them not to be null.
>For example, if the fields are A,B,C these combinations are ok:
>A B C
>NULL 3432 NULL
>NULL NULL 554
>333 NULL NULL
>Howere these are not:
>A B C
>NULL NULL NULL
>NULL 5454 554
>333 545 6858
>I'm not very familiar with the syntax of constraints, and I did this:
>check(
>case when A is null then 0 else 1 end+
>case when B is null then 0 else 1 end+
>case when C is null then 0 else 1 end
>=1)
>but I get a syntax error.
>Any ideas?
Hi Star,
Though you've gotten some alternative formulations, I fail to see why
you would have gotten syntax errors. The code below runs fine for me:
CREATE TABLE Star(A int, B int, C int,
check(case when A is null then 0 else 1 end +
case when B is null then 0 else 1 end +
case when C is null then 0 else 1 end = 1)
)
go
-- Accepted
INSERT INTO Star (A, B, C)
select 1, null, null
union all
select null, 1, null
union all
select null, null, 1
-- Rejected
INSERT INTO Star (A, B, C)
SELECT 1, 1, null
INSERT INTO Star (A, B, C)
SELECT null, null, null
INSERT INTO Star (A, B, C)
SELECT 1, 1, 1
-- Show results
select * from Star
go
DROP TABLE Star
go
Hugo Kornelis, SQL Server MVP|||>From the samp[le data, it looks like they are all positive integers, so
we can use this trick:
CHECK ( COALESCE (SIGN(a), 0)
+ COALESCE (SIGN(b), 0)
+ COALESCE (SIGN(c), 0) = 1)
if you have negative numbers use SIGN (ABS(x)) and if you have zeroes,
use SIGN (ABS(x+1))|||usually problems like this arise when mutually exclusive subtypes are
stored in one table. Is that the case? Are you considering splitting up
the table?