Why getting error: No matching unique or primary key for this column-list; if I have the same column-CodePudding

I need to add a Foreign key to my Date_Reserved attribute in the Additional_extra Table, (Maybe because the Date type can't be unique? I keep getting the next error:

Error report - ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "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

For The next Code:

ALTER TABLE Additional_Extra
Add(
    CONSTRAINT test_date
        FOREIGN KEY(Date_Reserved)
        REFERENCES Reservation(Date_Reserved)
);

My Reservation Table:

CREATE TABLE Reservation(
Booking_Number INT NOT NULL,
Room_Number INT NOT NULL,
Date_Reserved DATE NOT NULL,
PRIMARY KEY(Booking_Number, Room_Number, Date_Reserved),
FOREIGN KEY(Booking_Number) REFERENCES Booking(Booking_Number),
FOREIGN KEY(Room_Number) REFERENCES Room(Room_Number)
);

And my Additional_Extra Table:

  CREATE TABLE Additional_Extra(
    Booking_Number INT NOT NULL,
    Room_Number INT NOT NULL,
    Date_Reserved DATE NOT NULL,
    Extra_ID INT NOT NULL,
    PRIMARY KEY(Booking_Number, Room_Number, Date_Reserved, Extra_ID),
    FOREIGN KEY(Booking_Number) REFERENCES Booking(Booking_Number),
    FOREIGN KEY(Room_Number) REFERENCES Room(Room_Number),
    FOREIGN KEY(Extra_ID) REFERENCES Extra(Extra_ID)
    );

This is the part of the task that I need to create. So it is given that Date_Reserved must be FK:

Any Suggestion what is wrong? I tried to add FK when creating the Table, however when I find out that just the Date_Reserved FK line is wrong I created without that, but the error is still the same. I tried from the GUI to add a Foreign key but same error

CodePudding user response：

date_reserved is part of a composite primary key consisting of many columns; there is no unique or primary key that is solely on the date_reserved column.

Your constraint:

ALTER TABLE Additional_Extra
Add(
    CONSTRAINT test_date
        FOREIGN KEY(Date_Reserved)
        REFERENCES Reservation(Date_Reserved)
);

Is trying to refer to a unique constraint that is solely on the date_reserved column and that does not exist so the SQL engine (correctly) raises the exception that such a constraint does not exist.

What you need to do is refer to the entire composite key:

ALTER TABLE Additional_Extra
Add(
    CONSTRAINT test_date
        FOREIGN KEY(Booking_Number, Room_Number, Date_Reserved)
        REFERENCES Reservation(Booking_Number, Room_Number, Date_Reserved)
);

fiddle