MySQL Forums
Forum List  »  InnoDB

Re: Foreign Key Error 121
Posted by: Stephen Lynch
Date: April 10, 2006 07:48AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
22667
April 07, 2006 08:35AM
6490
April 09, 2006 01:38PM
7544
April 10, 2006 05:52AM
4171
April 17, 2009 09:54AM
4530
April 10, 2006 07:06AM
Re: Foreign Key Error 121
4400
April 10, 2006 07:48AM
3857
April 10, 2006 06:06PM
5148
April 11, 2006 09:08AM
3793
April 11, 2006 02:49PM
3960
September 17, 2007 05:54PM
6268
September 26, 2007 09:55AM
3525
July 07, 2008 06:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.