Field Primary key from int to bigint

Asked

Viewed 1,222 times

4

Well I need to turn my primarykay fields from int to bigint, but it is giving a problem. Follow the code below:

Executing SQL script in server
ERROR: Error 1025: Error on rename of './DB_ATOS542/#sql-b4b_210' to './DB_ATOS542/tbl_PESSOAS' (errno: 150)
SQL Code:
        ALTER TABLE `DB_ATOS542`.`tbl_PESSOAS` 
        CHANGE COLUMN `COD_IDENT_PESSO` `COD_IDENT_PESSO` BIGINT NOT NULL 

SQL script execution finished: statements: 3 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch
mysql sql

  • this table has reference in other tables? because every FK that points to this field you will have to take the constrains to be able to change.

  • What is the right attitude to do ?

  • Do you need to EVEN go to bigint? everything depends on the depth of the data, if you have mtas tables referenced is more complicated, if you don’t have, create a new column bigint and delete the old one. everything depends on the case. more information.

  • Really my table is linked in several other, however I’m doing everything by Workbench

1 answer

1


The problem there is that you are trying to change the type of a Primary key field that has Foreign Keys referencing it, this is not allowed as a Foreign key field always has to have the same type of field to which it refers, example:

CREATE TABLE bla(id INT PRIMARY KEY AUTO_INCREMENT);

in this case you can change the type of the id field to BIGINT without problems, even having data. Now let’s say I added a table that has a Foreign key that references my "bla id field"

CREATE TABLE bla2(id INT PRIMARY KEY AUTO_INCREMENT, fk_bla INT, FOREIGN KEY (fk_bla) REFERENCES bla(id));

and then try to convert the id field to BIGINT

ALTER TABLE bla MODIFY id BIGINT;
ERROR 1025 (HY000): Error on rename of './test/#sql-47c_2' to './test/bla' (errno: 150)

As I wrote above, this is not allowed as both PK and FK reference to PK need to maintain the same type. To allow this change what you must do is:

1) Remove the FK constraints that reference the column you want to change

2) carry out the type change in the desired column and also in the FK columns (whose Constraint you temporarily removed)

3) recreate the FOREIGN KEY constraints

following the example

-- removendo a FK que referência bla.id
ALTER TABLE bla2 DROP FOREIGN KEY bla2_ibfk_1; 
-- "bla2_ibfk_1" é o nome da FK constraint, nesse caso é algo gerado automaticamente pelo mysql

-- alterando os tipos de INT para BIGINT
ALTER TABLE bla MODIFY id BIGINT;
ALTER TABLE bla2 MODIFY fk_bla BIGINT;

-- finalmente recriando a FK constraint
ALTER TABLE bla2 ADD CONSTRAINT bla2_fk_bla FOREIGN KEY fk_bla (fk_bla) REFERENCES bla(id);

As you can see the process is a bit laborious ;)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.