2K5: How can I enforced the requirement that [at least one of 3] columns be
NOT NULL? I don't care if 0, 1, or 2 of the 3 columns have a NULL value - I
just want to require at least one of the 3 columns to have  a value [not
NULL] when a new row is inserted - and I don't care which one has the [not
NULL] value. (all columns in question are varchar(20)).

Thanks

CHECK LEN
(
    COALESCE(colA,'')
    + COALESCE(colB,'')
    + COALESCE(colC,'')
    + COALESCE(colD,'')
) > 0

....or...

CHECK
(
    ColA IS NOT NULL
    OR ColB IS NOT NULL
    OR ColC IS NOT NULL
    OR ColD IS NOT NULL
)



Show quoteHide quote

Aaron,

I liked your idea for the Check constraint using coalesce. However, I am having some confusion about how it would work for disparate column datatypes.

I just couldn't make it work. (As you see, I tried using the len()/ coalesce() combination you suggested, and even tried using len() without coalesce. The only option that worked for me was the   OR {ColumnName} IS NOT NULL  .)

Help me understand what I'm doing wrong...

CREATE TABLE #MyTable
   (    RowID        int      IDENTITY
      , CaseNumber   int
      , EntryDate    datetime
      , PerpName     varchar(20)
   )

ALTER TABLE #MyTable
--    ADD CONSTRAINT OneColumnNeeded CHECK ( len( coalesce( CaseNumber, '') +
--                                                coalesce( EntryDate, '') +
--                                                coalesce( PerpName, '' )
--                                              ) > 0
--                                         )
   ADD CONSTRAINT OneColumnNeeded CHECK ( ( len( CaseNumber) +
                                            len( EntryDate ) +
                                            len( PerpName )
                                          ) > 0
                                        )


--    ADD CONSTRAINT OneColumnNeeded CHECK (    CaseNumber IS NOT NULL
--                                           OR EntryDate IS NOT NULL
--                                           OR PerpName IS NOT NULL
--                                         )

INSERT INTO #MyTable VALUES ( NULL, NULL, NULL )
INSERT INTO #MyTable VALUES ( 123, NULL, NULL )
INSERT INTO #MyTable VALUES ( NULL, '08/30/2006', NULL )
INSERT INTO #MyTable VALUES ( NULL, NULL, 'Smith, John' )

SELECT
     RowID
   , CaseNumber
   , EntryDate
   , PerpName
FROM #MyTable

DROP TABLE #MyTable

Show quoteHide quote

From the OP:

A

True.

Not arguing about your suggestion, just trying to extrapolate it to other
scenarios and understand the cautions.

Show quoteHide quote

Arnie,

This is an option:


