Adding Record without Violation of PRIMARY KEY constraint

Posted on 2009-12-16
Last Modified: 2012-05-08
I recently imported records from Microsoft Access 2007 into SQL Server Express 2008 using the "Microsoft SQL Server Migration Assistant 2008 for Access".  Everything seems to be working well with existing data, but I now need to add a new record, which is where I am having problems.

First, I have a table "tblTechPlantMap" with the fields:

ID PK,int, not null
TechID int, null
PlantID int, null
Savings float, null
Notes nvarchar(max), null

Both TechID and PlantID link two other tables together, "tblTechnology" and "tblPlant" by their "ID" fields.  (there is no other natural primary key)  I would expect that TechID and PlantID would be listed as foreign keys, but in Microsoft SQL Server Management Studio, they are not listed as foreign, only the primary key is listed.

Anyway, when I try an add a row to "tblTechPlantMap" using
"INSERT INTO tblTechPlantMap (PlantID,TechID) VALUES(3,47)"

I get:

Violation of PRIMARY KEY constraint 'tblTechPlantMap$PrimaryKey'.
Cannot  insert duplicate key in object 'dbo.tblTechPlantMap'.

I am wondering if the problem is that "tblTechPlantMap.ID" is not listed as being of the IDENTITY type, and that because I am not specifying a number for ID when adding the record it is causing the problem (and because that key is not being auto-numbered).  If so, does that mean I need to find my own index?

From what I understand, if that is the problem, then the only way I can "fix" the problem is to copy the table "tblTechPlantMap" into a new table where it was created with the IDENTITY property, delete the old table, and then rename the new one.  Is this the case?  If so, what is the best way to do this?

If so, then how do I property specify the Foreign keys, and am I correct that they need to be specified so that the databases are kept clean, with no orphan records when tables are deleted?  I assume that Foreign keys are the same as creating relationships in Access.
Question by:Airmaster
    LVL 13

    Accepted Solution


    This is happening because of not passing on ID to the insert query.
    There are  a few ways to fix this:
    1. Modify your insert process to take MAX ID available and include it in your INSERT.
    INSERT INTO tblTechPlantMap (ID,PlantID,TechID) VALUES((SELECT MAX(ID) FROM tblTechPlantMap),3,47)
    2. Another method would be to add identity column and define primary KEY as (ID, IDENTITY_Column)

    LVL 9

    Assisted Solution

    You can go to SQL Server Management Studio and define the ID column as IDENTITY. You can also Insert Records with:

    INSERT INTO tblTechPlantMap (ID,PlantID,TechID) VALUES(CASE WHEN (SELECT MAX(ID) FROM tblTechPlantMap) IS NULL THEN 1 ELSE (SELECT MAX(ID) + 1 FROM tblTechPlantMap)) END,3,47)

    The primary key set in tblTechPlantMap does not affect your records in other tables, so you don't need to take care what ID you set. You are right, there should be relations between (plantid and plant table) and (techid and tech table).

    Author Comment

    When I try to set the IDENTITY property for ID in SQL Server Mangement Studio, I get the error of "Identity property cannot be set in column 'ID' because it has a default value".  I don't see how a default value was specified, nor do I know how to change it so there isn't, or what the repercussions of this are.

    LVL 9

    Expert Comment

    You can see and change the default value in column properties (Design view of table in SQL Server Mangement Studio)

    Author Closing Comment

    Great response

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now