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.

Solution 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.

Solution 2

This is necessarily DBMS dependent. In the DBMSes I'm familiar with, the unique constraint and the foreign key constraint are separate considerations, you can have both, and they both act normally when combined.

Comments

[taksIV]

Oh believe me, I totally agree. They haven't even taught us half the stuff that they expect us to execute within the assignment. Slack.