Why I cannot execute this auto generated script with mysql workbench
Your dump is somewhat weird, but your "repaired" part seems good (only missing one space, I suppose typo in question) - did it give different error? Column types do not match:

`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT NULL,


One is UNSIGNED and the other is not, that is one problem. Make them both either signed or unsigned (usually unsigned is the right one for autoincrement)

I just added missing space and some backticks to be safe:

CONSTRAINT `user_id` FOREIGN KEY (`user_id`)
    REFERENCES `motivian`.`user`(`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION);


Check it at http://sqlfiddle.com/#!9/9dca1/1