I need help with a script that use Foreign Keys and InnoDB

[ben0it24]

CREATE TABLE `pmd_machinery` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_id` int(11) DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `domain_ID` (`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=95 ;

CREATE TABLE `pmd_domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL,
  `ordering` smallint(6) NOT NULL,
  KEY `ID` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

ALTER TABLE `pmd_machinery`
ADD CONSTRAINT `Reference2` FOREIGN KEY (`domain_ID`) REFERENCES `pmd_domains` (`domain_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

 

I use Mysql 5.1.33

 

and i receive a error :

ERROR 1005 <HY000> at line 1245342: 'Can't create the table mamaison_db1.#sql-a18_3f' Errcode: 150

 

Thanks

[Mchl]

#sql-a18_3f is not a correct table name. Why your script try to create a table called like that, is up to you to find out.

[JustLikeIcarus]

The References section of your add constraint statement has an error specifically REFERENCES `pmd_domains` (`domain_ID`) should be REFERENCES `pmd_domains` (`id`) as pmd_domains.domain_id isnt the name of the column.