ORA-02270: Error report: no matching unique or primary key for this column list

foreign keyoracleoracle-11g-r2primary-key

I have created two tables:

CREATE TABLE team (
    teamname        VARCHAR2(30) NOT NULL,
    carndate        DATE NOT NULL,
    teamnomembers   NUMBER(2) NOT NULL,
    charname        VARCHAR2(30),
    entryno         NUMBER(4) NOT NULL
);

COMMENT ON COLUMN team.teamname IS
    'Team name';

COMMENT ON COLUMN team.carndate IS
    'Date of carnival';

COMMENT ON COLUMN team.teamnomembers IS
    'Number of team members';

COMMENT ON COLUMN team.charname IS
    'Approved charity name';

COMMENT ON COLUMN team.entryno IS
    'Team leader for this team in this carnival';

ALTER TABLE team ADD CONSTRAINT team_pk PRIMARY KEY ( teamname,
                                                      carndate );

and another:

CREATE TABLE entry (
    entryno           NUMBER(4) NOT NULL,
    carndate          DATE NOT NULL,
    entrystarttime    DATE NOT NULL,
    entryfinishtime   DATE NOT NULL,
    entryplace        NUMBER(4) NOT NULL,
    charname          VARCHAR2(30) NOT NULL,
    compno            NUMBER(4) NOT NULL,
    eventypecode      CHAR(3) NOT NULL,
    teamname          VARCHAR2(30) NOT NULL
);

ALTER TABLE entry ADD CONSTRAINT entry_pk PRIMARY KEY ( entryno, 
                                                        carndate );

ALTER TABLE entry
    ADD CONSTRAINT entry_charity_fk FOREIGN KEY ( charname )
        REFERENCES charity ( charname );

ALTER TABLE entry
    ADD CONSTRAINT entry_carnival_fk FOREIGN KEY ( carndate )
        REFERENCES carnival ( carndate );

ALTER TABLE entry
    ADD CONSTRAINT entry_team_fk FOREIGN KEY ( teamname )
        REFERENCES team ( teamname );

When i run the script in Oracle, I'm getting an error report saying 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

and the error is reported at the Entry table, where i set the foreign key "teamname" at the last line. Why am i getting this error?

Best Answer

Foreign keys must reference another table's primary key, or a unique column in the other table (that is, a column with a unique constraint or index of some sort).

teamname is not unique in team, so a foreign key cannot link to it.

you need to either make teamname unique in team, or use a composite foreign key to match `team's composite primary key:

ALTER TABLE entry
    ADD CONSTRAINT entry_team_fk FOREIGN KEY ( teamname, carndate )
        REFERENCES team ( teamname, carndate );

Note: carndate may not be appropriate for this foreign key - just using it as an example. You would know best if there's a date in entry that would match team.carndate.

Related Question