CODEWITHSUNDEEP
mysqlforeign-keysforeign-key-relationship
user391986
user391986

Reputation: 30956

mysql innodb altering two tables that have foreign keys

Below is my current database structure

CREATE TABLE one (
  id           MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (id));

CREATE TABLE two (
  id           MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  one_id       MEDIUMINT UNSIGNED NOT NULL
  PRIMARY KEY (id),
  CONSTRAINT FOREIGN KEY (`one_Id`) REFERENCES `one` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

How can I run the following alter command on both tables

ALTER TABLE one MODIFY COLUMN `id` int(10) NOT NULL auto_increment;
ALTER TABLE two MODIFY COLUMN `one_id` int(10) NOT NULL;

Right now I'm getting this error

ERROR 1025 (HY000): Error on rename (errno: 150)

I've also tried to disable these but it doesn't help

 SET autocommit=0; 
 SET unique_checks=0; 
 SET foreign_key_checks=0;

Upvotes: 0

Views: 113

Answers (2)

Toddius Zho
Toddius Zho

Reputation: 624

This is straight from the a ALTER TABLE documentation for MySQL:

Before MySQL 5.6.7, using ALTER TABLE to change the definition of a foreign key column could cause a loss of referential integrity. For example, changing a foreign key column that contained NULL values to be NOT NULL caused the NULL values to be the empty string. Similarly, an ALTER TABLE IGNORE that removed rows in a parent table could break referential integrity.

As of 5.6.7, the server prohibits changes to foreign key columns with the potential to cause loss of referential integrity. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward.

This makes the other answer correct. The SET foreign_key_checks=0; statement and its brethren will disable constraints, but will not let you modify a table such that an existing constraint can no longer even exist.

Upvotes: 0

Erik Lukiman
Erik Lukiman

Reputation: 337

Assuming your constraint name in table two is two_ibfk_1 , you can see the name of the constraint with this command :
SHOW CREATE TABLE two;

so the command would be to delete the constraint first, and then recreate it after it was modified

ALTER TABLE two DROP FOREIGN KEY two_ibfk_1;
ALTER TABLE one MODIFY COLUMN `id` int(10) NOT NULL auto_increment;
ALTER TABLE two MODIFY COLUMN `one_id` int(10) NOT NULL ;
ALTER TABLE two ADD CONSTRAINT FOREIGN KEY (`one_Id`) REFERENCES `one` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

FYI, the second command to modify table two can not be set as auto_increment, because the primary is is column id

Upvotes: 1

Related Questions