0

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?

dani herrera
  • 48,760
  • 8
  • 117
  • 177
niels
  • 760
  • 8
  • 25

1 Answers1

3

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)
)
dani herrera
  • 48,760
  • 8
  • 117
  • 177
  • If SuplProd already has a primary key, then a UNIQUE CONSTRAINT would work as well. –  Jan 02 '12 at 12:22
  • @a_horse_with_no_name, right! thanks about appointment. Also if SuplProd has a primary key you can replace supplier, product in Orders table by a copy of SuplProd primary key field(s). – dani herrera Jan 02 '12 at 12:33