Help creating foreign keys

[Darkmatter5]

Here are the three tables I have.

 

counties

CREATE TABLE `counties` (
  `CountyID` int(11) NOT NULL auto_increment,
  `County` varchar(50) default NULL,
  PRIMARY KEY  (`CountyID`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

jobs

CREATE TABLE `jobs` (
  `JobID` int(11) NOT NULL auto_increment,
  `JobNumber` varchar(20) default NULL,
  `Client` int(11) default NULL,
  `County` int(11) default NULL,
  `JobDesc` longtext,
  `JobLoc` longtext,
  `Surv1` int(11) default NULL,
  `Surv2` int(11) default NULL,
  `Surv3` int(11) default NULL,
  `Sudb` int(11) default NULL,
  `Sect` varchar(50) default NULL,
  `LotBlk` varchar( default NULL,
  `FBPg` varchar(9) default NULL,
  `FndDate` date default NULL,
  `Acerage` varchar(10) default NULL,
  `Type1` int(11) default NULL,
  `Type2` int(11) default NULL,
  `Type3` int(11) default NULL,
  `MemoInfo` longtext,
  `Surveyor` int(11) default NULL,
  `VolPg` varchar(9) default NULL,
  `Estimate` double default NULL,
  `Amt` double default NULL,
  `AssignDate` date default NULL,
  `CompDate` date default NULL,
  PRIMARY KEY  (`JobID`),
  KEY `fk_client` (`Client`),
  KEY `fk_surv1` (`Surv1`),
  KEY `fk_surv2` (`Surv2`),
  KEY `fk_surv3` (`Surv3`),
  KEY `fk_subd` (`Sudb`),
  KEY `fk_type1` (`Type1`),
  KEY `fk_type2` (`Type2`),
  KEY `fk_type3` (`Type3`),
  KEY `fk_surveyor` (`Surveyor`),
  CONSTRAINT `fk_client` FOREIGN KEY (`Client`) REFERENCES `clients` (`ClientID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_subd` FOREIGN KEY (`Sudb`) REFERENCES `subdivisions` (`SubdivisionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_surv1` FOREIGN KEY (`Surv1`) REFERENCES `surveys` (`SurveyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_surv2` FOREIGN KEY (`Surv2`) REFERENCES `surveys` (`SurveyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_surv3` FOREIGN KEY (`Surv3`) REFERENCES `surveys` (`SurveyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_surveyor` FOREIGN KEY (`Surveyor`) REFERENCES `surveyors` (`SurveyorID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_type1` FOREIGN KEY (`Type1`) REFERENCES `types` (`TypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_type2` FOREIGN KEY (`Type2`) REFERENCES `types` (`TypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_type3` FOREIGN KEY (`Type3`) REFERENCES `types` (`TypeID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5702 DEFAULT CHARSET=latin1;

surveys

CREATE TABLE `surveys` (
  `SurveyID` int(11) NOT NULL auto_increment,
  `Survey` varchar(50) default NULL,
  `County` int(11) default '0',
  PRIMARY KEY  (`SurveyID`)
) ENGINE=InnoDB AUTO_INCREMENT=4856 DEFAULT CHARSET=latin1;

 

I need to create a foreign key between CountyID in counties and County in jobs and surveys.  Can someone tell me the command I'd use to create these two foreign keys?  I've tried creating them with Navicat, but they aren't taking and are generating an error 1452.  As you can see from the table data, I've created numerous other foreign keys, but the these two just aren't working.  Can anyone help?

[Barand]

Have you considered normalizing your data?

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[Darkmatter5]

Okay I've gone through and "normalized" my database and completely rebuilt it.  But I still can't get this one relationship to build.  I'm thinking since county is already referenced to surveys through county_id that adding this new relationship between jobs.county_id and counties.county_id won't work.  How should I go about referencing counties.county_id to jobs_county_id and surveys_county_id?  Here's the code of my new tables.

 

counties - table

CREATE TABLE `counties` (
  `county_id` int(11) NOT NULL auto_increment,
  `county` varchar(50) NOT NULL,
  PRIMARY KEY  (`county_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

surveys - table

CREATE TABLE `surveys` (
  `survey_id` int(11) NOT NULL auto_increment,
  `survey` varchar(50) NOT NULL,
  `county_id` int(11) NOT NULL,
  PRIMARY KEY  (`survey_id`),
  KEY `fk_county_id` (`county_id`),
  CONSTRAINT `fk_county_id` FOREIGN KEY (`county_id`) REFERENCES `counties` (`county_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

jobs - table

CREATE TABLE `jobs` (
  `job_id` int(11) NOT NULL auto_increment,
  `job_number` varchar(20) NOT NULL,
  `client_id` int(11) NOT NULL,
  `county_id` int(11) default NULL,
  `job_desc` longtext,
  `job_loc` longtext,
  `survey_id_1` int(11) default NULL,
  `survey_id_2` int(11) default NULL,
  `survey_id_3` int(11) default NULL,
  `subdivision_id` int(11) default NULL,
  `section` varchar(50) default NULL,
  `lot_blk` varchar( default NULL,
  `fb_pg` varchar(9) default NULL,
  `fnd_date` date default NULL,
  `acerage` varchar(10) default NULL,
  `type_id_1` int(11) default NULL,
  `type_id_2` int(11) default NULL,
  `type_id_3` int(11) default NULL,
  `memo_info` longtext,
  `employee_id` int(11) default NULL,
  `vol_pg` varchar(9) default NULL,
  `estimate` double default NULL,
  `amount` double default NULL,
  `assign_date` date default NULL,
  `completion_date` date default NULL,
  PRIMARY KEY  (`job_id`),
  KEY `fk_client_id` (`client_id`),
  KEY `fk_survey_id_1` (`survey_id_1`),
  KEY `fk_survey_id_2` (`survey_id_2`),
  KEY `fk_survey_id_3` (`survey_id_3`),
  KEY `fk_subdivision_id` (`subdivision_id`),
  KEY `fk_type_id_1` (`type_id_1`),
  KEY `fk_type_id_2` (`type_id_2`),
  KEY `fk_type_id_3` (`type_id_3`),
  KEY `fk_employee_id` (`employee_id`),
  CONSTRAINT `fk_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_subdivision_id` FOREIGN KEY (`subdivision_id`) REFERENCES `subdivisions` (`subdivision_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_survey_id_1` FOREIGN KEY (`survey_id_1`) REFERENCES `surveys` (`survey_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_survey_id_2` FOREIGN KEY (`survey_id_2`) REFERENCES `surveys` (`survey_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_survey_id_3` FOREIGN KEY (`survey_id_3`) REFERENCES `surveys` (`survey_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_type_id_1` FOREIGN KEY (`type_id_1`) REFERENCES `types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_type_id_2` FOREIGN KEY (`type_id_2`) REFERENCES `types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_type_id_3` FOREIGN KEY (`type_id_3`) REFERENCES `types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Am I doing anything wrong with how my tables are constructed?  Am I using improper practices in MySQL or something?  Can anyone please help?!?!