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