Unable to create foreign key constraint in MySQL. Error number 150

I am attempting to create a data model. It's fairly complicated, but I'm trying to link the Splits table to the Trials table. A screenshot of the model is below:

I'm attempting to make Splits.protocol + Splits.resultID + Splits.trialNumber a foreign key to Trials. Those three relations are the primary key of Trials. I'm doing this with MySQL Workbench and it throws an Error #150. Does anyone know what the problem is?

Here is the SQL statement and the error that it throws when attempting to execute it:

ERROR 1005: Can't create table '403898_BAMNormalized.#sql-7285_6c29081' (errno: 150)

SQL Statement:

ALTER TABLE `403898_BAMNormalized`.`Splits` 

ADD CONSTRAINT `FK_FromTrial`
    FOREIGN KEY (`protocol` , `resultID` , `trialNumber`)
    REFERENCES `403898_BAMNormalized`.`Trials` (`protocol` , `resultID` , `trialNumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'Splits' already exists

SQL Statement:

CREATE TABLE `Splits` (
    `protocol` varchar(255) NOT NULL,
    `resultID` int(11) NOT NULL,
    `trialNumber` int(11) NOT NULL,
    `splitNumber` int(11) NOT NULL,
    `splitScore` decimal(10,0) NOT NULL,
    PRIMARY KEY (`protocol`,`resultID`,`trialNumber`,`splitNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here are the create table statements:

CREATE TABLE `Trials` (
    `resultID` int(11) NOT NULL,
    `protocol` varchar(255) NOT NULL,
    `trialNumber` int(11) NOT NULL,
    `trialScore` decimal(10,0) NOT NULL,
    `best` char(1) DEFAULT NULL,
    `DQFlag` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`resultID`,`protocol`,`trialNumber`),
    CONSTRAINT `FK_trialID` FOREIGN KEY (`resultID`, `protocol`) REFERENCES `ResultsDetails` (`resultID`, `protocol`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Splits` (
    `protocol` varchar(255) NOT NULL,
    `resultID` int(11) NOT NULL,
    `trialNumber` int(11) NOT NULL,
    `splitNumber` int(11) NOT NULL,
    `splitScore` decimal(10,0) NOT NULL,
    PRIMARY KEY (`protocol`,`resultID`,`trialNumber`,`splitNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indexes of Trials table:

Answers


Reordering the index to represent the same order as the foreign key was the solution. See comments appended to original post for detailed information.


Need Your Help

SQL Server not connecting

sql-server

I have created a form and connected to a SQL Server database. The data entered in the form enters into the database. But when I upload the same form into the upload the form gets submitted the but ...