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 so, how would you announce it?

How are you going to assign the 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 reference the dispatch table:

 desp_id, prod_name, shelfLoc, quantity 

I thought that I might need to create a unique constraint that would look like this:

 ALTER TABLE product ADD CONSTRAINT prod_nameID_uc UNIQUE (prod_id,prod_name) 

I am wondering if you can specify a unique key as a foreign key in the send table. I should have prod_name and not prod_id in the send table so that the information is more meaningful to the user when reading, instead of seeing the identifier number. I am using iSQL plus in oracle.

Answer 1

In Oracle FOREIGN KEY, it is possible to refer to a UNIQUE constraint:

 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> 

This, however, is bad practice. The main reason for using a primary key along with a unique key is to provide a synthetic key for use in foreign keys. I was you, I would be worried that your teachers will give you a task riddled with bad practice .

Answer 2

It depends on the DBMS. In the DBMS that I am familiar with, the unique restriction and foreign key restriction are separate considerations, you can have both, and they both work fine when they are combined.