Simple Insert Query ins't working

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 2005 Forums

Transact-SQL (2005)

Simple Insert Query ins't working

Author

Topic

David0129
Starting Member

8 Posts

Posted - 2009-01-30 : 16:54:38

I was troubleshooting a .NET procedure that did an insert. It was working there are over 3000 rows in this table. I decided to do the failing update manually (TSQL) and found that it was also failing.

I know it has to be something simple that I am missing....

Here is the DDL:



CREATE TABLE [dbo].[MemberOf](
	[memberOfID] [int] IDENTITY(1,1) NOT NULL,
	[adID] [int] NOT NULL,
	[memberOf] [varchar](200) NULL,
	[active] [bit] NULL,
 CONSTRAINT [PK_MemberOf_1] PRIMARY KEY CLUSTERED 
(
	[memberOfID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MemberOf]  WITH CHECK ADD  CONSTRAINT [FK_MemberOf_ActiveDirectory] FOREIGN KEY([adID])
REFERENCES [dbo].[ActiveDirectory] ([adID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MemberOf] CHECK CONSTRAINT [FK_MemberOf_ActiveDirectory]

Here is the Insert Query:


  
INSERT  INTO [MemberOf]
        (
          [adID],
          [memberOf],
          [active]
        )
VALUES  ( 476, 'DomSQLUsers', 1 )

Here is the error:


Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_MemberOf_1'. Cannot insert duplicate key in object 'dbo.MemberOf'.
The statement has been terminated.

Thanks in advance,

Dave

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-30 : 16:59:05

Looks like you're trying to insert a value that already exists in the primary key column.

Some days you're the dog, and some days you're the fire hydrant.

David0129
Starting Member

8 Posts

Posted - 2009-01-30 : 17:00:22

Yes I know what the error says, but I am not specifying a primary key value, it is an identity column (memberOfID)

Dave

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-30 : 17:06:04

Not sure if this could be the issue, but try this:

SET IDENTITY_INSERT MemberOf ON

Some days you're the dog, and some days you're the fire hydrant.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 17:14:36

Does 476 value exist in dbo.[Activedirectory] table in Primary key [adID].

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-30 : 18:24:41

Check the identity value and make sure it did not get reseeded:

SELECT MAX([memberOfID]) FROM [dbo].[MemberOf]

DBCC CHECKIDENT ('[dbo].[MemberOf]')

The MAX value should be the same as the current identity value from the DBCC command.

David0129
Starting Member

8 Posts

Posted - 2009-01-30 : 22:06:59

Thanks for the responses.

yes,

adID 476 exists in the parent table.

We are switching to a new Active Directory Domain and I have been doing a lot of housekeeping. I have been deleting this table and resetting the the seed back to 0 and re-running my .NET procedures today, maybe that has something to do with it. I will run

"DBCC CHECKIDENT ('[dbo].[MemberOf]')"

on Monday when I get back see if that helps.

Dave

David0129
Starting Member

8 Posts

Posted - 2009-02-02 : 08:44:09

Okay, I am back to work.

I first ran:
DBCC CHECKIDENT ('[dbo].[MemberOf]')

Results:
Checking identity information: current identity value '6', current column value '2320'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I then ran my original insert query that was failing on Friday:



INSERT  INTO [MemberOf]
        (
          [adID],
          [memberOf],
          [active]
        )
VALUES  ( 476, 'DomSQLUsers', 1 )

Results:
(1 row(s) affected) -->query worked no errors.

I then reran:
DBCC CHECKIDENT ('[dbo].[MemberOf]')

Results:
Checking identity information: current identity value '2321', current column value '2321'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I guess the problem solved itself. I do have nightly backups that updates the statistics on every database, could this be what fixed it? I wear a lot of hats here and my dba hat doesn't fit very well ;-). Thanks for your replies.

Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 08:47:47

why are you each time reseting id value? this might cause the table to generate the duplicate id values during next insertion so that it might violate pk constraint. ideally id is reseted only when you truncate the table and want to repopulate it once again starting from initial value.

David0129
Starting Member

8 Posts

Posted - 2009-02-02 : 09:14:56

I was rewriting all the procedures that populate these tables. During troubleshooting of new code I would delete all the data in the tables, reset the the identity columns, repopulate the tables, and verify the data. Somewhere I must have done something that start causing the pk violation errors. Its fixed now and I know how to fix it if it happens again. Thanks.

Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 09:17:18

ok..thats fine. cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources