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
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
Joined: Nov 22, 2008
thanks for helping ! your answer seems really reasonable, good on ya dennis
subject: Referential Integrity: How 2 INSERT the professional way ?