[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding Record without Violation of PRIMARY KEY constraint

Posted on 2009-12-16
5
Medium Priority
?
389 Views
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.
0
Comment
Question by:Airmaster
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
sameer2010 earned 1000 total points
ID: 26064281
Hi,

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.
E.g.
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)

Thanks,
Sam
0
 
LVL 9

Assisted Solution

by:Stephan_Schrandt
Stephan_Schrandt earned 1000 total points
ID: 26064316
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).
0
 

Author Comment

by:Airmaster
ID: 26064704
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.

0
 
LVL 9

Expert Comment

by:Stephan_Schrandt
ID: 26065084
You can see and change the default value in column properties (Design view of table in SQL Server Mangement Studio)
0
 

Author Closing Comment

by:Airmaster
ID: 31666856
Great response
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

872 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