Is it possible to set a unique constraint as a foreign key in another table?

constraints, foreign-keys, oracle, unique

Solution

It is perfectly possible to reference a UNIQUE constraint in an Oracle FOREIGN KEY:

SQL> create table products (
  2      prod_id number not null
  3      , prod_name varchar2 (30) not null
  4      , constraint prod_pk primary key ( prod_id )
  5      , constraint prod_uk unique ( prod_name )
  6      )
  7  /

Table created.

SQL> create table despatch (
  2      desp_id number not null
  3      , prod_name
  4      , constraint desp_pk primary key ( desp_id )
  5      , constraint desp_prod_pk foreign key ( prod_name )
  6          references products ( prod_name )
  7      )
  8  /

Table created.

SQL>

It is however bad practice. The main reason for using a primary key alongside a unique key is to provide a synthetic key for use in foreign keys. I were you I would be concerned that your teachers are giving you an assignment riddled with bad practice.

Problem

Is it possible to set a unique constraint as a foreign key in another table? If yes, how would you go about declaring it? How would you go about assigning a candidate key? Is it possible? Example: I have a product table that consists of: ``` prod_id, prod_name, prod_price, QOH ``` Where I want prod_name to link to the despatch table: ``` desp_id, prod_name, shelfLoc, quantity ``` What I was thinking is that I may need to create a unique constraint which will look like this: ``` ALTER TABLE product ADD CONSTRAINT prod_nameID_uc UNIQUE (prod_id,prod_name) ``` 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 `prod_name` rather than `prod_id` 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.

Original source