如何修复错误"there is no unique constraint matching given keys for referenced table"

Question

我正试图在pgadmim中创建一些表。

尽管在tb_discpline和tb_round这两个表中，规程_id是主键，但我得到了错误：

引用表没有与给定键匹配的唯一约束"tb_round"；

添加完整代码：

CREATE TABLE tb_discipline (
discipline_id INT NOT NULL,
name CHARACTER VARYING(50) NOT NULL,
inventor CHARACTER VARYING(50)  NOT NULL,
type CHARACTER VARYING(10)  NOT NULL,
object_type CHARACTER(20) DEFAULT NULL,
CONSTRAINT PK_tb_discipline PRIMARY KEY(discipline_id)
);
------------------------------------------------------------------------------------------------
--
-- Create table tb_athlete
--
------------------------------------------------------------------------------------------------
CREATE TABLE tb_athlete (
athlete_id CHARACTER(7) NOT NULL,
name CHARACTER VARYING(50)  NOT NULL,
country CHARACTER(3)  NOT NULL,
substitute_id CHARACTER (7),
CONSTRAINT PK_tb_athlete PRIMARY KEY(athlete_id),
CONSTRAINT FK_athlete_substitute  FOREIGN KEY (substitute_id) REFERENCES tb_athlete(athlete_id)
);
------------------------------------------------------------------------------------------------
--
-- Create table tb_play 
--
------------------------------------------------------------------------------------------------
CREATE TABLE tb_play (
athlete_id CHARACTER(7) NOT NULL,
discipline_id INT NOT NULL,
CONSTRAINT FK_play_athlete  FOREIGN KEY (athlete_id) REFERENCES tb_athlete(athlete_id),
CONSTRAINT FK_play_discipline  FOREIGN KEY (discipline_id) REFERENCES tb_discipline(discipline_id)
);
------------------------------------------------------------------------------------------------
--
-- Create table tb_round
--
------------------------------------------------------------------------------------------------
CREATE TABLE tb_round (
round_number INT NOT NULL,
discipline_id INT NOT NULL,
CONSTRAINT PK_tb_round PRIMARY KEY(round_number, discipline_id),
CONSTRAINT FK_round_discipline  FOREIGN KEY (discipline_id) REFERENCES tb_discipline(discipline_id)
);
------------------------------------------------------------------------------------------------
--
-- Create table tb_register  
--
------------------------------------------------------------------------------------------------
CREATE TABLE tb_register (
athlete_id CHARACTER(7) NOT NULL,
round_number INT NOT NULL,
discipline_id INT NOT NULL UNIQUE,
register_date DATE NOT NULL DEFAULT CURRENT_DATE,
register_position INT,
register_time TIME,
register_measure REAL,
CONSTRAINT PK_tb_register PRIMARY KEY(athlete_id,round_number,discipline_id),
CONSTRAINT FK_register_athlete  FOREIGN KEY (athlete_id) REFERENCES tb_athlete(athlete_id),
CONSTRAINT FK_register_round_discipline  FOREIGN KEY (discipline_id) REFERENCES tb_round(discipline_id),
CONSTRAINT FK_register_round_number FOREIGN KEY (round_number) REFERENCES tb_round(round_number)
);

知道我该怎么解决这个问题吗？

Answer 1

在tb_register中有两个外键引用round，但只有其密钥的一部分。使其引用完整键。

CREATE TABLE tb_register
(...
CONSTRAINT fk_register_round_number_discipline_id
FOREIGN KEY (round_number,
discipline_id)
REFERENCES tb_round
(round_number,
discipline_id)
...);