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?

Help with a SQL Query

I have a if condition in a query for a columnA <> 1 but this columnA in the table is null and my IF query fails - How do I need to structure my query:
If rsX = 1 Or rsX = 2 AND columnA <> 1 Then
ANy help will be appreciated.
Thanks!!if you really did mean to connect columnA<>1 with rsX=2 then what you want is this --
select rsX
, columnA
, otherstuff
from yourtable
where rsX = 1
or (
rsX = 2
and ( columnA is null
or columnA <> 1
)
)if not, then you probably want this --
select rsX
, columnA
, otherstuff
from yourtable
where ( rsX = 1
or rsX = 2
)
and ( columnA is null
or columnA <> 1
)|||Thanks for your reply - Now I have rewritten my query as -
If ((rsX = 1 Or rsX = 2) AND (columnA is NULL OR columnA <> 1)) Then

I am getting a run time error 424 in VB -object required - Any ideas??|||nope, sorry, no idea, i don't know VB

however, you may want to try SELECT ... WHERE instead of IF ... THEN

you should let the database engine select just the rows you want

rudy|||What kind of variable is columnA?

VB only supports the Null value for certain data types.

Originally posted by sri2003
Thanks for your reply - Now I have rewritten my query as -
If ((rsX = 1 Or rsX = 2) AND (columnA is NULL OR columnA <> 1)) Then

I am getting a run time error 424 in VB -object required - Any ideas??|||Please post your entire SQL query, not just the IF clause.

blindman|||The variable for the column is Char.|||The SQL query is select all the records from the table it is a simple SQL - Something like Select a,b,c,d,e from tableDummy. The in VB is where I do the IF condition for the recordset that is:
If rsX = 1 Or rsX = 2 AND columnA <> 1 - do you want me to post the entire RecordSet - please let me know.
Thanks!|||Ok, the column is of type char, but a variable in VB of type String is not able to hold a Null value.

Is it a VBScript?

What are the variables declared as? (if they are explicitly declared)

Is there some reason you do not directly use the fields from the recordset in the comparison?
((rs.rsX = 1 Or rs.rsX = 2) AND (rs.ColumnA Is Null Or rs.ColumnA <> 1))

Also, as some suggested, it might be a better idea to do the entire filtering in the WHERE clause if possible.