Xrhrft

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?

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.