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 want a compound primary key.

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

You can create a primary key on two columns: click on both columns in designer view > click on pk

Or, you could add a unique constraint on 2 columns:

ALTER TABLE [dbo].[RepresentativeData] 
add CONSTRAINT [UK_Representative_repRecID_AppID] unique (repRecID,AppId)
go

I prefer the compound primary key, because it enforces that the value does exist in the other tables.

Answer 4

yes it is called a compound primary key

Answer 5

There's nothing wrong with a compound primary key for this but's probably easier in most situations to create a single primary key column anyway. Unless you have particular hardware constraints, the pk col will probably only improve performace and easy of maintainance.

Don't forget to consider that you may have situations which may not neatly fit your model. For example, you may have stock which you know exists but do not currently know which warehouse it is in, or in transit or not yet allocated or whatever. You either need to create business rules to fit this into your compound primary key or use a primary key column instead.

Answer 6

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 7

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 8

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.