Re: Solved! Re: ERROR 1005: Can't create table (errno: 150) :: InnoDB

[Roland Booth]

Here is my example and some pointers that lead a soln to my problem.

'contact_id' in table 'note' referencing 'id' in table 'contact'

CREATE TABLE `contact` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Contact ID',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0 ;

CREATE TABLE `note` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT 'Note ID',
`entity_table` varchar(64) collate utf8_unicode_ci NOT NULL COMMENT 'Name of table where item being referenced is stored.',
`entity_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
`contact_id` int(10) unsigned default NULL COMMENT 'FK to Contact ID creator',
PRIMARY KEY (`id`),
KEY `index_entity` (`entity_table`,`entity_id`),
KEY `FK_note_contact_id` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=0;

ALTER TABLE `note`
ADD CONSTRAINT `FK_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON DELETE SET NULL;

Things to note

1. Tables must be same type. [in this case InnoDB]
2. The columns must be the same type.[in this case int(10)]
3. The Attributes must be the same unsigned
4. The field that is referencing must be default set to null "default NULL"
i.e. contact_id , must be default NULL. (If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.)
5. Indexes on foreign keys and referenced keys

Useful reference:
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html