Help with constraint

26 Jan 2006 6:51 PM

Star

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!

26 Jan 2006 7:25 PM

Patrik

You could do something like this.

check(
A IS NOT NULL
OR B IS NOT NULL
OR C IS NOT NULL)

Show quote

"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!
>

26 Jan 2006 7:39 PM

Star

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:
Show quote

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

26 Jan 2006 7:44 PM

Alexander Kuznetsov

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

26 Jan 2006 8:02 PM

Patrik

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
)

Show quote

"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:
> > 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!
> >>
>

26 Jan 2006 7:43 PM

Star

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)

26 Jan 2006 8:28 PM

Alexander Kuznetsov

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

26 Jan 2006 9:13 PM

Hugo Kornelis

On Thu, 26 Jan 2006 13:51:41 -0500, Star wrote:

Show quote

>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

26 Jan 2006 9:17 PM

--CELKO--

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

26 Jan 2006 10:47 PM

Alexander Kuznetsov

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?