How to fix error "there is no unique constraint matching given keys for referenced table"

Question

I am trying to create some tables in pgadmim.

Although in both tables tb_discipline and tb_round the discipline_id is the primary key I get the error:

there is no unique constraint matching given keys for the referenced table "tb_round"

Adding the full code:

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)
    );

Any idea how can I solve this?

Answer 1

You have two foreign keys in tb_register referencing round but only part of its key. Make that one referencing the complete key.

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