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

Question

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.

Answer 1

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.