CODEWITHSUNDEEP
c#sqlasp.netdatabaselinq-to-sql
Vittorio Romeo
Vittorio Romeo

Reputation: 93324

Reserving an ID from a table before inserting to maintain foreign keys constraints intact

I have a User table that has an auto-increment ID primary key field and a Name string field.

I also have an Email table that has an auto-increment ID primary key field, a Value string field and a User_ID field that links the email address to a specific user. It is a foreign key to the User table.

The requirements for my project force me to develop an ASP.NET page, using Linq to SQL, where the end-user has to add users and emails to the database at the same time.

The user creation page will contain a textbox for the Name user field, and an AJAX-enabled grid control to insert a number of email addresses for the user that will be created. It will also have a Create button at the end.

The AJAX-enabled grid requires a binding to a Linq to SQL table.

The issue here is that, when creating an email address for the user in the AJAX-enabled grid, I do not yet have a valid user ID, as I still have to create an user record by pressing the Create button.

I see two possible solutions, but they are both unsatifsying and inelegant:

  1. Create a temporary user object with all null-fields upon entering the user creation page. This way, I'll be able to correctly maintain the foreign key constraint when adding email addresses in the AJAX-enabled grid.

  2. Not use a foreign key constraint in the email addresses table, and use null until I create a valid user ID. After creating the user ID, I link the previously created email addresses by substituing null with the newly-created ID.

I believe this is not an uncommon issue... is there a more elegant solution?

Upvotes: 1

Views: 210

Answers (1)

Steve Lillis
Steve Lillis

Reputation: 3256

It seems like wasted effort to store the e-mail addresses server side while the user goes through the creation process. Why not just store them locally and pass them with the rest of the data at time of User Creation? (you can still validate via AJAX).

Nevertheless, if you wish to store them on the server, I recommend a separate table for creation-in-progress e-mail addresses that does not have the foreign constraint. You then grab the e-mail addresses out of this table at time of user creation.

Reasons for this approach:

  1. Doesn't sacrifice database integrity by removing the constraint
  2. Doesn't sacrifice database integrity by allowing 'garbage' in the users table
  3. You can implement a clean up process that runs periodically to clean up all the emails that were added but didn't go on to be used (i.e. the creator didn't click the Create User button)

Upvotes: 1

Related Questions