如何添加倍数外键"ORA-02270: no matching unique or primary key for this column-list"

Question

create table coc (
WS_NO number(10,0) PRIMARY KEY,
last_name varchar2(30),
first_name varchar2(30),
middle_initial varchar2(1),
Email varchar2(2),
contact_no varchar2(11),
date_to_be_submitted date,
applicant_id number(10,0),
company_name VARCHAR2(50),
plant_location VARCHAR2(50),
sample_id number(10,0),
sample_name VARCHAR2(50),
parameters VARCHAR2(50),
CONSTRAINT APPLICANT_FK FOREIGN KEY (applicant_id, company_name, plant_location) REFERENCES APPLICANT (applicant_id, company_name, plant_location),
CONSTRAINT SAMPLE_FK FOREIGN KEY (sample_id, sample_name, parameters) REFERENCES SAMPLE (sample_id, sample_name, parameters));

Answer 1

我认为错误信息非常清楚。外键约束中提到了列，但它们没有定义。如果您希望它们引用其他内容，则需要定义它们:

create table coc (
    WS_NO number(10,0) PRIMARY KEY,
    last_name varchar2(30),
    first_name varchar2(30),
    middle_initial varchar2(1),
    Email varchar2(2),
    contact_no varchar2(11),
    date_to_be_submitted date,
    applicant_id ??,
    company_name ??,
    plant_location ??,
    sample_id ??,
    sample_name ??,
    CONSTRAINT APPLICANT_FK FOREIGN KEY (applicant_id, company_name, plant_location) REFERENCES APPLICANT (applicant_id, company_name, plant_location),
    CONSTRAINT SAMPLE_FK FOREIGN KEY (sample_id, sample_name, parameters) REFERENCES SAMPLE (sample_id, sample_name, plant_location)
);

Answer 2

列(applicant_id, company_name, plant_location)和(sample_id, sample_name, parameters)的集合必须在引用的表application和SAMPLE中设置为唯一键或主键。