I have a form that allows a user to edit some company data. When they click submit, I sometimes need to create records in various tables. For instance, if a user edits a company which has no CONTACT_INFO record or IM records, I must create these records if the user has entered contact_info or instant message screen names in the form.
The form has two fields for IM screen names. If the user enters only one, i encounter a problem. I am successfully able to create an IM record for that one screen name but I cannot then create a contact_info record that will point to it.
More specifically, this query:
INSERT INTO contact_info (email1, email2, mobile_email, day_phone, eve_phone, cell, day_fax, eve_fax, street_1, street_2, city, state, postal_code, country, website, im1_id, im2_id) VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '31787', '')
yields this error:
mysql wrote:Cannot add or update a child row: a foreign key constraint fails (dvi_aam052107/contact_info, CONSTRAINT Ref_81 FOREIGN KEY (im2_id) REFERENCES im (id) ON DELETE NO ACTION ON UPDATE NO ACTION)
the contact_info table (including Ref_81) is structured thusly:
CREATE TABLE `contact_info` (
`id` int(11) NOT NULL auto_increment,
`email1` varchar(64) default NULL,
`email2` varchar(64) default NULL,
`mobile_email` varchar(64) default NULL,
`day_phone` varchar(32) default NULL,
`eve_phone` varchar(32) default NULL,
`cell` varchar(32) default NULL,
`day_fax` varchar(32) default NULL,
`eve_fax` varchar(32) default NULL,
`street_1` varchar(64) default NULL,
`street_2` varchar(64) default NULL,
`city` varchar(32) default NULL,
`state` varchar(32) default NULL,
`postal_code` varchar(32) default NULL,
`country` varchar(64) default NULL,
`website` varchar(255) default NULL,
`im1_id` int(11) NOT NULL default '0',
`im2_id` int(11) NOT NULL default '0',
`removed_on` datetime default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `Ref_80` (`im1_id`),
KEY `Ref_81` (`im2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=205847 ;
--
-- Constraints for table `contact_info`
--
ALTER TABLE `contact_info`
ADD CONSTRAINT `Ref_80` FOREIGN KEY (`im1_id`) REFERENCES `im` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `Ref_81` FOREIGN KEY (`im2_id`) REFERENCES `im` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
Do I need to drop that constraint? I'm fairly inexperienced with foreign key constraints and don't really know what it's doing. I definitely need to be able to create contact_info records with only one IM screen name.
