Two foreign keys instead of primary

Question

I was wondering, is there any possibility to create a table without a primary key, but with two foreign keys, where the foreign keys pairs are always different? For example, a STOCK table with item_id and warehouse_id as foreign keys from ITEMS and WAREHOUSES tables. So same item can be in different warehouses. The view of the table:

item_id   warehouse_id   quantity
10        200            1000
10        201            3000
10        202            10000
11        200            7000
11        202            2000
12        203            5000

Or do i have to create unused primary key field with auto increment or something? The database is oracle.

Thanks!

Answer 1

You don't have to create a "unused" primary key field, but it often makes life simpler. (As Paul T points out, you'd have to specified both field to delete a row).

I often name such columns "PK", to make their limited utility obvious.

Answer 2

Like this:

create table stock
( item_id      references items(item_id)
, warehouse_id references warehouses(warehouse_id)
, quantity     number(12,2) not null
, constraint stock_pk primary key (item_id, warehouse_id)
);

Answer 3

If you aren't doing any sort of query that needs it, you don't need a primary key. It makes it a tiny bit harder to delete a record unambiguously, though. You might want to put a unique constraint on item_id,warehouse_id if Oracle allows that.

Answer 4

yes it is called a compound primary key

Answer 5

You want a compound primary key.

Answer 6

Like everyone has said, you can create a primary from 2 columns. You don't have to create an artificial auto increment column.

Also, bear in mind that foreign keys serve a different purpose than primary keys. So you can't replace a primary key with 2 foreign keys.