Simplifying CHECK constraint

1 Sep 2005 10:55 AM

Igor Solodovnikov

I have table with following DDL:

CREATE TABLE [dbo].[DiscTable] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [PeriodStart] [datetime] NULL ,
    [PeriodStop] [datetime] NULL ,
) ON [PRIMARY]

Our business rules require PeriodStart and PeriodStop fields must have
equal NULL state. I can ensure that by following CHECK constraint:

ALTER TABLE [dbo].[DiscTable] WITH NOCHECK ADD
    CONSTRAINT [CK_DiscTable_Period] CHECK ((PeriodStart IS NULL AND
PeriodStop IS NULL) OR (PeriodStart IS NOT NULL AND PeriodStop IS NOT
NULL))
GO

But from my point of view this constraint look long and ugly. Is there any
way to rewrite it to be shorter?

1 Sep 2005 11:11 AM

David Portas

I think your constraint is concise and readable enough but I hope the
table design you posted isn't your real one. The only non-nullable
column is IDENTITTY so you don't have a logical key at all. If this is
a true representation of your table then normalizing the design would
probably eliminate the need for the CHECK constraint altogether.

--
David Portas
SQL Server MVP
--

1 Sep 2005 1:18 PM

Igor Solodovnikov

Thank you for response. This is not real table. I have dropped about 10
columns to clearly demonstrate my question.

On Thu, 01 Sep 2005 14:11:54 +0300, David Portas
<REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote:

Show quote

> I think your constraint is concise and readable enough but I hope the
> table design you posted isn't your real one. The only non-nullable
> column is IDENTITTY so you don't have a logical key at all. If this is
> a true representation of your table then normalizing the design would
> probably eliminate the need for the CHECK constraint altogether.
>

1 Sep 2005 1:25 PM

David Portas

A table design problem isn't clearly demonstrated unless we know the
keys. You didn't post any keys so I could only guess. Please include
keys as well as relevant constraints when you post DDL.

--
David Portas
SQL Server MVP
--

1 Sep 2005 11:14 AM

Uri Dimant

Igor
You can write an UDF and ADD CONSTRAINT with reference to this UDF but I
have my doubt about performnce in that case.

Show quote

"Igor Solodovnikov" <IgorSolodovni***@discussions.microsoft.com> wrote in
message news:op.swe7bjqcn8ihmu@iw2k.helpmicro.local...
>I have table with following DDL:
>
> CREATE TABLE [dbo].[DiscTable] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [PeriodStart] [datetime] NULL ,
> [PeriodStop] [datetime] NULL ,
> ) ON [PRIMARY]
>
> Our business rules require PeriodStart and PeriodStop fields must have
> equal NULL state. I can ensure that by following CHECK constraint:
>
> ALTER TABLE [dbo].[DiscTable] WITH NOCHECK ADD
> CONSTRAINT [CK_DiscTable_Period] CHECK ((PeriodStart IS NULL AND
> PeriodStop IS NULL) OR (PeriodStart IS NOT NULL AND PeriodStop IS NOT
> NULL))
> GO
>
> But from my point of view this constraint look long and ugly. Is there any
> way to rewrite it to be shorter?

1 Sep 2005 1:35 PM

Igor Solodovnikov

Thank you. I have written something like this:

CREATE FUNCTION IsDTNull (@v datetime)
RETURNS int
BEGIN
RETURN CASE WHEN @v IS NULL THEN 1 ELSE 0 END
END
GO

ALTER TABLE [dbo].[DiscTable] WITH NOCHECK ADD
CONSTRAINT [CK_DiscTable_Period] CHECK
(dbo.IsDTNull(PeriodStart)=dbo.IsDTNull(PeriodStop))
GO

On Thu, 01 Sep 2005 14:14:59 +0300, Uri Dimant <u***@iscar.co.il> wrote:

Show quote

> Igor
> You can write an UDF and ADD CONSTRAINT with reference to this UDF but I
> have my doubt about performnce in that case.
>
>
>
>
>
> "Igor Solodovnikov" <IgorSolodovni***@discussions.microsoft.com> wrote in
> message news:op.swe7bjqcn8ihmu@iw2k.helpmicro.local...
>> I have table with following DDL:
>>
>> CREATE TABLE [dbo].[DiscTable] (
>> [id] [int] IDENTITY (1, 1) NOT NULL ,
>> [PeriodStart] [datetime] NULL ,
>> [PeriodStop] [datetime] NULL ,
>> ) ON [PRIMARY]
>>
>> Our business rules require PeriodStart and PeriodStop fields must have
>> equal NULL state. I can ensure that by following CHECK constraint:
>>
>> ALTER TABLE [dbo].[DiscTable] WITH NOCHECK ADD
>> CONSTRAINT [CK_DiscTable_Period] CHECK ((PeriodStart IS NULL AND
>> PeriodStop IS NULL) OR (PeriodStart IS NOT NULL AND PeriodStop IS NOT
>> NULL))
>> GO
>>
>> But from my point of view this constraint look long and ugly. Is there
>> any
>> way to rewrite it to be shorter?
>
>

