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

Questions

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

November 7, 2022

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

>Solution :

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