ALTER TABLE #MyTable ADD CONSTRAINT OneColumnNeeded
  CHECK (
    CASE WHEN CaseNumber IS NULL THEN 0 ELSE 1 END +
    CASE WHEN EntryDate IS NULL THEN 0 ELSE 1 END +
    CASE WHEN PerpName IS NULL THEN 0 ELSE 1 END
Steve Kass
Drew University
www.stevekass.com

Arnie Rowland wrote:

Show quoteHide quote

On Wed, 30 Aug 2006 17:41:19 -0700, Arnie Rowland wrote:

Hi Arnie,

The best way to understand what you're doing wrong is to inspect some
intermediate results. Just run the following in SQL Server Management
Studio or Query Analyzer and check out the results.

SELECT CAST(null AS varchar(20))
SELECT LEN(CAST(null AS varchar(20)))
SELECT COALESCE(CAST(null AS varchar(20)), '')
SELECT LEN(COALESCE(CAST(null AS varchar(20)), ''))
SELECT CAST(null AS int)
SELECT LEN(CAST(null AS int))
SELECT COALESCE(CAST(null AS int), '')
SELECT LEN(COALESCE(CAST(null AS int), ''))
SELECT CAST(null AS datetime)
SELECT LEN(CAST(null AS datetime))
SELECT COALESCE(CAST(null AS datetime), '')
SELECT LEN(COALESCE(CAST(null AS datetime), ''))

NOT NULL? <<

COALESCE (col1, col2, col3) IS NOT NULL

Joe,

That doesn't work with disparate column datatypes.

Try:

CREATE TABLE #MyTable
   (    RowID        int      IDENTITY
      , CaseNumber   int
      , EntryDate    datetime
      , PerpName     varchar(20)
   )

ALTER TABLE #MyTable
   ADD CONSTRAINT OneColumnNeeded CHECK ( coalesce( CaseNumber, EntryDate, PerpName ) IS NOT NULL )

INSERT INTO #MyTable VALUES ( 123, NULL, NULL )
INSERT INTO #MyTable VALUES ( NULL, '08/30/2006', NULL )
INSERT INTO #MyTable VALUES ( NULL, NULL, 'Smith, John' )

DROP TABLE #MyTable

Show quoteHide quote

Yes, it should.  NULL does not have a data type, so the expression
should cast to the "highest" data type in the list, just as all other
members of the CASE family are required to do.

I am getting ready to go to bed and catch a plane, so I cannot test
how well SQL Server met Standards, tho.

Yes, only if all 3 values are NULL.

The COALESCE won't work when you have >1 NOT NULL.with the different data
type.

Show quoteHide quote

On 30 Aug 2006 20:28:36 -0700, --CELKO-- wrote:

Hi Joe,

Would you please kindly explain what a product that meets the standards
would return from

  SELECT COALESCE (CAST(null AS int),
                   CAST(null AS datetime),
                   CAST('Smith, John' AS varchar(20)));

Since int is the datatype with highest precedence of the three used
here, and since 'Smmith, John' doesn't convert to int all that well, I'd
expect a run-time error. Which is exactly what SQL Server gives me. But
obviously, you know better?

Your SELECT also raises an error when run on the standards-compliant
OCELOT .
"[OCELOT][OCELOT]Data exception: Data type <CHARACTER> cannot be
assigned to data type <TIMESTAMP>"
(datetime, called timestamp in OCELOT, has the highest precedence of the
three types here.)

In addition, OCELOT raises an error if NULL is used as a value without
having been assigned or cast a type.

Steve Kass
Drew University

Hugo Kornelis wrote:

Show quoteHide quote

I suppose a workaround, using standard SQL, would be to explicitly cast
everything as a varchar first?

Show quoteHide quote

On Tue, 5 Sep 2006 15:13:47 -0400, Jim Underwood wrote:

Hi Jim,

AFAIK, every datatype can be converted to varchar - assuming you meant
varchar(max), as varchar(8000) would be too short for some datatypes.

So yes, that should work in this case.

AFAIK, you can't directly CAST image or CLR UDT's as varchar (you can cast
them as binary and then cast the result as varchar), but anyone who would
put one of those in a COALESCE with other datatypes has big problems <grin>

Tom

Show quoteHide quote

SELECT COALESCE (CAST (NULL AS INTEGER),
CAST (NULL AS TIMESTAMP),
CAST ('Smith, John' AS VARCHAR(20)))  <<

  Straight substitution in the definition and using TIMESTAMP instead
of the proprietary DATETIME would give us

CASE WHEN CAST (NULL AS INTEGER) IS NOT NULL
          THEN CAST (NULL AS INTEGER)
        ELSE CASE WHEN CAST (NULL AS TIMESTAMP) IS NOT NULL
                  THEN CAST (NULL AS TIMESTAMP)
                   ELSE CASE
                            WHEN CAST ('Smith, John' AS VARCHAR(20)) IS
NOT NULL
                             THEN CAST ('Smith, John' AS VARCHAR(20))
                            ELSE NULL END;

Now the expression is cast to the highest data type in all of the THEN
clauses - even if it is impossible to execute them. That would be
VARCHAR(20). Here is the quote:

SELECT COALESCE (CAST (NULL AS INTEGER),
CAST (NULL AS TIMESTAMP),
CAST ('Smith, John' AS VARCHAR(20)))

Here is the quote about CASE expressions:

...
2) COALESCE (V1, V2) is equivalent to the following <case
specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the
following <case specification>:

CASE WHEN V1 IS NOT NULL
THEN V1
ELSE COALESCE (V2, . . . ,n ) END

...

5) At least one <result> in a <case specification> shall specify a
<result expression>.

6) If an <else clause> is not specified, then ELSE NULL is implicit.

7) The data type of a <case specification> is determined by applying
Subclause 9.3, "Set operation result data types", to the data types of
all <result expression>s in the <case specification>.

====
9.3 Set operation result data types

Function

Specify the Syntax Rules and result data types for <case expression>s
and <query expression>s having set operators.

