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)"
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.