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]
G
-----
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
----> 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