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

charindex faster than like operator?

Hi ppl,
While I was working on the post "compare 2 strings" in my test db,
I came across this.
I ran these queries, the ones using the charindex and patindex were faster
than the like operator. I ran it in different tables with different inputs.
But same result.
I know server configs can affect the processing. But can some one run it in
their table and let me know. Plz. I am wordering whats the difference. Any
light on this?
Eager to know. Thanks a lot.
select top 100 * from tbl1 where namecol like '%food%'
select top 100 * from tbl1 where charindex('food',namecol) > 0
select top 100 * from tbl1 where patindex('%food%',namecol) > 0
select top 100 * from tbl1 where 'ab,cd' like '%' + namecol + '%'
select top 100 * from tbl1 where charindex(namecol,'ab,cd') > 0
select top 100 * from tbl1 where patindex('%' + namecol + '%','ab,cd') > 0use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')

Char compare to String

hi
I using C# to write a ASP.NET page
and I need to compare a string variable to a Char field in the SQL table in
SQL Server 2000
how can I write a SQL statement to do that?\
I try to use "SELECT Name from Employee Where pass="+password
it shows me an "Invalid column name error"
How can I fix that ?
ThanksYou have to put the value between apostrophes.
"SELECT Name from Employee Where pass = '" + password + "'"
in sql is should look like:
select [name] from employee where pass = '@.#WE$%'
AMB
"Lam" wrote:

> hi
> I using C# to write a ASP.NET page
> and I need to compare a string variable to a Char field in the SQL table i
n
> SQL Server 2000
> how can I write a SQL statement to do that?\
> I try to use "SELECT Name from Employee Where pass="+password
> it shows me an "Invalid column name error"
> How can I fix that ?
> Thanks
>
>|||Hi
name is a reserved SQL keyword, so you have put put it in brackets like
Alejandro indicated.
Regards
Mike
"Alejandro Mesa" wrote:
> You have to put the value between apostrophes.
> "SELECT Name from Employee Where pass = '" + password + "'"
> in sql is should look like:
> select [name] from employee where pass = '@.#WE$%'
>
> AMB
>
> "Lam" wrote:
>|||Thanks Mike. I thought the error was related to the comparison, but also was
trying to indicate what you said (my fault, I did it in t-sql, not in
english).
AMB
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> name is a reserved SQL keyword, so you have put put it in brackets like
> Alejandro indicated.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>