integrity constraint in Oracle

Question

Suppose that a table named SuplProd has the columns supplier and product and two entries: (Sony Ericcson, Xperia) and (Apple, iPhone).

I would like to create a table named Orders with columns supplier, product and quantity.

However, I'd like the combination (supplier, product) of table Orders to be limited to contain only entries from SuplProd.

For example, the entry (Sony Ericcson, Xperia, 1) would be valid for the table Orders whereas (Apple, Xperia, 1) would not.

How is this possible in Oracle?

Answer 1

You should create a foreign key in orders table:

create table SuplProd (
   supplier ...,
   product ...,
   constraint SuplProd_pk 
     primary key( supplier, product)
)

create table Orders
   ...
   supplier ...,
   product ...,
   qty,
   constraint SuplProd_pk 
     primary key( ... ),
   constraint orders_to_suplprod_fk
     foreign key ( supplier, product)
     references SuplPRod (supplier, product)
)