insert data from a asp.net form to a sql database with foreign key constraints

Question

i have two tables asset employee

assetid-pk empid-pk empid-fk now, i have a form to populate the asset table but it cant because of the foreign key constraint..

what to do?

thx Tk

Answer 1

Foreign keys are created for a good reason - to prevent orphan rows at a minimum. Create the corresponding parent and then use the appropriate value as the foreign key value on the child table.

Answer 2

You should think about this update as a series of SQL statements, not just one statement. You'll process the statements in order of dependency, see example.

Asset
  PK AssetID
     AssetName
  FK EmployeeID
  etc...

Employee
  PK EmployeeID
     EmployeeName
  etc...

If you want to "add" a new asset, you'll first need to know which employee it will be assigned to. If it will be assigned to a new employee, you'll need to add them first.

Here is an example of adding a asset named 'BOOK' for a new employee named 'Zach'.

DECLARE @EmployeeFK AS INT;

INSERT (EmployeeName) VALUES ('Zach') INTO EMPLOYEE;
SELECT @EmployeeFK = @@IDENTITY;
INSERT (AssetName, EmployeeID) VALUES ('BOOK',@EmployeeFK) INTO ASSET;

The important thing to notice above, is that we grab the new identity (aka: EmployeeID) assigned to 'Zach', so we can use it when we add the new asset.

Answer 3

If I understand you correctly, are you trying to build the data graph locally before persisting to the data? That is, create the parent and child records within the application and persist it all at once?

There are a couple approaches to this. One approach people take is to use GUIDs as the unique identifiers for the data. That way you don't need to get the next ID from the database, you can just create the graph locally and persist the whole thing. There's been a debate on this approach between software and database for a long time, because while it makes a lot of sense in many ways (hit the database less often, maintain relationships before persisting, uniquely identify data across systems) it turns out to be a significant resource hit on the database.

Another approach is to use an ORM that will handle the persistence mapping for you. Something like NHibernate, for example. You would create your parent object and the child objects would just be properties on that. They wouldn't have any concept of foreign keys and IDs and such, they'd just be objects in code related by being set as properties on each other (such as a "blog post" object with a generic collection of "comment" objects, etc.). This graph would be handed off to the ORM which would use its knowledge of the mapping between the objects and the persistence to send it off to the database in the correct order, perhaps giving back the same object but with ID numbers populated.

Or is this not what you're asking? It's a little unclear, to be honest.