Here is my example and some pointers that lead a solution 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
6. Watch your case. the FK is case sensitive
If you need to alter a table do the following
set foreign_key_checks = 0;
...
Alter table etc..
..
set foreign_key_checks = 1;
Useful reference:
See also:
