473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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
Susanne
Mar 20 '06 #1
6 4399
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

Your constraint should be

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

Mar 20 '06 #3
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Martin | last post by:
Hello Everybody! I have a POLINE table on a SQL Server 2000 DB. Before I update the record I need to check that either field, STORELOC or WONUM has data on it. If both fields are NULL I would...
5
by: Doug Baroter | last post by:
Hi, DDL: -- create table #task (taskID int identity(1,1) primary key, taskName varchar(25) unique, taskCompleteDate dateTime, taskComplete bit default(0)); /* Business Rules: a) if...
2
by: Doug Baroter | last post by:
Hi, DDLs and DMLs: create table #job (jobID int identity(1,1) primary key, jobName varchar(25) unique not null, jobEndDate dateTime, jobComplete bit default(0), check (( is null and = 0) OR (...
3
by: RAD | last post by:
I am working with an evaluation copy of SQL Server 2000 for the first time; my DB experience lies with MS Access. I have a simple table in SQL Server (tblCompany) that has a field called...
0
by: Fabre Lambeau | last post by:
I've got a problem when adding a CONSTRAINT CHECK on a table by calling a function. It just seems not to work... Here is the table (simplified to only the relevant fields for this case): ...
1
by: Bob Stearns | last post by:
When the "new" data IS NOT NULL or whenever the row is stored? In particular is the first clause needed in: ALTER TABLE IS3.FLUSHES ADD CONSTRAINT PALP_LEFT_OV CHECK (PALP_LEFT_OV IS NULL OR...
3
by: ferg | last post by:
I have a Customer table. The table has two different CHECK constraints. Then there is the Customer details dialog, which provides the user with an UI for changing users. I have some UPDATE sql,...
3
by: Helen Wheels | last post by:
Can we use parentheses in a check constraint in MS-SQL-server DDL? e.g. I'm having a problem with the following statement: ALTER TABLE . ADD CONSTRAINT CHECK (( IS NULL AND IS NULL) OR (...
2
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.