I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksTry this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>|||Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
Showing posts with label condition. Show all posts
Showing posts with label condition. Show all posts
Friday, March 23, 2012
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>sql
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>sql
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
Thanks
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>
|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegro ups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
Thanks
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>
|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegro ups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
Monday, March 19, 2012
exclude condition
i have two tables: "Person" and "Year". "Person" can have many "Year"(one to many relation). i want a query which returns all the recordsfrom "Person" where "Year" is 2005 but exclude if there is any "Year"with 2004. how can i write that query? any help will be appreciated.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records fromPerson where there is no any year with 2004 but only 2005. If a personhas both 2004 and 2005 exclude that person.
My first thought is this:
It could also be accomplished like this:
Of the 2, I think the second option will perform better. You should run both in Query Analyzer and decide for yourself.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records fromPerson where there is no any year with 2004 but only 2005. If a personhas both 2004 and 2005 exclude that person.
My first thought is this:
SELECT
*
FROM
Person
LEFT OUTER JOIN
Year AS Year2004 ON Person.Id = Year.PersonID AND Year2004 = 2004
LEFT OUTER JOIN
Year AS Year2005 ON Person.Id = Year.PersonID AND Year2005 = 2005
WHERE
Year2004.PersonID IS NULL AND
Year2005.PersonID IS NOT NULL
It could also be accomplished like this:
SELECT
*
FROM
Person
WHERE
NOT EXISTS(SELECT PersonID FROM Year WHERE Person.Id = Year.PersonID AND Year = 2004) AND
EXISTS(SELECT PersonID FROM Year WHERE Person.Id = Year.PersonID AND Year = 2005)
Of the 2, I think the second option will perform better. You should run both in Query Analyzer and decide for yourself.
Friday, March 9, 2012
Excessive blocking - runnable processes
Hi there,
Currently using SQL Server 2000 (SP4). The following condition started occurring last week:
- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking
Any help would be greatly appreciated.
Thanks.Did the blocking happen with the user tables or in tempdb?|||It happens on the user tables... Thanks.|||This is probably a fault of the database application, not the server. Poorly designed schemas and bad code are the primary culprits of blocking, which may not occur until the application use scales beyond that which it was tested.
Try to figure out which sprocs or statements are causing the blocking, and then try to optimize them.|||Since your job actually finishes, and it did not happened at night. My guess would be:
1. Some process running long update transaction, or
2.|||2. Your IO time slows down due to workload, that may increases chance of blocking too.|||If you cannot spot abnormalties in profiler, have a look at IO time.|||Thanks for all your replies. It was pointing to an application issue. Thanks again.
Currently using SQL Server 2000 (SP4). The following condition started occurring last week:
- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking
Any help would be greatly appreciated.
Thanks.Did the blocking happen with the user tables or in tempdb?|||It happens on the user tables... Thanks.|||This is probably a fault of the database application, not the server. Poorly designed schemas and bad code are the primary culprits of blocking, which may not occur until the application use scales beyond that which it was tested.
Try to figure out which sprocs or statements are causing the blocking, and then try to optimize them.|||Since your job actually finishes, and it did not happened at night. My guess would be:
1. Some process running long update transaction, or
2.|||2. Your IO time slows down due to workload, that may increases chance of blocking too.|||If you cannot spot abnormalties in profiler, have a look at IO time.|||Thanks for all your replies. It was pointing to an application issue. Thanks again.
Subscribe to:
Posts (Atom)