Check constraint does not work (compare with null)

Hi!

I have a table with a check constraint. But unfortunately it does not
work like I wanted.

CREATE TABLE MAP
(
[R_ID] [T_D_ID] NOT NULL,
[R_ID1] [T_D_ID] NULL,
CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),
CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID])
)

R_ID1 should always have the value of R_ID or Null
The following statements should cause errors:

insert into map (R_ID, R_ID1)values(1,2);
update map set R_ID1=3 where R_ID=1;

But there occur no errors. Does anyone have an idea? It is an SQL Server
2000.

TIA
SusanneChange it to:

CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Susanne Klemm" <Susanne.Klemm@.appliedsystems.de> wrote in message
news:441e9f63$0$43596$bfcc4b32@.reader.news.celox.d e...
Hi!

I have a table with a check constraint. But unfortunately it does not
work like I wanted.

CREATE TABLE MAP
(
[R_ID] [T_D_ID] NOT NULL,
[R_ID1] [T_D_ID] NULL,
CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),
CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID])
)

R_ID1 should always have the value of R_ID or Null
The following statements should cause errors:

insert into map (R_ID, R_ID1)values(1,2);
update map set R_ID1=3 where R_ID=1;

But there occur no errors. Does anyone have an idea? It is an SQL Server
2000.

TIA
Susanne|||Your constraint should be

CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),|||Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .

Change it to:

CHECK (R_ID1 = R_ID)

The UNKNOWN case where R_ID1 is null will still be permitted.

Better still, get rid of R_ID1, which is apparently redundant - except
maybe if it is part of a foreign key. In the case of a foreign key I
would still look for a better design without the nullable column.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Doh! Coffee... I need coffee...

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142860722.911871.308570@.v46g2000cwv.googlegr oups.com...
Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .

Change it to:

CHECK (R_ID1 = R_ID)

The UNKNOWN case where R_ID1 is null will still be permitted.

Better still, get rid of R_ID1, which is apparently redundant - except
maybe if it is part of a foreign key. In the case of a foreign key I
would still look for a better design without the nullable column.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

Thank you, this worked.

Susanne|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Change it to:
> CHECK (R_ID1 = R_ID)
> The UNKNOWN case where R_ID1 is null will still be permitted.

Actually, the data-modelling tool that I use, PowerDesiger 9.5, insist on
adding IS NULL conditions to all my column constraints for my nullable
columns. I would guess the reason for this is that there was a bug in SQL
2000 RTM where NULL values actually can give you constraint violations.
(There is a similar bug with rules that has been around since SQL 7 RTM,
and I suspect never will get fixed.)

> Better still, get rid of R_ID1, which is apparently redundant - except
> maybe if it is part of a foreign key. In the case of a foreign key I
> would still look for a better design without the nullable column.

To me it looks like a funny sort of bit column, as there are only two
possible values. But maybe Susanne only gave us a scaled-down example,
and the resl-world table looks a little different.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx