This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Referential Integrity: How 2 INSERT the professional way ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Referential Integrity: How 2 INSERT the professional way ?" Watch "Referential Integrity: How 2 INSERT the professional way ?" New topic
Author

Referential Integrity: How 2 INSERT the professional way ?

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
hi everyone !
i have two database tables A and B, and B references A. B links A to another table (m:n relationship of A to this other table).
now if i insert a row into A and one in B referencing this newly inserted row, i want to keep the referential integrity between A and B.
a problem occurs, though, if the database itself assigns the primary key value for all new rows.
to do this, i would do one of the following:
1)
- start a transaction
- insert into A
- select MAX(id) from A
- insert into B, with the max. id as the foreign key
- commit
2)
i don't use auto-increments(/sequences/identity columns) and solve the problem by using my own ids
in the sense of platform-independence, and also in trying to do the best thing, which is the way to go ? or is there a third, better way i just didn't think of ? can we always assume that selecting the id with the biggest value belongs to the row we just inserted when using database functions like "identity" (sybase, mssql) or "sequence" (postgres) ?
i hope i don't confuse u
dennis
Joerg Sailer
Greenhorn

Joined: Dec 06, 2002
Posts: 3
Hi,
in the sense of independence you should use an external UUID-generator, because of the platformspecific implemententions of identity, sequence or similar functions. For example DB2 uses a byte-array for identity-columns. Nevertheless the MAX-function will not help, because of characters in the IDs.
Just crawl with google or have a look at the URL
UUID-Package-API
Joerg
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
thanks for helping !
your answer seems really reasonable, good on ya
dennis
 
Don't get me started about those stupid light bulbs.
 
subject: Referential Integrity: How 2 INSERT the professional way ?
 
Similar Threads
retrieve autonumber value of last insert
Problem with identity column
how to get the inserted record by using resultset in java?
Auto-generated ID number
Referential integrity error in Child table even when Parent table has values