Adding Record without Violation of PRIMARY KEY constraint

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.
AirmasterAsked:
Who is Participating?
 
sameer2010Connect With a Mentor Commented:
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
 
Stephan_SchrandtConnect With a Mentor Commented:
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
 
AirmasterAuthor Commented:
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
 
Stephan_SchrandtCommented:
You can see and change the default value in column properties (Design view of table in SQL Server Mangement Studio)
0
 
AirmasterAuthor Commented:
Great response
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.