Altering a table primary key which has foreign key constrains in mysql

Question

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.

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;

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.

ALTER TABLE ohrm_emp_education
ADD column id int not null AUTO_INCREMENT,
DROP PRIMARY KEY,
ADD primary key (id)

But it shows following error

#1025 - Error on rename of './test/#sql-4f6_19b' to './test/ohrm_emp_education' (errno: 150)

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.

Oleg · Accepted Answer · 2012-03-01T05:25:15.980

3

Try to delete foreign keys first, something like this:

ALTER TABLE `ohrm_emp_education` DROP FOREIGN KEY `emp_number`;
ALTER TABLE `ohrm_emp_education` DROP FOREIGN KEY `education_id`;

And then alter table.

edited Mar 01 '12 at 05:25

answered Mar 01 '12 at 05:19

Oleg

2,733
7
39
62

score 3 · Answer 2 · answered Mar 01 '12 at 05:25

3

If you are using SQL Server Management Studio.
Then right click on the table and click design. Then right click on the row which contains the composite key and click on remove primary key then add new column and insert data into that column and check that the column has no empty data.

Then again go to design view and right click on the required column and click on Set as Primary Key

You're Done!!

answered Mar 01 '12 at 05:25

Krishna Thota

6,646
14
54
79

Thanks for the quick reply. I'm searching for a way we can do it using pure sql commands, not in ui. I manged to get the answer by dropping the foreign keys first. – Thilanka Mar 01 '12 at 08:35

score 1 · Answer 3 · answered Mar 01 '12 at 05:22

1

You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.

answered Mar 01 '12 at 05:22

Vishwanath Dalvi

35,388
41
123
155

score 1 · Answer 4 · answered Mar 01 '12 at 05:22

1

drop/disable foreign key constraint. Drop primary key, add new PK, enable/add fk.

answered Mar 01 '12 at 05:22

elrado

4,960
1
17
15

Altering a table primary key which has foreign key constrains in mysql

4 Answers4