CODEWITHSUNDEEP
mysqlforeign-keys
genespos
genespos

Reputation: 3311

Change Primary Key from BigInt to Unsigned BigInt when linked as foreign key

I have a scenario like this:

CREATE TABLE `Users` (
  `IdUser` bigint(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `MainTable` (
  `IdLite` bigint(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `LinkedTable` (
  `IdUser` bigint(20) NOT NULL,
  `IdLite` bigint(20) NOT NULL,
PRIMARY KEY (`IdUser`, `IdLite`),
FOREIGN KEY (`IdUser`) REFERENCES `Users` (`IdUser`),
FOREIGN KEY (`IdLite`) REFERENCES `MainTable` (`IdLite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm trying to change IdLite to Unsigned with a query like this:

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `MainTable` CHANGE `IdLite` 
    `IdLite` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `LinkedTable` CHANGE `IdLite` 
    `IdLite` BIGINT(20) UNSIGNED NOT NULL;
SET FOREIGN_KEY_CHECKS=1;

but I get error:

errno: 150 - Foreign key constraint is incorrectly formed

How can I solve?

Upvotes: 1

Views: 3883

Answers (1)

eggyal
eggyal

Reputation: 125835

You can't change the data type of columns used in an existing FK constraint.

You can drop the FK constraint, change the column data types and then recreate the FK constraint:

ALTER TABLE LinkedTable
  DROP FOREIGN KEY linkedtable_ibfk_2; -- or whatever the symbol is named

ALTER TABLE MainTable
  MODIFY IdLite SERIAL; -- alias of BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

ALTER TABLE LinkedTable
  MODIFY IdLite BIGINT UNSIGNED NOT NULL,
  ADD FOREIGN KEY (IdLite) REFERENCES MainTable (IdLite);

Upvotes: 4

Related Questions