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.