复合外键 - 在 Oracle 中可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8434174/
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
Composite Foreign Key - Possible In Oracle?
提问by Jatinder Singh
I am trying to create a relation/table in Oracle that is between two many to many tables and therefore the Primary key of this table is a composite key but both keys are foreign.
我试图在 Oracle 中创建一个关系/表，它位于两个多对多表之间，因此该表的主键是复合键，但两个键都是外键。
CREATE TABLE employee_licence_certificate(
emp_id NUMBER(4) REFERENCES employee(emp_id)
, licence_cert_code VARCHAR2(6) REFERENCES licence_certificate(licence_cert_code)
, date_earned DATE NOT NULL
)
PRIMARY KEY (emp_id, licence_cert_code))
I have tried using the method for composition keys but I seem to get the following error, which is starting to make me wonder is this even possible?
我曾尝试使用组合键的方法，但我似乎收到以下错误，这让我开始怀疑这是否可能？
Error starting at line 1 in command:
CREATE TABLE employee_licence_certificate(emp_id NUMBER(4) REFERENCES employee(emp_id)
, licence_cert_code VARCHAR2(6) REFERENCES licence_certificate(licence_cert_code)
, date_earned DATE NOT NULL) PRIMARY KEY (emp_id, licence_cert_code))
Error at Command Line:3 Column:29
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"
*Cause:
*Action:
回答by chance
Try this:
尝试这个：
CREATE TABLE employee_licence_certificate(
emp_id NUMBER(4) REFERENCES employee(emp_id)
, licence_cert_code VARCHAR2(6) REFERENCES licence_certificate(licence_cert_code)
, date_earned DATE NOT NULL
,
PRIMARY KEY (emp_id, licence_cert_code))
回答by Sodved
I use a different syntax. I prefer to explicitly name my foreign key constraints so that the error message if/when its violated is more meaningful/tracable. So I would do it something like this:
我使用不同的语法。我更喜欢明确地命名我的外键约束，以便错误消息（如果/何时违反）更有意义/可追踪。所以我会这样做：
CREATE TABLE employee_licence_certificate
( emp_id NUMBER(4) NOT NULL
, licence_cert_code VARCHAR2(6) NOT NULL
, date_earned DATE NOT NULL
, CONSTRAINT elc_pk PRIMARY KEY (emp_id, licence_cert_code)
, CONSTRAINT elc_emp_fk FOREIGN KEY (emp_id)
REFERENCES employee(emp_id)
, CONSTRAINT elc_lct_fk FOREIGN KEY (licence_cert_code )
REFERENCES licence_certificate(licence_cert_code)
)
回答by Daniel Hilgarth
It sure is possible. You simply need to fix your statement:
这肯定是可能的。你只需要修正你的陈述：
CREATE TABLE employee_licence_certificate(emp_id NUMBER(4) REFERENCES employee(emp_id)
, licence_cert_code VARCHAR2(6) REFERENCES licence_certificate(licence_cert_code)
, date_earned DATE NOT NULL, PRIMARY KEY (emp_id, licence_cert_code))
By the way, it is MUCH easier to spot such errors when you format your statements properly:
顺便说一句，当您正确格式化语句时，更容易发现此类错误：
create table employee_licence_certificate
(
emp_id number(15) references employee(emp_id),
licence_cert_code VARCHAR2(6) REFERENCES licence_certificate(licence_cert_code),
date_earned date not null,
primary key (emp_id, licence_cert_code)
)
