CODEWITHSUNDEEP
t-sql
Mr. Blonde
Mr. Blonde

Reputation: 721

Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object

I have a table with a primary key constraint created like so:

    CONSTRAINT [APP_NOTIFICATION_LOG_PK] PRIMARY KEY CLUSTERED 
(   
    [ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I had some records in the table that I have now deleted.

I manually find the next ID to insert like so:

SELECT @maxid_log = max(ID) + 1 FROM APP_NOTIFICATION_LOG;

And then I try to insert the record:

INSERT INTO [dbo].[APP_NOTIFICATION_LOG]([ID],[COLOR],[ACTIVE],[FK_SYS_USERS_ID],[FK_APP_NOTIFICATIONS_ID], [MESSAGE],[WIN_USER_CREATOR], [FK_JOBR_RESOURCE_ID])
SELECT -- log notification created
    @maxid_log,
    anc.COLOR,
    1,
    anc.[FK_SYS_USERS_ID],
    an.id,
    'Notification cancelled!',
    @creatorUserId,
    @jobrResourceDbId
FROM [dbo].[APP_NOTIFICATIONS] an
    INNER JOIN  [dbo].[APP_NOTIFICATION_CONFIG] anc on anc.id = @configId   
WHERE an.[FK_JOBR_RESOURCE_ID] = @jobrResourceDbId

At this stage get the error in the title. It also says that the value 5 is a dublicate. But running a select:

SELECT * FROM APP_NOTIFICATION_LOG WHERE ID = 5

...returns zero records.

What could be the problem here?

Upvotes: 0

Views: 630

Answers (2)

Andrei Filimon
Andrei Filimon

Reputation: 1188

Your inner join returns more than just 1 result, so you try to insert several rows with same id.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

The Select is returning more than one record ?

Run it by itself and see how many rows are returned.

Upvotes: 1

Related Questions