两个外键而不是主键

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:

我想知道，有没有可能创建一个没有主键的表，但有两个外键，外键对总是不同？例如，一个STOCK表，其中item_id和warehouse_id作为来自ITEMS和WAREHOUSES表的外键。所以同样的物品可以在不同的仓库中。表格视图：

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.

或者我是否必须使用自动增量创建未使用的主键字段？数据库是oracle。

Thanks!

谢谢！

8 个解决方案

#1

You want a compound primary key.

你想要一个复合主键。

#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)
);

#3

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

您可以在两列上创建主键：在设计器视图中单击两列>单击pk

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

或者，您可以在2列上添加唯一约束：

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.

我更喜欢复合主键，因为它强制该值确实存在于其他表中。

#4

yes it is called a compound primary key

是的，它被称为复合主键

#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.

复合主键没有任何问题，但在大多数情况下，创建单个主键列可能更容易。除非您有特定的硬件限制，否则pk col可能只会改善性能并且易于维护。

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.

不要忘记您可能会遇到可能不适合您模型的情况。例如，您可能拥有您知道存在的库存但目前不知道它在哪个仓库，或者在运输途中或尚未分配的库存或其他什么。您需要创建业务规则以将其纳入复合主键或使用主键列。

#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.

如果您没有进行任何需要它的查询，则不需要主键。不过，这使得删除记录变得有点困难。如果Oracle允许，您可能希望在item_id，warehouse_id上添加唯一约束。

#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).

您不必创建“未使用的”主键字段，但它通常会使生活更简单。（正如Paul T指出的那样，你必须指定两个字段才能删除一行）。

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

我经常将这些专栏命名为“PK”，以使其有限的实用性显而易见。

#8

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

就像每个人都说的那样，你可以从2列创建一个主要的。您不必创建人工自动增量列。

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.

另外，请记住，外键与主键的用途不同。所以你不能用2个外键替换主键。

#1