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 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
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/m...S,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" <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
--
Tom Moreau wrote: Change it to:
CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
Thank you, this worked.
Susanne
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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
(...
|
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...
|
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):
...
|
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...
|
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,...
|
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
(...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |