unique key violation
-
i have a table that gets updated each night.. the update is like this:
insert into tab1
col1, col2, col3, col4
select a,b,c,d from table2 Union select a,b,c,c from table3
The only unique key on the tab1 is the primary key. it should be auto generated. but each time i run this sproc i get a unique key violation . i have checked all other col's on the table but none are unique. anyone come across this before?
-
Can you post the ddl script for tab1?
-
USE
[SRDB]
GO
/****** Object: Table [dbo].[MCSFinFactData] Script Date: 11/06/2006 14:46:47 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[MCSFinFactData](
[MCSFinFactDataID] [int]
IDENTITY(1,1) NOT NULL,
[GBIInvoiceDetailID] [int]
NULL,
[InvoiceDateDimensionID] [int]
NULL,
[DataCutoffDateDimensionID] [int]
NULL,
[InvoiceDimensionID] [int]
NULL,
[SubDimensionID] [int]
NULL,
[CompanyCodeDimensionID] [int]
NULL,
[GBIDetailDimensionID] [int]
NULL,
[AmountLocal] [real]
NULL,
[AmountUSD] [real]
NULL,
[CreatedDate] [datetime]
NULL,
[LastModifieddate] [datetime]
NULL,
CONSTRAINT [PK_MCSFinFactData] PRIMARY KEY CLUSTERED
(
[MCSFinFactDataID]
ASC
)
ON [PRIMARY],
CONSTRAINT [UNQ_MCSFinFactData] UNIQUE NONCLUSTERED
(
[GBIInvoiceDetailID]
ASC
)
ON [PRIMARY]
)
ON [PRIMARY]
-
Are you sure you are not getting the error on that unique constraint (GBIInvoiceDetailID) it would make more sens as you are not checking for uniqueness on that column??
I have never seen sql server fail on a identity column like that
! -
"The only unique key on the tab1 is the primary key... I have checked all other col's on the table but none are unique."
You have two unique constraints on the table: PK_MCSFinFactData and UNQ_MCSFinFactData. I would guess that it is the latter that is being violated by your update statement.
John
-
thanks for the help guys. i opened up "edit table" and on each column i checked for a unique field, but could only find one on the primary key. when you showed me the 2nd unique field, i was able to resolve my issue. carelessness on my part. thanks all
-
I wouldn't call it careless, maybe caffeineless
.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply