3

I have tables named add_disease and specificity_of_gender. I want to set the primary key of specificity_of_gender table as foreign key in add_disease table. My codes are like below:

--
-- Table structure for table `add_disease`
--
CREATE TABLE IF NOT EXISTS `add_disease` (
  `Disease_Id` int(100) NOT NULL AUTO_INCREMENT,
  `Disease_Name` varchar(100) NOT NULL,
  `Gender_Id` int(100) NOT NULL,
  `Age_Id` int(100) NOT NULL,
  `Notion_Id` int(100) NOT NULL,
  `Type_Id` int(100) NOT NULL,
  `Stage_Id` int(100) NOT NULL,
  `Scope_Id` int(100) NOT NULL,
  `Symptoms` text NOT NULL,
  `Description` text NOT NULL,
  `Image` int(100) NOT NULL,
  PRIMARY KEY (`Disease_Id`),
KEY ` Gender_Id ` (`Gender_Id `),
KEY ` Age_Id ` (`Age_Id `),
KEY ` Notion_Id ` (`Notion_Id `),
KEY ` Type_Id ` (`Type_Id `),
KEY ` Stage_Id ` (`Stage_Id `),
KEY ` Scope_Id ` (`Scope_Id `)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `add_disease`
--**

And

--
-- Table structure for table `specificity_of_gender`
--

CREATE TABLE IF NOT EXISTS `specificity_of_gender` (
  `Gender_Id` int(100) NOT NULL AUTO_INCREMENT,
  `Gender_Name` varchar(100) NOT NULL,
  PRIMARY KEY (`Gender_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `specificity_of_gender`
--

INSERT INTO `specificity_of_gender` (`Gender_Id`, `Gender_Name`) VALUES
(**1, 'Male'),
(2, 'Female'),
(3, 'Others');

And MySQL says

Error

SQL query:
--
-- Database: `online_medical_service`
--
-- --------------------------------------------------------
--
-- Table structure for table `add_disease`
--
CREATE TABLE IF NOT EXISTS `add_disease` (
`Disease_Id` int( 100 ) NOT NULL AUTO_INCREMENT ,
`Disease_Name` varchar( 100 ) NOT NULL ,
`Gender_Id` int( 100 ) NOT NULL ,
`Age_Id` int( 100 ) NOT NULL ,
`Notion_Id` int( 100 ) NOT NULL ,
`Type_Id` int( 100 ) NOT NULL ,
`Stage_Id` int( 100 ) NOT NULL ,
`Scope_Id` int( 100 ) NOT NULL ,
`Symptoms` text NOT NULL ,
`Description` text NOT NULL ,
`Image` int( 100 ) NOT NULL ,
PRIMARY KEY ( `Disease_Id` ) ,
KEY ` Gender_Id ` ( `Gender_Id ` ) ,
KEY ` Age_Id ` ( `Age_Id ` ) ,
KEY ` Notion_Id ` ( `Notion_Id ` ) ,
KEY ` Type_Id ` ( `Type_Id ` ) ,
KEY ` Stage_Id ` ( `Stage_Id ` ) ,
KEY ` Scope_Id ` ( `Scope_Id ` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;

MySQL said:

#1072 - Key column 'Gender_Id ' doesn't exist in table

Please can anyone tell me what to do?? please help me with this... :(

John Woo
  • 258,903
  • 69
  • 498
  • 492
Finest ITgirl
  • 33
  • 2
  • In the future, please enclose your code in code sample blocks (I submitted an edit doing this already.) It greatly enhances the readability of your question so that other users can better help you with your issue. – Paul O. Jan 16 '13 at 15:38

1 Answers1

3

the problem is that you have extra spaces on the column name and index name when you define an index,

PRIMARY KEY (`Disease_Id`),
KEY `bGender_Idb` (`Gender_Id `),
KEY ` Age_Id ` (`Age_Id `),
KEY ` Notion_Id ` (`Notion_Id `),
KEY ` Type_Id ` (`Type_Id `),
KEY ` Stage_Id ` (`Stage_Id `),
KEY ` Scope_Id ` (`Scope_Id `)

so basically,

`Gender_Id ` is not equal to `Gender_Id`
          ^ see extra spaces from here

and you did that to all your columns: Gender_Id, Age_Id, etc..

You should remove the extra trailing spaces and it will work.

Below is a link of the fixed DDL

John Woo
  • 258,903
  • 69
  • 498
  • 492