INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to get foreign key for inserting new records.

How to get foreign key for inserting new records.

(OP)
Any suggestions to solve this problem? - Sorry for long question.

Suppose I have two tables,
Orders{OrderID, OrderDate...) and
OrderLines{OrderLineID,OrderID,ProductID....} where OrderID is the primary key in orders and foreign key in orderlines. OrderID as PK defaults to the next integer using a sequence.
I want to add new records for a customer's order, ie add the Order record then all the associated OrderLine records.

In a transaction, how do I get the new OrderID for inserting into the OrderLine records before the transaction is committed?

psuedocode:
Begin Transaction
Insert Into Orders (OrderDate...) Values (...);
//Get the new orderid somehow
For Each Order-Item
   Insert Into OrderLines (OrderID, ...) Values (...);
Next
Commit Transaction

I am doing this using PHP-Pear and also ADO, not using PSM.

FYI- In MS SQL Server i have the same problem, solved by finding the maximum OrderID, which is the new one, inside the transaction. I don't like it though.
Thanks
Sello2

RE: How to get foreign key for inserting new records.

If your sequence is, for example, named SEQ1, you get the default by setting: DEFAULT NEXT_VALUE OF SEQ1. Then to access the last value of the sequence you use: CURRENT_VALUE OF SEQ1.

psuedocode:
Begin Transaction
//
//  This insert uses NEXT_VALUE OF SEQ1
//
Insert Into Orders (OrderDate...) Values (...);
//
//  The following inserts can access the value of SEQ1
//
For Each Order-Item you do
   Insert Into OrderLines (OrderID, ...) Values (CURRENT_VALUE OF SEQ1, ...);
Next
Commit Transaction

After you have used NEXT_VALUE, then CURRENT_VALUE will return the same value until you use NEXT_VALUE on the same sequence again (regardless of other users).

RE: How to get foreign key for inserting new records.

I have tested your SQL Server solution in Mimer but its very slow doing max(id) if the table is big and used a lot (risk of transaction conflict). We changed to using timestamp as primary key, it works well. But sequences is a new thing that is even better.

RE: How to get foreign key for inserting new records.

(OP)
I agree my sql server solution sucks. It was used in a vb program with ADO when adding a new record the database won't supply a value for a 'autonumber' type field until updated.
I think sequences are super! Mimerwizard's solution looks like a winner.

RE: How to get foreign key for inserting new records.

PeterersJazz,

Using a timestamp as a primary key? Guess Murphy is waiting for you? Never get key conflicts?

What are sequences for?


Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com

RE: How to get foreign key for inserting new records.

MartinjinTonies,

have been using timestamp as primary key for a log table in production for some time (before sequences where available). It works because timestamps include milli seconds and if you get an error doing insert you can easily do another try.

But I agree with you if there would be hundreds of inserts per second, but how often is that the case?

In this case we didnt have any better for a primary key. We could have used a random value as primary key or having next value in a separate table but that would give a transcation conflict to often.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close