title: Altering a table primary key which has foreign key constrains in mysql
body: <p>I have a mysql database which have set of tables One table have a composite key as the primary key and and a single foreign key. Following are the table definitions. </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `ohrm_emp_education` (
    `emp_number` int(11) NOT NULL,
    `education_id` int(11) NOT NULL,
    `institute` varchar(100) DEFAULT NULL,
    `major` varchar(100) DEFAULT NULL,
    `year` decimal(4,0) DEFAULT NULL,
    `score` varchar(25) DEFAULT NULL,
    `start_date` date DEFAULT NULL,
    `end_date` date DEFAULT NULL,
    PRIMARY KEY (`emp_number`,`education_id`),
    KEY `education_id` (`education_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
  
  ALTER TABLE `ohrm_emp_education`
  ADD CONSTRAINT `ohrm_emp_education_ibfk_1` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
  ADD CONSTRAINT `ohrm_emp_education_ibfk_2` FOREIGN KEY (`education_id`) REFERENCES `ohrm_education` (`id`) ON DELETE CASCADE;
  </code></pre>
  
  <p>But now I need to add a new column to this existing table and make it as the primary key. I tried it with the following query. </p>
  
  <pre><code>ALTER TABLE ohrm_emp_education
  ADD column id int not null AUTO_INCREMENT,
  DROP PRIMARY KEY,
  ADD primary key (id) 
  </code></pre>
  
  <p>But it shows following error</p>
  
  <pre><code>#1025 - Error on rename of './test/#sql-4f6_19b' to './test/ohrm_emp_education' (errno: 150)
  </code></pre>
  
  <p>I tried with several answers which are found on the internet but couldn't solve it properly.  Can someone help me on this. Thanks in advance. </p>