title: Is it possible to set a unique constraint as a foreign key in another table?
body: <p>Is it possible to set a unique constraint as a foreign key in another table?
  If yes, how would you go about declaring it?</p>
  
  <p>How would you go about assigning a candidate key?
  Is it possible?</p>
  
  <p>Example:
  I have a product table that consists of:</p>
  
  <pre><code>prod_id, prod_name, prod_price, QOH
  </code></pre>
  
  <p>Where I want prod_name to link to the despatch table:</p>
  
  <pre><code>desp_id, prod_name, shelfLoc, quantity
  </code></pre>
  
  <p>What I was thinking is that I may need to create a unique constraint which will look like this:</p>
  
  <pre><code>ALTER TABLE product
  ADD CONSTRAINT prod_nameID_uc 
  UNIQUE (prod_id,prod_name)
  </code></pre>
  
  <p>What I'm wondering is, if it is possible to refer to a unique key as a foreign key in the despatch table. I have to have <code>prod_name</code> rather than <code>prod_id</code> in the despatch table so that the information is more meaningful to the user when reading it, rather than seeing an id number.
  I am using iSQL plus on oracle.</p>