Re: Foreign Key Error 121
Heikki,
There is actually 2 constraints that have this issue. There is FK_staffmembers_2 and FK_staffmembers_3. Both constraints are references to the same table and same column. Its basically represents a staff member and their internal and external office telephone numbers.
DROP TABLE IF EXISTS `staffmember`;
CREATE TABLE `staffmember` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL,
`internal_staff_member_id` varchar(15) default NULL,
`first_names` varchar(100) default NULL,
`last_name` varchar(100) default NULL,
`work_title` varchar(128) default NULL,
`email` varchar(100) default NULL,
`address` varchar(200) default NULL,
`phone` varchar(50) default NULL,
`mobile` varchar(50) default NULL,
`emergency_contact_1` varchar(100) default NULL,
`emergency_contact_1_phone` varchar(50) default NULL,
`emergency_contact_1_mobile` varchar(50) default NULL,
`emergency_contact_2` varchar(100) default NULL,
`emergency_contact_2_phone` varchar(50) default NULL,
`emergency_contact_2_mobile` varchar(50) default NULL,
`start_date` datetime default NULL,
`end_date` datetime default NULL,
`notes` text,
`external_office_number` int(11) default NULL,
`internal_office_number` int(11) default NULL,
`skype_name` varchar(45) default NULL,
PRIMARY KEY (`id`),
KEY `FK_staff_user` (`user_id`),
KEY `FK_external_office_number` (`external_office_number`),
KEY `FK_internal_office_number` (`internal_office_number`),
CONSTRAINT `FK_staffmembers_2` FOREIGN KEY (`external_office_number`) REFERENCES `phonenumber` (`id`),
CONSTRAINT `FK_staffmembers_3` FOREIGN KEY (`internal_office_number`) REFERENCES `phonenumber` (`id`),
CONSTRAINT `staffmember_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Above is the table definition. These tables were never altered from MyIASM to Innodb.
The tables were renamed before but I dont think this caused it. Another developer here, can use the same script without problem, so It is obviously something that has happened with my ibdata file.
Ill keep digging around trying to reproduce it on a clean mysql installation.