title: Referencing Surrogate keys
body: <p>I have 2 tables Customers &amp; Accounts in my Oracle Database. I have sequence numbers for generating surrogate key values for both tables.</p>
  
  <pre><code>CREATE SEQUENCE         customers_seq   NOCACHE;
  CREATE SEQUENCE         accounts_seq    NOCACHE;
  
  
  CREATE TABLE            customers
  (
  customer_surrogate_id   NUMBER(10),     
  customer_id             VARCHAR2(8) UNIQUE      NOT NULL,
  customer_password       VARCHAR2(20),
  customer_name           VARCHAR2(20), 
  customer_pan            VARCHAR2(10)    UNIQUE,
  customer_email_id       VARCHAR2(20)    UNIQUE,
  CONSTRAINT              customer_pk 
      PRIMARY KEY (customer_surrogate_id)
  );
  
  CREATE TABLE            accounts
  (
  accounts_surrogate_id   NUMBER(10),
  account_id              VARCHAR2(10)    UNIQUE      NOT NULL,
  customer_surrogate_id   NUMBER(10),
  account_type            VARCHAR2(10),
  account_currency        VARCHAR2(20),
  account_balance         NUMBER(20, 2), 
  CONSTRAINT              accounts_pk 
      PRIMARY KEY (accounts_surrogate_id), 
  CONSTRAINT          accounts_fk
      FOREIGN KEY (customer_surrogate_id) 
          REFERENCES  customers(customer_surrogate_id)
  );
  </code></pre>
  
  <p>I know how to use <code>sequence_name.NEXTVAL</code> &amp; <code>sequence_name.CURRVAL</code> in insert statements to perform the reference</p>
  
  <p>The problem is with using <code>NEXTVAL</code> &amp; <code>CURRVAL</code> is that it assumes that inserts to both tables occur sequentially like</p>
  
  <pre><code>insert into Customers(// use NEXTVAL here)
  insert into Accounts(// use CURRVAL here to reference the above row in Customers)
  </code></pre>
  
  <p>But in my java application, the multiple inserts for the Customers table can occur before even one insert occurs in Accounts table. <code>CURRVAL</code> will return the value of the last inserted row of the Customers table. </p>
  
  <p>When inserting a row into Accounts table, I can get customers_id values in my application. Should the <code>customer_id</code> be used to query the Customers table to get the <code>customer_surrogate_id</code> as shown below?</p>
  
  <pre><code>insert into Customers(// use NEXTVAL here)
  ...
  insert into Accounts(// use the customer_id to query and find customer_surrogate_id)
  </code></pre>
  
  <p>Is there better way to reference the Customers table in this situation? </p>
  
  <p>Edit: I am using JDBC to access the database.</p>