oracle 是否可以将唯一约束设置为另一个表中的外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1320093/
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
Is it possible to set a unique constraint as a foreign key in another table?
提问by taksIV
Is it possible to set a unique constraint as a foreign key in another table? If yes, how would you go about declaring it?
是否可以将唯一约束设置为另一个表中的外键？如果是，您将如何声明？
How would you go about assigning a candidate key? Is it possible?
您将如何分配候选键？是否可以？
Example: I have a product table that consists of:
示例：我有一个产品表，其中包含：
prod_id, prod_name, prod_price, QOH
Where I want prod_name to link to the despatch table:
我希望 prod_name 链接到发货表的位置：
desp_id, prod_name, shelfLoc, quantity
What I was thinking is that I may need to create a unique constraint which will look like this:
我在想的是我可能需要创建一个独特的约束，它看起来像这样：
ALTER TABLE product
ADD CONSTRAINT prod_nameID_uc
UNIQUE (prod_id,prod_name)
What I'm wondering is, if it is possible to refer to a unique key as a foreign key in the despatch table. I have to have prod_namerather than prod_idin the despatch table so that the information is more meaningful to the user when reading it, rather than seeing an id number.
I am using iSQL plus on oracle.
我想知道的是，是否可以在调度表中将唯一键称为外键。我必须有prod_name而不是prod_id在发送表中，这样信息在阅读时对用户更有意义，而不是看到一个 id 号。我在 oracle 上使用 iSQL plus。
回答by APC
It is perfectly possible to reference a UNIQUE constraint in an Oracle FOREIGN KEY:
在 Oracle FOREIGN KEY 中引用 UNIQUE 约束是完全可能的：
SQL> create table products (
2 prod_id number not null
3 , prod_name varchar2 (30) not null
4 , constraint prod_pk primary key ( prod_id )
5 , constraint prod_uk unique ( prod_name )
6 )
7 /
Table created.
SQL> create table despatch (
2 desp_id number not null
3 , prod_name
4 , constraint desp_pk primary key ( desp_id )
5 , constraint desp_prod_pk foreign key ( prod_name )
6 references products ( prod_name )
7 )
8 /
Table created.
SQL>
It is however bad practice. The main reason for using a primary key alongside a unique key is to provide a synthetic key for use in foreign keys. I were you I would be concerned that your teachers are giving you an assignment riddled with bad practice.
然而，这是不好的做法。将主键与唯一键一起使用的主要原因是提供用于外键的合成键。我是你，我会担心你的老师会给你一个充满不良做法的作业。
回答by chaos
This is necessarily DBMS dependent. In the DBMSes I'm familiar with, the unique constraint and the foreign key constraint are separate considerations, you can have both, and they both act normally when combined.
这必然依赖于 DBMS。在我熟悉的 DBMS 中，唯一约束和外键约束是单独的考虑因素，您可以同时拥有它们，并且它们在组合时都可以正常运行。
