CODEWITHSUNDEEP
sqlsql-serverunique-constraint
Mithril
Mithril

Reputation: 13718

Violation of PRIMARY KEY constraint on a column I not set it to be

My database is SQL Server. I want to insert a duplicate key in RequestId, then come across this error.

In addition:My database is created by Visual Studio Sql Server 2008 Server Project

I'm sure there is no constraint in the table.

Neither a column is PRIMARY KEY:

CREATE TABLE [dbo].[RequestPrize] (
    [RequestId] INT           NOT NULL,
    [PrizeId]   INT           NULL,
    [Verified]  BIT           NOT NULL,
    [Created]   SMALLDATETIME NOT NULL
);

But when I insert a duplicate key of RequestId:

insert into [RequestPrize] (RequestId, PrizeId) values('138', 9)

error output :

Violation of PRIMARY KEY constraint 'PK_RequestPrize'. Cannot insert duplicate key in object 'dbo.RequestPrize'.

Then I try to drop this constraint,

ALTER TABLE [RequestPrize] DROP CONSTRAINT RequestId

error :

Msg 3728, Level 16, State 1, Line 1
'RequestId' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

update: I really want to know where this CONSTRAINT come from.

Upvotes: 1

Views: 2242

Answers (2)

Devart
Devart

Reputation: 121902

Without PK all works fine -

CREATE TABLE dbo.RequestPrize
(
    [RequestId] INT           NOT NULL,
    [PrizeId]   INT           NULL,
    [Verified]  BIT           NOT NULL DEFAULT 0,
    [Created]   SMALLDATETIME NOT NULL DEFAULT GETDATE()
)
GO

INSERT INTO dbo.RequestPrize (RequestId, PrizeId) 
VALUES (138, 9)
GO

INSERT INTO dbo.RequestPrize (RequestId, PrizeId) 
VALUES (138, 9)
GO

So drop (if exist) your PK -

IF EXISTS(
    SELECT 1 
    FROM sys.objects o
    WHERE o.type = 'PK'
        AND o.parent_object_id = OBJECT_ID('dbo.RequestPrize', 'U')
) ALTER TABLE dbo.RequestPrize DROP CONSTRAINT PK_RequestPrize

Upvotes: 1

harshal
harshal

Reputation: 592

To drop a constraint you have to use the alias name for constraint you specified while creating "PK_RequestPrize".

By Syntax i guess you are using Microsoft SQLserver, to be sure check if query browser is connected to proper DB.

use "[dbo].[RequestPrize]" while inserting to have more confirmation.

enter image description here

Upvotes: 0

Related Questions