weird PK violation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

weird PK violation

Author

Topic

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-07 : 12:22:28

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 [Smile]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 12:30:36

you are trying to insert a value into CREDIT_REPORT_ID that already exists.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-07 : 12:34:21

quote:
Originally posted by spirit1

you are trying to insert a value into CREDIT_REPORT_ID that already exists.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

well that would seem the obvious answer, but i'm not. Im not specifying a value, just letting the identity field populate it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 12:39:33

do you have a trigger on this table?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-07 : 14:05:09

If the table is not empty is it possible that the IDENTITY was adjsuted (reseeded) so that it is trying to insert an value that alreasy exists? Try selcting: SELECT IDENT_CURRENT('<table_name>') and see if that ID already exists.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources