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