File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Referential Integrity: How 2 INSERT the professional way ?

 
Anonymous
Ranch Hand
Posts: 18944
  • 0
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18944
  • 0
  • send pies
  • Quote
  • Report post to moderator
thanks for helping !
your answer seems really reasonable, good on ya
dennis
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic