oracle 两个外键而不是主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/208666/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Two foreign keys instead of primary
提问by Galwegian
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 STOCKtable with item_idand warehouse_idas foreign keys from ITEMSand WAREHOUSEStables. 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!
谢谢！
回答by Galwegian
You want a compound primary key.
你想要一个复合主键。
回答by Tony Andrews
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)
);
回答by DOK
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.
我更喜欢复合主键，因为它强制该值确实存在于其他表中。
回答by Josh Mein
yes it is called a compound primary key
是的，它被称为复合主键
回答by Colin Pickard
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.
不要忘记考虑您可能会遇到不完全适合您的模型的情况。例如，您可能有库存，您知道它存在，但目前不知道它在哪个仓库，或者在运输中，或者尚未分配或其他。您要么需要创建业务规则以将其放入复合主键中，要么改用主键列。
回答by Paul Tomblin
If you aren't doing any sort of query that needs it, you don't needa 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 设置唯一约束。
回答by James Curran
You don't haveto 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”，以使其有限的效用显而易见。
回答by Tundey
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 个外键替换主键。
