MariaDB: ALTER TABLE syntax to add a FOREIGN KEY?

Question

what'S wrong with the following statement?

ALTER TABLE submittedForecast
  ADD CONSTRAINT FOREIGN KEY (data) REFERENCES blobs (id);

The error message I am getting is

Can't create table `fcdemo`.`#sql-664_b` (errno: 150 "Foreign key constraint is incorrectly formed")

Answer 1

This works for me on MariaDB 10.1.8:

CREATE TABLE `submittedforecast` (
    `id` INT(11) NOT NULL,
    `data` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `data` (`data`)
) ENGINE=InnoDB;    
CREATE TABLE `blobs` (
    `id` INT(11) NOT NULL,
    `content` BLOB NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
ALTER TABLE submittedForecast
  ADD CONSTRAINT FOREIGN KEY (data) REFERENCES blobs (id);

Can you give your MariaDB version number and a complete example including the CREATE TABLE statements for submittedForecast and blobs?

Answer 2

No idea if you already solved this but make sure both engines and collations match between tables (e.g: latin1 to latin1 and InnoDB to InnoDB).

I was having the same issue and by switching these I managed to get it working.