Syncing primary & foreign keys

Results 1 to 2 of 2

Thread: Syncing primary & foreign keys

  1. #1
    Join Date
    Dec 1969

    Default Syncing primary & foreign keys

    Using ASP and MS SQL Server 2000 (AND occasionally MSAccess), can the following situation occur?<BR><BR>1. Two tables exist. An Orders table and an Items table. Orders contain primary key, customer, date, etc. Items contain their own primary key, an Orders table foreign key, product description, price etc.<BR><BR>2. When saving this info to the database, what is the method used to ensure that the primary and foreign keys are syncronized...<BR><BR>3. For example. If I:<BR><BR>a. Insert an Orders record into the table, its id/PK will be generated automatically (via SQL Server identity).<BR><BR>b. I then query the Orders table for the largest id and use that value in ASP as the FK value to write all my Items records.<BR><BR>But what is to prevent another user from writing another Orders record BEFORE I get the results back from my query of that table? ie. I write an Orders record, it gets an id of 15. Another user writes in an Orders record, it gets an id of 16. I then query the database and get back 16 as the value to use as my foreign key, which would be incorrect.<BR><BR>I am missing a very basic concept here it seems. Is it possible to begin/commit a transaction or lock write access to a table in ASP, and have other users transactions queued? (That is not just have the page return an error that the table is locked, etc.)<BR><BR>Any help would be appreciated,<BR>Thank-you,<BR>Mike

  2. #2
    Join Date
    Dec 1969

    Default Getting the ID wrong.

    See - in the Databases, General category, I believe.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts