troubleshooting Question

Adding Record without Violation of PRIMARY KEY constraint

Avatar of Airmaster
Airmaster asked on
Microsoft AccessMicrosoft SQL Server 2008SQL
5 Comments2 Solutions448 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros