FK problem
hi
I HAVE TWO TABLES
--
-- Table structure for table `healthgene2`.`submissions`
--
CREATE TABLE `client` (
`Client_ID` int(10) unsigned NOT NULL auto_increment,
`Fname` varchar(40) default NULL,
`Lname` varchar(40) default NULL,
`Company` varchar(80) default NULL,
`Address` varchar(255) NOT NULL default '',
`City` varchar(50) NOT NULL default '',
`Province` varchar(20) default NULL,
`ZIP` varchar(10) NOT NULL default '',
`Country` varchar(40) NOT NULL default '',
`Phone` varchar(18) default NULL,
`Fax` varchar(18) default NULL,
`Email` varchar(50) default NULL,
`Notes` varchar(255) default NULL,
`CreatDate` date default NULL,
`Active` enum('F','T') NOT NULL default 'T',
`CreditID` varchar(7) NOT NULL default '',
`ClientCode` varchar(12) NOT NULL default '',
PRIMARY KEY (`Client_ID`),
KEY `FK_CreditID` (`CreditID`),
CONSTRAINT `FK_CreditID` FOREIGN KEY (`CreditID`) REFERENCES `credit_groups` (`CreditID`) ON delete cascade ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `submissions` (
`Subm_ID` int(10) unsigned NOT NULL auto_increment,
`SubmDate` date NOT NULL default '0000-00-00',
`RepDate` datetime NOT NULL default '0000-00-00 00:00:00',
`Client_ID` int(10) unsigned NOT NULL,
`ShipmentID` varchar(7) NOT NULL default '1-No',
`ShipmentExtID` varchar(5) NOT NULL default '1-Reg',
`ShipmentPrice` decimal(8,2) NOT NULL default '0.00',
`TaxPercent` float unsigned NOT NULL default '0',
`FuelChargePercent` float unsigned NOT NULL default '0',
`Submit_by` varchar(150) default NULL,
`SubmBalance` decimal(8,2) NOT NULL default '0.00',
`SubmNote` varchar(255) NOT NULL default '',
`SubmOK` enum('Y','N','D','C','ErSubm') NOT NULL default 'N',
`user_id` int(10) unsigned NOT NULL,
`InvoiceID` varchar(11) NOT NULL,
PRIMARY KEY (`Subm_ID`),
-- KEY `FK_Client_ID` (`Client_ID`),
KEY `FK_ShipmentID` (`ShipmentID`),
KEY `FK_ShipmentExtID` (`ShipmentExtID`),
KEY `FK_user_id` (`user_id`),
KEY `FK_InvoiceID` (`InvoiceID`),
-- CONSTRAINT `FK_Client_ID` FOREIGN KEY (`Client_ID`) REFERENCES `client` (`Client_ID`) ON delete cascade ON UPDATE CASCADE,
CONSTRAINT `FK_ShipmentID` FOREIGN KEY (`ShipmentID`) REFERENCES `shipment` (`ShipmentID`)ON delete cascade ON UPDATE CASCADE,
CONSTRAINT `FK_ShipmentExtID` FOREIGN KEY (`ShipmentExtID`) REFERENCES `shipment_ext` (`ShipmentExtID`) ON delete cascade ON UPDATE CASCADE,
CONSTRAINT `FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON delete cascade ON UPDATE CASCADE,
CONSTRAINT `FK_InvoiceID` FOREIGN KEY (`InvoiceID`) REFERENCES `invoices` (`InvoiceID`) ON delete cascade ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SO I AM TRYING TO GET DATA FROM A PREVIOUS DATABASE INTO THIS ONE USING LOAD DATA FROM OUTFILE.
LOAD DATA INFILE 'C:/Program Files/MySQL/MySQL Server 4.1/data/databaseBackup/submissions.txt'
INTO TABLE healthgene2.submissions
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
(`Subm_ID`, `SubmDate`, `RepDate`, `Client_Id`, `ShipmentId`, `ShipmentExtId`, `ShipmentPrice`, `TaxPercent`, `Submit_by`, `SubmBalance`, `SubmNote`, `SubmOK`, `user_id`, `InvoiceID`);
AS YOU CAN SEE ABOVE IN THE CREATE TABLE THE FK IS COMMENTED OUT BECAUSE I GET A CANNOT UPDATE FOREIGN KEY CONSTRAINT ERROR BECAUSE PARENT DOESN'T EXIST.
AT FIRST I THOUGHT IT WAS INSERTING INTO THE TABLE WRONG( IE. WRONG FIELD ORDER)
HOWEVER, SINCE THE FK IS COMMENTED OUT THE LOAD DATA INFILE WORKS.
WHEN I VIEW THE CONTENTS OF THE TABLE ALL OF THE DATA IS THERE INCLUDING THE CLIENT_ID. WHEN I LOOK UP THE CLIENT ID IN THE CLIENT TABLE IT EXISTS.
WHEN I UNCOMMENT THE FK I GET NOTHING IN THE SUBMISSION TABLE, WHICH USUALLY MEANS THAT THE VERY FIRST ROW HAD AN ERROR.
HAS SOMEONE SEEN THIS TYPE OF ERROR BEFORE?