Home > database >  SQL set FOREIGN KEY to a non unique value throws error in sql developer
SQL set FOREIGN KEY to a non unique value throws error in sql developer

Time:04-22

I have a N:M situation, but I can't use any of the 2 non unique values as a Foreign Key can anyone help?? Both Platform_Id and Station_Id can not be set as a foreign key in the Platform_Host table.

CREATE TABLE Repair_Platform
(
    Platform_Id INT NOT NULL,
    Station_Id INT NOT NULL,
    Mechanic_Id INT NOT NULL UNIQUE,
    Validation_Num INT NOT NULL,

    CONSTRAINT Platform_fk1 
        FOREIGN KEY (Station_Id) REFERENCES Repair_Station (Station_Id),
    CONSTRAINT Platform_fk2 
        FOREIGN KEY (Mechanic_Id) REFERENCES Engineer (Engineer_Id),
    CONSTRAINT Platform_pk  
        PRIMARY KEY (Station_Id, Platform_Id)
);

CREATE TABLE Platform_Host
(
    Station_Id INT NOT NULL,
    Platform_Id INT NOT NULL,
    Vehicle_Id VARCHAR(8) NOT NULL,
    DateTime DATE NOT NULL,

    CONSTRAINT Host_fk1 
        FOREIGN KEY (Station_Id) REFERENCES Repair_Platform (Station_Id),
    CONSTRAINT Host_fk2 
        FOREIGN KEY (Vehicle_Id) REFERENCES Vehicle (License_Plate),
    CONSTRAINT Host_fk3 
        FOREIGN KEY (Platform_Id) REFERENCES Repair_Platform (Platform_Id)
);

Error:

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

CodePudding user response:

You need to reference the whole primary key of Repair_Platform - like this:

CREATE TABLE Platform_Host
(
    -- columns
    -- constraints

    CONSTRAINT Host_fk3 
        FOREIGN KEY (StationId, Platform_Id) 
             REFERENCES Repair_Platform (StationId, Platform_Id)
);

You cannot reference only parts of a primary key - it's an all or nothing deal.

  • Related