By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Check constraint does not work (compare with null)

P: n/a
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
Mar 20 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
..
"Susanne Klemm" <Su***********@appliedsystems.de> wrote in message
news:44***********************@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
Mar 20 '06 #2

P: n/a

Your constraint should be

CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

Mar 20 '06 #3

P: n/a
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/m...S,SQL.90).aspx
--

Mar 20 '06 #4

P: n/a
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" <RE****************************@acm.org> wrote in message
news:11**********************@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/m...S,SQL.90).aspx
--

Mar 20 '06 #5

P: n/a
Tom Moreau wrote:
Change it to:

CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),


Thank you, this worked.

Susanne
Mar 20 '06 #6

P: n/a
David Portas (RE****************************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 20 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.