Avatar of gvamsimba
gvamsimba
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Violation of PRIMARY KEY constraint

Hi I have a table named test and a column named technologyid. Now this column is the primary key of the table
but at the same time, this column is also the foreign key referencing to another table. I know this is weird but this is how the table has been designed.

Now, I have a requirement to insert some records and these are existing technologyid's and it throws the below error.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_TechnologyAttributes'. Cannot insert duplicate key in object 'dbo.TechnologyAttributes'. The duplicate key value is (666).
The statement has been terminated.

Is there any way to get around this ?

Many Thanks
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
gvamsimba

8/22/2022 - Mon
SOLUTION
Phillip Burton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Horn

>Cannot insert duplicate key in object 'dbo.TechnologyAttributes'.. The duplicate key value is (666).
Please do two things for us:
   Copy-paste the insert T-SQL into this question.
   Click on table test in SSMS, then right-click > Script Table As > Create > New Query editor, and copy-paste the resulting T-SQL into this question.

Also spell out for us why you wish to insert a value into a PK table when it already exists, and based on the above T-SQL we can suggest a workaround.

Another possibility:  Since you described table test, and the error message shows table TechnologyAttributes, maybe there's a trigger on table Test that attempts to insert into table TechnologyAttributes
gvamsimba

ASKER
Hi Phillip,

No, we already have a technology table in which the technologyid is the primary key....

this test table should ideally have its own primary key but for some reason they made the technologyid
as the primary key. We need to have two technlogyid's in this table now for some technologies as we are introducing a new flag column for this test table and the stored proc can work out to make sure only one technology can be returned at any one time.

so what is the best way to achieve this ?
gvamsimba

ASKER
HI Jim,

Yes you are right. the table name actually is TechnologyAttributes. we already have a technology table in which the technologyid is the primary key....

INSERT INTO [TechnologyAttributes]
           ([TechnologyID]
           ,[FuelBurningFlag]
           ,[StartDate]
           ,[CapacityFactor]
           ,[MonthsToPreliminaryExpiry])
     VALUES
           (666
           ,0
           ,'2014-04-01 00:00:00'
           ,0
           ,9)
GO


CREATE TABLE [dbo].[TechnologyAttributes](
      [TechnologyID] [int] NOT NULL,
      [FuelBurningFlag] [bit] NOT NULL,
      [StartDate] [smalldatetime] NOT NULL,
      [EndDate] [smalldatetime] NULL,
      [TechnologyClassID] [int] NULL,
      [CapacityFactor] [tinyint] NOT NULL,
      [MonthsToPreliminaryExpiry] [int] NOT NULL,
 CONSTRAINT [PK_TechnologyAttributes] PRIMARY KEY CLUSTERED
(
      [TechnologyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Maps 1-to-1 with Technology ID in Technology View' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TechnologyAttributes', @level2type=N'COLUMN',@level2name=N'TechnologyID'
GO

ALTER TABLE [dbo].[TechnologyAttributes]  WITH CHECK ADD  CONSTRAINT [FK_TechnologyAttributes_Technology_TechnologyID] FOREIGN KEY([TechnologyID])
REFERENCES [dbo].[Technology] ([TechnologyID])
GO

ALTER TABLE [dbo].[TechnologyAttributes] CHECK CONSTRAINT [FK_TechnologyAttributes_Technology_TechnologyID]
GO

ALTER TABLE [dbo].[TechnologyAttributes] ADD  CONSTRAINT [DF_TechnologyAttributes_CapacityFactor]  DEFAULT ((100)) FOR [CapacityFactor]
GO

ALTER TABLE [dbo].[TechnologyAttributes] ADD  DEFAULT ((-1)) FOR [MonthsToPreliminaryExpiry]
GO
Your help has saved me hundreds of hours of internet surfing.
fblack61
Phillip Burton

>>We need to have two technlogyid's in this table now for some technologies as we are introducing a new flag column for this test table

Then can't you have (as I was suggesting earlier) a composite PK, i.e. technologyid and flag?
gvamsimba

ASKER
Hi Phillip,

How do I do that ?
Phillip Burton

drop your existing PK, and then

alter table test
add constraint PKTest primary key(technlogyid,[Flag])
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gvamsimba

ASKER
oh Got you Phillip, what you are saying is, instead of creating a new Primary key column to this table, we would just drop the existing PK on the technologyid column and then add a new column which is a flag.
and then create a new composite primary key on technlogyid column and flag column which will make sure that
the combination values in both these columns are never the same ?

Is it right ?
Phillip Burton

Well, you are saying that you have or are going to add a new column which is a flag, and then presumably you want the combination values in both columns to be never the same, so you can have:

TechnologyID Flag
665                   True
666                   True
666                   False
667                   True

If that is so, then yes.
gvamsimba

ASKER
Yes that is right , those combination values should never be the same. this is a look up table anyway...

But how many Indexes will this create now ?

clustered or non-clustered ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Phillip Burton

Should only be the same number of indexes - you are dropping one PK and creating another.

And PKs by default should be clustered, but if you have a reason for non-clustered...
gvamsimba

ASKER
But I thought clustered index can only be on one column...is it not right ?
ASKER CERTIFIED SOLUTION
Phillip Burton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gvamsimba

ASKER
Excellent...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.