I've got a table with an identity column, P, as a primary key.  This table has another column, C, with a uniqueness constraint.  I want to insert a row into the table if no row has a value for C=c, and if a row does exist whose column C=c I want to get the value of the identity column for storage in another table as a foreign key.  Is there any way to do this with Derby that will be atomic?  i.e. if the row exists, then the value of the identify column should be retrieved without the possibility of that row being deleted in the interim, if the row doesn't exist then the insert should succeed and not be in a race with other threads on other connections attempting the same operation.

A couple of obvious approached come to mind:

1)  - select * from t1 where C=c for update
     - if row returned, then get value of identity column, id
     - if row not returned, insert into t1 values(c), get identity column from last insert, id
     - insert row with column value of id into second table

this would seem to have an insert race condition.

2) - insert into t1 where C=c
    - if insert fails, select id from t1 where C=c
    - if insert succeeds, get identity column from last insert, id
    - insert row with column value of id into second table

this would seem to be subject to the row being deleted between the insert attempt and the select.

There must be a fool-proof way to do this, but I'm not quite sure what it is.  Any help would be appreciated.

Ace.