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

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 2

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.