weird PK violation
-
wondering if someone has come across the issue below, or can point out the error in my ways
I have a table defined as....
CREATE TABLE [dbo].[CREDIT_REPORTS] (
[CREDIT_REPORT_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[CREATED_DTM] [datetime] NOT NULL ,
[REPORT_TYPE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[LOCATION] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[SUBJECT_TYPE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[REPORT_STATUS] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[ARCHIVE_STATUS] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[COMPANY_ID] [int] NULL ,
[INDIVIDUAL_ID] [int] NULL ,
[AGENCY] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[AGENCY_REFERENCE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AGENCY_REPORT_TYPE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AGENCY_REPORT_SUBTYPE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AGENCY_REPORT_DTM] [datetime] NULL ,
[CURRENCY] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[NAME] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[REFERENCE] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[USER_ID] [int] NOT NULL ,
[COMMENTS] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL ,
[OPTIONS] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[CONTENT_TYPE] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[VEHICLE_ID] [int] NULL ,
[USER_REFERENCE] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[XML_REPORT_ID] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CREDIT_REPORTS] WITH NOCHECK ADD
CONSTRAINT [DF__CREDIT_RE__rowgu__43D61337] DEFAULT (newid()) FOR [rowguid],
CONSTRAINT [CREDIT_REPORTS_PK] PRIMARY KEY CLUSTERED
(
[CREDIT_REPORT_ID]
) ON [PRIMARY]
GO
and the following insert statement
begin tran
insert into credit_reports (created_dtm,report_type,location,subject_type,report_status,
archive_status,agency,[user_id])
values (getdate(),'','','','','','','')
rollback tran
I am putting values in for all columns defined as not null yet i get the following error message
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'CREDIT_REPORTS_PK'. Cannot insert duplicate key in object 'CREDIT_REPORTS'.
The statement has been terminated.
Can anyone shed any light? having a bad friday
-
Is the above DDL copied from Enterprise Manager?
I run the whole thing without any issues.
A thought thou, is there data already in the table? If so what is the highest value for the field in your PK? Use DBCC CHECKIDENT to make sure the number isn't lower than that value.
-
I see you have a "NOT FOR REPLICATION" parameter setup on the identity column. So I am guessing this came from on a "subscriber" database. Anyway You can run
DBCC CHECKIDENT ( 'table_name' , NORESEED )
and verify that your tables have values bigger than that
to correct the problem you should update the seed to a value equal to the last inserted identity with
DBCC CHECKIDENT ( 'table_name' , RESEED, XXXXX ) -- Where XXXX is the last pk value
Hope this helps,
* Noel -
Cheers guys, a reseed was the answer! thanks
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply