Hi
I would really appreciate any help with resolving the problem I appear to have with my foreign-key definition in the query below.
I have read
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html but I still can't figure out where my query is malformed...
This is the error:
ERROR 1005 (HY000): Can't create table './myDB/sG.frm' (errno: 150)
The failing 'create table' query:
----
CREATE TABLE `sG`
(
`nID` mediumint(8) unsigned NOT NULL default '0',
`tabA` varchar(30) collate utf8_unicode_ci NOT NULL default '',
`lastUpdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`nID`,`tabA`),
KEY `permitabA_fkey` (`tabA`),
CONSTRAINT `permitabA_fkey` FOREIGN KEY (`tabA`) REFERENCES `permitSG` (`tabA`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
----
The referenced table:
----
CREATE TABLE `permitSG` (
`tabA` varchar(30) NOT NULL default '',
`desc` varchar(255) default NULL,
PRIMARY KEY (`tabA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
----
Many thanks
NJH