• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Referential Integrity: How 2 INSERT the professional way ?

 
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks for helping !
your answer seems really reasonable, good on ya
dennis
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic