June 11, 2008 at 3:45 am
Hi ,
We have the databases with exactly same data on SQL Server 2000 and SQL server 2005.
I noticed the strange behavior while running following statements on both server.
Competency Table has Competencytype NVARCHAR Primary Key column.
UserCompetency table is child table of Competency referencing Competencytype column.
CompetencyType column contains record for both 1 & 99 competency Table and both primary records and many child records in UserCompetency table.
On SQL 2005
update competency set competencytype='1' where competencytype='99'
update competency set competencytype='99' where competencytype='1'
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__COMPETENCY__1881A0DE'. Cannot insert duplicate key in object 'dbo.competency'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__COMPETENCY__1881A0DE'. Cannot insert duplicate key in object 'dbo.competency'.
The statement has been terminated.
On SQL 2000
update competency set competencytype='1' where competencytype='99'
update competency set competencytype='99' where competencytype='1'
Msg 547, Level 16, State 0, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_COMPETENCYOFUSER'. The conflict occurred in database 'OTRW03_2k', table 'USERCOMPETENCY', column 'COMPETENCYTYPE'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_COMPETENCYOFUSER'. The conflict occurred in database 'OTRW03_2k', table 'USERCOMPETENCY', column 'COMPETENCYTYPE'.
The statement has been terminated.
Why Primary Key not violated on SQL Server 2000 ?
Another strange behavior is by removing quotes I get Primary key violation on SQL server 2000 ??
update competency set competencytype=1 where competencytype=99.
Any ideas ?
Many Thanks,
Doba
June 12, 2008 at 7:22 am
Hi,
I think both are same..no difference..i found...
SQL Server 2005
---------------------------
Violation of PRIMARY KEY constraint 'PK__COMPETENCY__1881A0DE'. Cannot insert duplicate key in object 'dbo.competency'.
SQL Server 2000
---------------------------
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_COMPETENCYOFUSER'. The conflict occurred in database 'OTRW03_2k', table 'USERCOMPETENCY', column 'COMPETENCYTYPE'.
database 'OTRW03_2k'
it may be your database name....not sure..just check out..
only the description of error message is different i guess..
Can you put your table structure here. so it can be more clear...?
Cheers!
Sandy.
--
June 12, 2008 at 7:37 am
Hi Sandy,
Thanks for reply.
There is no problem on database names.
One DB is on 2005 and another is on 2000.
The both DBs are identical in schema and data.
Anyway here is the structure.
Thanks again.
Doba_DBA
CREATE TABLE [dbo].[COMPETENCY] (
[COMPETENCYTYPE] [nvarchar] (30) NOT NULL ,
[COMPETENCYACTIVEFROM] [datetime] NULL ,
[COMPETENCYACTIVETO] [datetime] NULL ,
[FUNDSRELEASEMANDATE] [numeric](10, 0) NULL ,
[LOANAMOUNTMANDATE] [numeric](10, 0) NULL ,
[LTVMANDATE] [float] NULL ,
[COMPETENCYDESCRIPTION] [nvarchar] (30) NULL ,
[RISKASSESSMENTMANDATE] [numeric](5, 0) NULL ,
[USERDECREASELIMITPERCENT] [float] NULL ,
[USERINCREASELIMITPERCENT] [float] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[COMPETENCY] ADD
PRIMARY KEY CLUSTERED
(COMPETENCYTYPE)
GO
CREATE TABLE [dbo].[USERCOMPETENCY] (
[USERID] [nvarchar] (64) COLLATE Latin1_General_CI_AS NOT NULL ,
[COMPETENCYTYPE] [nvarchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[USERCOMPETENCYACTIVEFROM] [datetime] NOT NULL ,
[COMPETENCYTYPETEXT] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[USERCOMPETENCY] ADD
CONSTRAINT [PK_USERCOMPETENCY] PRIMARY KEY CLUSTERED
([USERID],COMPETENCYTYPE],USERCOMPETENCYACTIVEFROM]
)
GO
ALTER TABLE [dbo].[USERCOMPETENCY] ADD
CONSTRAINT [FK_COMPETENCYOFUSER] FOREIGN KEY
(
[COMPETENCYTYPE]
) REFERENCES [dbo].[COMPETENCY] (
[COMPETENCYTYPE]
)
June 12, 2008 at 12:01 pm
I could not run your 'create's in 2000, but had-to make the COLLATE's match. Whether COLLATE'd or not, SQL 2000 throws a PK error for me on COMPETENCY whenever the update would have created a pk violation and an FK error on USERCOMPETENCY when CompetencyType '1' does not already exist.
I noticed that your SQL 2005 error msg complains about lines 1 and line 2 while your SQL 2000 message complains twice about line 1.
Perhaps your databases are not identical (either the data or the constraints or SQL Server defaults).
June 12, 2008 at 3:16 pm
Are the query plans the same in both cases. I suspect they are different, due to the change in behaviour when you remove the single quotes.
When you remove the quotes, you force SQL to do a type conversion from numeric to varchar, and that type conversion probably means that an index is no longer being used.
Perhaps whatever index is being used gives the engine the ability to detect the duplicate primary key earlier in the process ?
June 12, 2008 at 3:56 pm
This looks like a "data" problem to me.
* Noel
June 12, 2008 at 4:13 pm
I'm wondering why user would run those two statements if PK's '1' and '99' already existed.
June 13, 2008 at 2:30 am
Thanks Guys for your reply.
Apologies.I think I have pasted the table scripts with incorrect syntax, however my point is to concentrate more on this error.
Anyway the correct syntax is as below.
Now why we doing this ?
We have taken our SQL 2000 database and restored on SQL Server 2005 and checking what issues we may get with our Application product.
In this instance we expected that it should throw the primary key violation but surprised that SQL 2000 shows FK violation.
It is not just this table but same with other tables as well.You can try on some other sample table on SQL server 2000.
I believe if Primary Key violation should occur first if potential update/insert values already exist in primary key column.
SQL Server 2005 shows correct violation regardless of quotes but SQL Server 2000 doesn't.
I am not sure why ?
Thanks,
Doba
CREATE TABLE dbo.COMPETENCY (
COMPETENCYTYPE nvarchar (30) NOT NULL ,
COMPETENCYACTIVEFROM datetime NULL ,
COMPETENCYACTIVETO datetime NULL ,
FUNDSRELEASEMANDATE numeric(10, 0) NULL ,
LOANAMOUNTMANDATE numeric(10, 0) NULL ,
LTVMANDATE float NULL ,
COMPETENCYDESCRIPTION nvarchar (30) NULL ,
RISKASSESSMENTMANDATE numeric(5, 0) NULL ,
USERDECREASELIMITPERCENT float NULL ,
USERINCREASELIMITPERCENT float NULL
)
GO
ALTER TABLE dbo.COMPETENCY ADD PRIMARY KEY CLUSTERED (COMPETENCYTYPE)
GO
CREATE TABLE dbo.USERCOMPETENCY (
USERID nvarchar (64) NOT NULL ,
COMPETENCYTYPE nvarchar (30) NOT NULL ,
USERCOMPETENCYACTIVEFROM datetime NOT NULL ,
COMPETENCYTYPETEXT nvarchar (50) NULL
)
GO
ALTER TABLE dbo.USERCOMPETENCY ADD CONSTRAINT PK_USERCOMPETENCY PRIMARY KEY CLUSTERED
(USERID,COMPETENCYTYPE,USERCOMPETENCYACTIVEFROM)
GO
ALTER TABLE dbo.USERCOMPETENCY ADD CONSTRAINT FK_COMPETENCYOFUSER FOREIGN KEY
(COMPETENCYTYPE) REFERENCES dbo.COMPETENCY (COMPETENCYTYPE)
GO
June 13, 2008 at 8:08 am
heyy Doba_DBA,
I am bit late due to my current assignment.
today I saw your code and wondering how you executed this code.
I guess you may be post wrong code..
please check this again...
Cheers!
Sandy.
--
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply