CODEWITHSUNDEEP
mysqlsqlalter-tablemysql-error-1025
Thilanka
Thilanka

Reputation: 1763

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

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.

Upvotes: 2

Views: 5074

Answers (4)

Krishna Thota
Krishna Thota

Reputation: 7066

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!!

Upvotes: 3

Oleg
Oleg

Reputation: 2821

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.

Upvotes: 3

elrado
elrado

Reputation: 5282

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

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

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.

Upvotes: 1

Related Questions