Syntax Rules

1) Let DTS be a set of data types specified in an application of this
Subclause.

2) All of the data types in DTS shall be comparable.

3) Case:

a) If any of the data types in DTS is character string, then all data
types in DTS shall be character string, and all of them shall have the
same character repertoire. That character repertoire is the character
repertoire of the result. The character set of the result is the
character set of one of the data types in DTS. The specific character
set chosen is implementation-dependent. The collating sequence and the
coercibility attribute are determined as specified in Table 2,
"Collating coercibility rules for dyadic operators".

Case:

i) If any of the data types in DTS is variable-length character
string, then the result data type is variable-length character string
with maximum length in characters equal to the maximum of the lengths
in characters and maximum lengths in characters of the data types in
DTS.

ii) Otherwise, the result data type is fixed-length character string
with length in characters equal to the maximum of the lengths in
characters of the data types in DTS.

b) If any of the data types in DTS is bit string, then all data types
in DTS shall be bit string.

Case:

i) If any of the data types in DTS is variable-length bit string, then
the result data type is variable-length bit string with maximum length
in bits equal to the maximum of the lengths in bits and maximum lengths
in bits of the data types in DTS.

ii) Otherwise, the result data type is fixed-length bit string with
length in bits equal to the maximum of the lengths in bits of the data
types in DTS.

c) If all of the data types in DTS are exact numeric, then the result
data type is exact numeric with implementation-defined precision and
with scale equal to the maximum of the scales of the data types in DTS.

d) If any data type in DTS is approximate numeric, then each data type
in DTS shall be numeric and the result data type is approximate numeric
with implementation-defined precision.

e) If any data type in DTS is a datetime data type, then each data
type in DTS shall be the same datetime data type. The result data type
is the same datetime data type.

f) If any data type in DTS is interval, then each data type in DTS
shall be interval. If the precision of any data type in DTS specifies
YEAR or MONTH, then the precision of each data type shall specify only
YEAR or MONTH. If the precision of any data type in DTS specifies DAY,
HOUR, MINUTE, or SECOND(N), then the precision of no data type of DTS
shall specify the <datetime field>s YEAR and MONTH. The result data
type is interval with precision "S TO E", where S and E are the most
significant of the <start field>s and the least significant of the <end
field>s of the data types in DTS, respectively.

General Rules

None.

On 5 Sep 2006 15:33:28 -0700, --CELKO-- wrote:

Hi Joe,

I snipped the equivalent CASE expression and the part of the standard
that describes this definition. I have no problems with that.

I do have a problem with the statement that VARCHAR(20) is the highest
data type in all of the THEN clauses. I've just spent two hours reading
through both the SQL-92 and the SQL-2003 standard, but I was unable to
locate the list that defines which data type is "highest" if data types
are mixed in an expression. I know where to find the SQL Server list of
data type precedence (search BOL for "precedence, data types", or for
"data types, precedence"); that list says that int has higher precedence
than varchar. You seem to think that the standard says otherwise, but
even though you provide several quotes from the standard, there's none
to support this statement.

In fact, I can't even find support for your statement that the CASE
expression is cast to the highest data type used in the THEN clauses.
Here's the relevant part of subclause 9.3 of the SQL-92 standard that
you so kindly posted. (BTW, I checked for relevant changes in the
SQL-2003 standard, but I found none).

In this case, it is true that any of the data types is character string.
So the standard says that all should be string - but they are not. As I
read it, the standard doesn't alllow character data types and other data
types to be mixed in a CASE expression.

Doesn't apply, since there are no bit strings in the CASE expression.

Doesn't apply - only one of the data types is exact numeric data type,
not all.

Doesn't apply. Integer is exact numeric, varchar and datetime are not
numeric at all.

Applies - and just as in case a), the standard says that all data types
should be datetime. Another reason for an error.

No interval data type in SQL Server, so this will never apply.

Of the six cases, four don't apply and two call for an exception for
improper use of the CASE statement. I couldn't find anything to support
the idea that the standard allows the use of mixed data types in a CASE
statement, let alone any description as to what the resulting data type
should be.

So that raises the question why you defended an expression that
a) is not even allowed in standard SQL, and
b) is allowed but prone to run-time errors in Transact-SQL

hierarchical query 2005
problem with select
Detect Changes Using Checksum
Trigger creation loop on user tables?
Force Leading Zeroes