Solved

Violation of PRIMARY KEY constraint

Posted on 2014-12-02
14
207 Views
Last Modified: 2014-12-02
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
0
Comment
Question by:gvamsimba
  • 7
  • 6
14 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
ID: 40476051
That's not weird. Why would you want to have duplicate values in technologyid? Wouldn't that mess your existing queries up?

If you absolutely need to do this, then you need a different (or a composite) Primary Key.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40476066
>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
0
 

Author Comment

by:gvamsimba
ID: 40476070
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 ?
0
 

Author Comment

by:gvamsimba
ID: 40476084
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476092
>>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?
0
 

Author Comment

by:gvamsimba
ID: 40476105
Hi Phillip,

How do I do that ?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476116
drop your existing PK, and then

alter table test
add constraint PKTest primary key(technlogyid,[Flag])
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:gvamsimba
ID: 40476143
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 ?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476150
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.
0
 

Author Comment

by:gvamsimba
ID: 40476162
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 ?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40476188
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...
0
 

Author Comment

by:gvamsimba
ID: 40476225
But I thought clustered index can only be on one column...is it not right ?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40476232
No - you can't have more than one clustered index per table, but a clustered index can be over more than one column.

For example, this code, which creates a table then adds a composite clustered index, works:

CREATE TABLE [dbo].[Salads](
	[ID] [int] NOT NULL,
	[name] [nvarchar](10) NULL,
	[F1] [nvarchar](10) NULL,
	[F2] [nvarchar](10) NULL,
	[F3] [nvarchar](10) NULL)
GO

CREATE CLUSTERED INDEX IX_Salads 
    ON dbo.Salads (ID, [name]); 

Open in new window

0
 

Author Closing Comment

by:gvamsimba
ID: 40476245
Excellent...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now