I'm working on a web app where the Django authorized user reference is a foreign key of a table. Creating said table throws an error 1005, and I can't understand why.
CREATE TABLE `main_message` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`strat_key` int(10) UNSIGNED DEFAULT NULL,
`prod_task` int(10) UNSIGNED DEFAULT NULL,
`task` int(10) UNSIGNED DEFAULT NULL,
`user` int(10) UNSIGNED NOT NULL,
`message` varchar(255) NOT NULL,
`date_stamp` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user` (`user`),
KEY `task` (`task`),
KEY `prod_task` (`prod_task`),
KEY `strat_key` (`strat_key`),
CONSTRAINT `main_message_ibfk_1` FOREIGN KEY (`user`) REFERENCES `auth_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `main_message_ibfk_2` FOREIGN KEY (`task`) REFERENCES `main_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `main_message_ibfk_3` FOREIGN KEY (`prod_task`) REFERENCES `prod_main_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `main_message_ibfk_4` FOREIGN KEY (`strat_key`) REFERENCES `prod_main_strategicdirection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
Executing
SHOW ENGINE INNODB STATUS
resulted in this snippet:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130806 3:50:26 Error in foreign key constraint of table rmstg/main_message:
FOREIGN KEY (`user`) REFERENCES `auth_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `main_message_ibfk_2` FOREIGN KEY (`task`) REFERENCES `main_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `main_message_ibfk_3` FOREIGN KEY (`prod_task`) REFERENCES `prod_main_task` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `main_message_ibfk_4` FOREIGN KEY (`strat_key`) REFERENCES `prod_main_strategicdirection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
The strange thing is, I executed this create query on my local database without an error, and this table is essentially a drop in replacement with a few extra FK fields for an existing table.
auth_user does indeed contain a id field, which makes the error odd.
