如何修復錯誤“沒有唯一約束匹配參考表的給定鍵”

我正在嘗試在 pgadmim 中創建一些表。

盡管在 tb_discipline 和 tb_round 兩個表中，jection_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)
    );

知道我該如何解決這個問題嗎？

uj5u.com熱心網友回復：

您在tb_register參考輪中有兩個外鍵，但只有部分鍵。使那個參考完整的鍵。

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

標籤：sql PostgreSQL 独特的 首要的关键

上一篇：使用bash-c逐行決議

下一篇：如何按彼此接近的時間戳對SQL表進行分組？