sql - 主键和外键约束混淆

Question

目前正在尝试将外键应用于我创建的表，但出现 SQL 错误:

SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
       gives a column-list for which there is no matching unique or primary
       key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
       catalog view

我不知道从哪里开始，我认为一切都井井有条，但我无法指出哪里出了问题。

如果有人能指出我哪里出错了，这里是我正在使用的相关创建和更改脚本:

创建结果和兴奋剂测试:

CREATE TABLE Results
(
RaceID NUMBER,
HorseID NUMBER,
JockeyID NUMBER,
Position numeric(2)
);

CREATE TABLE DopingTest
(
RaceID NUMBER,
HorseID NUMBER,
TakenBy varchar2(60)
);

以及添加约束:

ALTER TABLE Results
ADD(
  CONSTRAINT pk_ResultsID
    PRIMARY KEY (RaceID,HorseID));

ALTER TABLE DopingTest
ADD(
  CONSTRAINT pk_DopingTest
    PRIMARY KEY (RaceID, HorseID));

ALTER TABLE Results
ADD(
  CONSTRAINT fk_raceID
    FOREIGN KEY (RaceID)
    REFERENCES Race(RaceID),
  CONSTRAINT fk_horseID
    FOREIGN KEY (HorseID)
    REFERENCES Horse(HorseID),
  CONSTRAINT fk_JockeyID
    FOREIGN KEY (JockeyID)
    REFERENCES Jockey(JockeyID));

ALTER TABLE DopingTest
ADD(
  CONSTRAINT fk_RaceIDDT
    FOREIGN KEY (RaceID)
    REFERENCES Results(RaceID),
  CONSTRAINT fk_HorseIDDT
    FOREIGN KEY (HorseID)
    REFERENCES Results(HorseID));

任何帮助将不胜感激，谢谢。

Answer 1

如果 results 有复合主键，则外键必须引用键的两个组件。

ALTER TABLE DopingTest
ADD(
  CONSTRAINT fk_RaceIDDT
    FOREIGN KEY (RaceID, HorseID)
    REFERENCES Results(RaceID, HorseID)
);