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  4414 
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.ce lox.de...
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.goo glegroups.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****@sommarsk og.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 like to send a message letting
the user know that either fields needs data before they can save the
record. If any of the fields have data then, it is OK to save the
record.
Could you please let me know how to accomplish this? An example will
|
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 taskCompleteDate is NULL (like default) then, taskComplete may
not be set to 1 (attempt to update it to 1 would fail);
|
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
( is not null and = 1)));
Q1 with check constraint:
sample dagta
|
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 "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.
In...
|
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):
CREATE TABLE public.tb_contacts
(
contact_id serial NOT NULL,
actor_id varchar(50) NOT NULL,
contacttype_id varchar(6) NOT NULL,
| | |
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 PALP_DATE IS NOT NULL)
Or would it suffice to say:
ALTER TABLE IS3.FLUSHES
|
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, which is called once
the user clicks the OK button on this dialog.
try
{
int rows = cmd.ExecuteNonQuery();
}
catch(SqlException se)
|
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
( IS NOT NULL AND IS NOT NULL AND IS NOT
NULL));
|
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 can I pass null value as parameter to the database stored procedure programattically using C#?
Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
