Error :ORA-02270

Question

The error I got .

FOREIGN KEY (bid)        REFERENCES ALLOCATION (bid),        * 

Error at line 8: ORA-02270: no matching unique or primary key for this column-list

Not sure how to fix it. ALLOCATION and WORKSESSION are week entities. Any helps would be appreciated.

    CREATE TABLE BOOK(
    bid          number(4),
    title        varchar2(30) CONSTRAINT NN_TITLE NOT NULL,
    sellingprice number(6,2) CONSTRAINT CHK_SELLING_PRICE CHECK(sellingprice>0),
    PRIMARY KEY  (bid)
    );

    CREATE TABLE AUTHOR(
    authorid    number(4),
    sname       varchar2(30),
    fname       varchar(30),
    PRIMARY KEY (authorid),
    CONSTRAINT UC_AUTHOR_NAME UNIQUE(sname,fname)
    );

    CREATE TABLE ALLOCATION(
    bid         number(4),
    authorid    number(4),
    payrate     number(6,2) CONSTRAINT CHK_PAY_RATE CHECK(payrate>1 AND payrate<79.99),
    PRIMARY KEY (bid,authorid),
    FOREIGN KEY (bid)      REFERENCES BOOK   (bid),
    FOREIGN KEY (authorid) REFERENCES AUTHOR (authorid)
    );

    CREATE TABLE WORKSESSION(
    bid       number(4),
    authorid  number(4),
    WorkYear  number(4)   CONSTRAINT CHK_WORKYEAR  CHECK(WorkYear<2013 AND WorkYear>2011),
    WorkWeek  number(2)   CONSTRAINT CHK_WORKWEEK  CHECK(WorkWeek>1 AND WorkWeek<52),
    WorkHours number(4,2) CONSTRAINT CHK_WORKHOURS CHECK(WorkHours>0.5 AND WorkHours<99.99),
    PRIMARY KEY (bid,authorid,WorkYear,WorkWeek),
    ***FOREIGN KEY (bid)       REFERENCES ALLOCATION (bid), 
    FOREIGN KEY (authorid) REFERENCES ALLOCATION (authorid)
    );

Answer 1

Your FK must point to an existing Primary Key (PK) or Unique Key(UK);

Use this create statement for last table:

CREATE TABLE WORKSESSION(
    bid       number(4),
    authorid  number(4),
    WorkYear  number(4)   CONSTRAINT CHK_WORKYEAR  CHECK(WorkYear<2013 AND WorkYear>2011),
    WorkWeek  number(2)   CONSTRAINT CHK_WORKWEEK  CHECK(WorkWeek>1 AND WorkWeek<52),
    WorkHours number(4,2) CONSTRAINT CHK_WORKHOURS CHECK(WorkHours>0.5 AND WorkHours<99.99),
    PRIMARY KEY (bid,authorid,WorkYear,WorkWeek),
    ***FOREIGN KEY (bid)       REFERENCES ALLOCATION (bid), 
    FOREIGN KEY (bid, authorid) REFERENCES ALLOCATION (bid, authorid)
    );