January 22, 2008 at 11:40 am
I have a database with collation Latin1_General_CI_AS.
I execute the next code:
--
create table dbo.t1 (c1 nvarchar(5) not null primary key) on [PRIMARY]
create table dbo.t2 (c1 nvarchar(5) not null primary key) on [PRIMARY]
alter table dbo.t2 add constraint IxF_t2_t1 foreign key (c1) references dbo.t1 (c1)
insert into dbo.t1 values (N'a')
insert into dbo.t2 values (N'a')
--
After, I execute:
--
declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=@cod where c1=@cod
--
No problem in this execution.
Next execution:
--
declare @cod as nvarchar(5)
set @cod='a'
update dbo.t1 set c1=@cod where c1=@cod
--
No problem in this execution.
But in this execution:
--
declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=isnull(@cod,c1) where c1=isnull(@cod,c1)
--
Error message:
"The UPDATE statement conflicted with the REFERENCE constraint "IxF_t2_t1". The conflict occurred in database "DMS00000", table "dbo.t2", column 'c1'."
Why?
The only difference is the function isnull in the "set" and in the "where".
I don´t understand, because the collate is "CI", case-insensitive.
Please, help me. Thank you very much.
January 22, 2008 at 3:21 pm
Hi
This is cool!
It is perfectly reproducible. You even don't need Where in the last statement since you have only one row in the table.
This works:
set @cod='A'
update dbo.t1 set c1=@cod
This works if the value in the table is 'A'
declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=isnull(@cod,c1) where c1=isnull(@cod,c1)
If the value in the table is 'a'
This does not work:
declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=isnull(@cod,c1) where c1=isnull(@cod,c1)
And this does not work too:
declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=isnull(@cod,c1)
so it is isnull function that does it. My server's collation is SQL_Latin1_General_CP1_CI_AS which is Case-Insensitive
Additionally:
if you replace c1 with its value 'a' and run this query, it will work fine. declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=isnull(@cod,'a')
So it is not isnull and collation probably. It does not resolve c1 to its value before it does its check for the referential integrity.
Regards,Yelena Varsha
January 23, 2008 at 1:12 am
Hi Yelena, thanks for you answer.
In my example the table have only one row. But it is an example.
In a table with rows, i need to use the clause where.
The function isnull is necessary because my business logical needs it.
Ever, i need to set the variable with null value. Then, I need to use the function isnull.
If I make the foreign key with the clause "on update cascade", it works fine.
The problem is that my logical business can't use this clause.
I would like to know if exists a way for my problem.
Thank you very much.
January 23, 2008 at 1:21 am
Hi
Whats the collation of the column that u r updating.
Also does the following work. Does the value get changed from 'a' to 'A'.
declare @cod as nvarchar(5)
set @cod='A'
update dbo.t1 set c1=@cod where c1=@cod
I think problem lies with the collation. Probably the column collation is different from you database collation.
"Keep Trying"
January 23, 2008 at 10:05 am
This is not the collation, this is the value resolution.
This will work on one value if the previous value was 'a'. What we do is we pre-assign the filed value to the second variable in advance.
declare @cod as nvarchar(5)
declare @cod2 as nvarchar(5)
set @cod='A'
select @cod2 = c1 from dbo.t1
update dbo.t1 set c1=isnull(@cod,@cod2)
I will try to re-write so it would work on the set of values. I need the verbal description of what the update should do:
update dbo.t1 set c1=isnull(@cod,c1) where c1=isnull(@cod,c1)
Regards,Yelena Varsha
January 24, 2008 at 10:08 am
The following works
set @cod='a'
update dbo.t1 set c1=isnull(@cod,c1) where c1=isnull(@cod,c1)
which is also interesting....
It might have to do with it being an nvarchar vs a varchar. I'll dig further.
January 24, 2008 at 10:34 am
Interesting...
I tried a couple variants to see if I could see a pattern.
-- This works
SELECT @cod='A'
update dbo.t1 set c1=coalesce(@cod ,c1)
FROM dbo.t1
where @Cod IS NOT NULL AND c1=@cod
-- This works
SELECT @cod='a'
update dbo.t1 set c1=coalesce(@cod ,c1 )
FROM dbo.t1
where @Cod IS NOT NULL AND c1=@cod
-- This fails.
SELECT @cod='A'
update dbo.t1 set c1=isnull(@cod,c1)
FROM dbo.t1
where c1 = isnull(@cod,c1)
Apparently something in the where clause where isnull is used is changing the collation of the table temporarily..
This is a bug.. Has anyone put this on MS Connect?
January 24, 2008 at 10:39 am
Also, if all the tables are varchar and the @Cod variable is still nvarchar(5) even the @cod is not null .. version doesn't work.
January 24, 2008 at 10:54 am
I went ahead and submitted the bug. (didn't see one). I'll try and remember to update this post when I get some feedback.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=323974">
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=323974
January 24, 2008 at 12:18 pm
there are two solutions:
1. On the foreign key, enforce Cascade on Delete and Update.
2. PK columns can never be null.
So PK columns do not need to be checked using isnull.
for the new value only do the update if it is not null.
January 24, 2008 at 1:04 pm
First of all, you missed the point. The ISNULL is not for the PK field, but the variable. You are just checking against the PK Field.
As for update cascade. Why? You don't need them to match case. That is the point. The case should be ignored. And is unless you happen to add the ISNULL.
Now if @cod is NULL there is a bigger issue because you then go ahead and update every row to the same value, but I suspect that isn't the point of this SQL. This is an example SQL that shows that collation is not working properly.
January 24, 2008 at 1:40 pm
You missed somthing in my post.
First, there is no reason why a PK should be changed without changing the FK in another table. FK means dependant.
In case , it really have to be that way. That FK has no reason to exist in the first place.
In the second solution. I said to check the new value against null, Right?
January 24, 2008 at 2:15 pm
I tried it, and the on update cascade will fix the issue, but it will cause a ripple effect on the system that is really not necessary.
The OP already stated with the collation that that 'Bob' = 'bob'
however there may be a reason such that you want to change the case of an existing row.
As for changing a row to be the same value that it already is, as I thought I was clear about, is as I expect a mistake in trying to construct an example case and would not be necessary. I haven't tried to construct a more complex case that makes more sense but still fails.
Can you state that there is a reason that
this fails
set c1 = isnull(@cod,c1)
but
set c1 = isnull(@cod,'a')
or
set c1 = isnull(@cod,'A')
works in all cases?
You are trying to apply logic to the reasons for not doing the SQL that way. I honestly can't think of a good case to do this at the moment, but it really isn't out of the realm of possibilities that a situation does exist. However, I can say with certainty that this is something that SHOULD work, but doesn't.
It's a bug, and could be easily coded around
--Msg 547, Level 16, State 0, Line 53
--The UPDATE statement conflicted with the REFERENCE constraint "IxF_t2_t1". The conflict occurred in database "master", table "dbo.t2", column 'c1'.
SELECT @cod='A'
update dbo.t1
set c1=ISNULL(@cod,c1)
FROM dbo.t1
where c1 =ISNULL(@cod,c1)
-- Works fine is it is null, it won't match assuming ANSI_NULLS or c1 != ''
update dbo.t1
set c1=ISNULL(@cod,c1)
FROM dbo.t1
where c1=@cod
-- also works
IF @cod IS NOT NULL
update dbo.t1
set c1=@cod
FROM dbo.t1
where c1 = @cod
April 15, 2008 at 2:59 pm
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=323974&wa=wsignin1.0
Hello Bob,
Thanks very much for sending this our way. Looks like this was caused by a regression from a Yukon bug fix. We have fixed this in SQL Server 2008 RTM, so you should be seeing it your way very soon. Thanks again for your help!
Goldie
Posted by Microsoft on 4/15/2008 at 11:38 AM
April 16, 2008 at 1:37 am
I will wait for SQLserver 2008.
Thanks for all.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply