Oracle (ORA-02270) : no matching unique or primary key for that column list-CodePudding

I have two tables:

CREATE TABLE Trasy_srodki_transportu(
  ID_Trasy Integer NOT NULL,
  ID_pojazdu Integer NOT NULL
) 
/
CREATE TABLE Trasy(
  ID_Trasy Integer NOT NULL,
  Linia Varchar2(4 ) NOT NULL,
  Data_rozpoczecia_kursowania Date NOT NULL,
  Data_zakonczenia_kursowania Date,
  ID_Pracownika Integer NOT NULL
)

Now i want to add foreign key to Trasy_srodki_transportu referencing to Trasy table:

ALTER TABLE Trasy_srodki_transportu ADD CONSTRAINT Trasa_jest_wykorzystywana FOREIGN KEY (ID_Trasy) REFERENCES Trasy (ID_Trasy)
/

and this throws Oracle (ORA-02270) : no matching unique or primary key for this column-list error. Any suggestions how to fix this?Data modeler view

CodePudding user response：

A foreign key needs to reference a key on the related table, but it's not the case in your example. Change the definition of the second table by adding a PRIMARY KEY constraint in it, as in:

CREATE TABLE Trasy (
  ID_Trasy Integer PRIMARY KEY NOT NULL,
  Linia Varchar2(4 ) NOT NULL,
  Data_rozpoczecia_kursowania Date NOT NULL,
  Data_zakonczenia_kursowania Date,
  ID_Pracownika Integer NOT NULL
)

Alternatively, you can create a unique constraint on it, that can also serve as a key. For example:

CREATE TABLE Trasy (
  ID_Trasy Integer NOT NULL,
  Linia Varchar2(4 ) NOT NULL,
  Data_rozpoczecia_kursowania Date NOT NULL,
  Data_zakonczenia_kursowania Date,
  ID_Pracownika Integer NOT NULL,
  CONSTRAINT uq_idtrasy UNIQUE (ID_Trasy)
)

CodePudding user response：

Either create the foreign key while creating the table itself or insert some related data to both the tables and then try Alter as foreign key expects the related columns data to be existing in the related parent table