On Wed, 2006-07-26 at 08:58 -0400, Jesse wrote: > I am trying to add a foreign key to one of my tables. When I execute the > following SQL Code: > > ALTER TABLE `bpa`.`confinvitems` ADD CONSTRAINT `FK_confinvitems_1` FOREIGN > KEY `FK_confinvitems_1` (`InvDetID`) > REFERENCES `confinvdet` (`ID`) > ON DELETE CASCADE; > > I get the error: > > MySQL Error Number 1452 > Cannot add or update a child row: a foreign key constraint fails > (`bpa/#sql-162c_1b`, CONSTRAINT `FK_confinvitems_1` FOREIGN KEY (`InvDetID`) > REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE) > > I have checked, and all the indexes seem to be in place,
By that I hope you mean there is an index on confinvitems.InvDetID _and_ confinvdet.ID > the data types are > exactly the same. There are no duplicate ID's in the ConfInvDet table. Any > idea what this error means, and how to fix it? Could be a record in confinvitems that has an InvDetID that doesn't exist in ConfInvDet. Check with something like: SELECT InvDetID FROM confinvitems WHERE InvDetID NOT IN (SELECT ID FROM ConfInvDet); Also if you do a SHOW INNODB STATUS after your failed query you can get more details on the last error. The InnoDB fkey errors reported back tends to be a bit vague, covering all sorts of failures. Looking at the text and sql examples it could be a table name case problem i.e. you refer to `ConfInvDet` in text but `confinvdet` in SQL. Are you on windows (case insensative table names) or a *nix machine (case sensative)? hth, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
