Primary Key Violation Error

SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
GO
-----
RECORDS:
11652IORESI73756002065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26 09:53:17.237
12207IORITC
73755872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
13208IORYLM
73751692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
143389IOMINA
73755882065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
15213IOALMA
73750872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
16651IORYAC
73756712065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
174578IOBASH
73755452065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
184573IODARM
73755052065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
194579IOALQA
73750062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
20206IOBURJ
73751102065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
21209IOLEMJ
73751502065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
22647IOBABV
73756382065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
23210IOJUBC
73751432065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
24205IOJUMB
73753362065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
253418IOGHYT
73756062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
263422IOFMDX
73756702065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
274731IOOASB
73753062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
284732IOSJUM
73755692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
294724IODXMB
73751602065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
304730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
314730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
324726IOGHDB
73752632065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
334809IOMADI
73753472065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
344725IOHILJ
73757072065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
354611IOGROS
73750862065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
364727IOHATA
73749942065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
374729IOJEBA
73750122065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
384728IOHYDX
73756812065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.

> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>
|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
Anith