Auto increment with a Unit Of Work

Answer 1

Avoid explicitly referencing the CONTACT_ID entirely. Assuming that Contact.NAME has a UNIQUE constraint and that the CONTACT_ID column REFERENCES Contact(ID):

INSERT INTO Contact (NAME) VALUES ('Joe Bloggs'); -- Contact.ID auto-generated
INSERT INTO Address (CONTACT_ID, NAME)
     VALUES ((SELECT ID FROM Contact WHERE NAME = 'Joe Bloggs'),
             '123 Apple Lane');

Now Address.CONTACT_ID is correct without your code knowing the key's value or even its type.