MySQL Forums
Forum List  »  InnoDB

Foreign key constraint fails
Posted by: Jamie Bohr
Date: March 22, 2008 02:07PM

I've created two tables, contact and manualdata, table stub definitions are below. I am trying to set up the contact table so the site column must exist in the manualdata table. Using the information from below when I try to update one of the rows that contains "site 1" I get a "#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test/contact`, CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`site`) REFERENCES `manualdata` (`site`))" error.

I don't want an update to manualdata to update the contact table, I just want the site column in contact to only have values based on the site column in manualdata.

Am I going about this wrong?

I know that if the last row for a given site is deleted out of the manualdata table it will cause an issue, that will be a rare, very rare occasion if it ever happens at all.

Table structures:
################################################################
CREATE TABLE `contact` (
`contact` varchar(15) default NULL COMMENT 'TCS contact',
`site` varchar(15) NOT NULL default '' COMMENT 'TCS Site',
PRIMARY KEY (`site`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

--
-- Dumping data for table `contact`
--

INSERT INTO `contact` VALUES ('blah', 'site 1');

-- --------------------------------------------------------

--
-- Table structure for table `manualdata`
--

CREATE TABLE `manualdata` (
`site` varchar(15) default NULL,
KEY `site` (`site`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `manualdata`
--

INSERT INTO `manualdata` VALUES ('site 1');
INSERT INTO `manualdata` VALUES ('site 1');
INSERT INTO `manualdata` VALUES ('site 2');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contact`
--
ALTER TABLE `contact`
ADD CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`site`) REFERENCES `manualdata` (`site`);

Options: ReplyQuote


Subject
Views
Written By
Posted
Foreign key constraint fails
2427
March 22, 2008 02:07PM


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.