1 Sep 2005 2:59 PM

AK

have you verified that

(dbo.IsDTNull(PeriodStart)=dbo.IsDTNull(PeriodStop))

performs as fast as

((PeriodStart IS NULL AND
PeriodStop IS NULL) OR (PeriodStart IS NOT NULL AND PeriodStop IS NOT

NULL))

1 Sep 2005 3:22 PM

Igor Solodovnikov

Sorry, but i have no time to verify that. Actually for me and my table
speed is not very important because table will never contain more than 100
records and changes will be very rare.

On Thu, 01 Sep 2005 17:59:22 +0300, AK <AK_TIREDOFSPAM@hotmail.COM> wrote:

Show quote

> have you verified that
>
> (dbo.IsDTNull(PeriodStart)=dbo.IsDTNull(PeriodStop))
>
> performs as fast as
>
> ((PeriodStart IS NULL AND
> PeriodStop IS NULL) OR (PeriodStart IS NOT NULL AND PeriodStop IS NOT
>
> NULL))
>

1 Sep 2005 8:49 PM

--CELKO--

What you have is fine, but you can also write:

CHECK (IFNULL(period_start, 1) + IFNULL(period_stop, 1) IN (0, 2))

Weird and hard to maintain, but short :)

2 Sep 2005 12:10 AM

Steve Kass

--CELKO-- wrote:

>What you have is fine, but you can also write:
>
>CHECK (IFNULL(period_start, 1) + IFNULL(period_stop, 1) IN (0, 2))
>
>Weird and hard to maintain, but short :)
>
>
>

Not to mention non-standard. What's ISNULL?

SK

2 Sep 2005 3:04 PM

Jeremy Williams

IFNULL:

http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-080.html
http://platinum.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_ifnull
http://www.as400pro.com/servlet/sql.tipView?key=166&category=SQL

I could not find the IFNULL function in my SQL 99 Standards document, but it
does appear that several products support some variation of it.

Show quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:OUuitK1rFHA.1204@TK2MSFTNGP15.phx.gbl...
>
> --CELKO-- wrote:
>
>>What you have is fine, but you can also write:
>>
>>CHECK (IFNULL(period_start, 1) + IFNULL(period_stop, 1) IN (0, 2))
>>
>>Weird and hard to maintain, but short :)
>>
>>
> Not to mention non-standard. What's ISNULL?
>
> SK

2 Sep 2005 7:46 AM

Igor Solodovnikov

On Thu, 01 Sep 2005 23:49:19 +0300, --CELKO-- <jcelko***@earthlink.net>
wrote:

> What you have is fine, but you can also write:
>
> CHECK (IFNULL(period_start, 1) + IFNULL(period_stop, 1) IN (0, 2))
>
> Weird and hard to maintain, but short :)
>

I can not find IFNULL function in books online. Are you referring to
ISNULL function?

2 Sep 2005 2:56 PM

Gert-Jan Strik

Igor Solodovnikov wrote:

>
> On Thu, 01 Sep 2005 23:49:19 +0300, --CELKO-- <jcelko***@earthlink.net>
> wrote:
>
> > What you have is fine, but you can also write:
> >
> > CHECK (IFNULL(period_start, 1) + IFNULL(period_stop, 1) IN (0, 2))
> >
> > Weird and hard to maintain, but short :)
> >
>
> I can not find IFNULL function in books online. Are you referring to
> ISNULL function?

Must be, because the other option (NULLIF) doesn't make sense in this
context.

How about this:

CHECK ( COALESCE(PeriodStart, PeriodStop) IS NULL
OR DATEDIFF(year, PeriodStart, PeriodStop) IS NOT NULL )

Gert-Jan

2 Sep 2005 3:45 PM

Igor Solodovnikov

On Fri, 02 Sep 2005 17:56:55 +0300, Gert-Jan Strik
<sorry@toomuchspamalready.nl> wrote:

>> I can not find IFNULL function in books online. Are you referring to
>> ISNULL function?
>
> Must be, because the other option (NULLIF) doesn't make sense in this
> context.
>
> How about this:
>
> CHECK ( COALESCE(PeriodStart, PeriodStop) IS NULL
> OR DATEDIFF(year, PeriodStart, PeriodStop) IS NOT NULL )
>
> Gert-Jan

I think your variation will work.
CELCO's variation will not work on MSSQL2000 even if you replace IFNULL
with ISNULL.