Primary key field from int to bigint

Question

Primary key field from int to bigint

Navigation

#1 by (1 votes)

4

Well I need to turn my primarykay fields from int to bigint, but it's giving a problem. Here is 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

asked by anonymous 02.12.2015 / 18:01

1 answer

How to identify in a shell script, which is the docker command that is running Calculation of hotel rates in MVC application

score 1 · Accepted Answer

The problem here is that you are trying to change the type of a primary key field that has foreign keys that reference it, this is not allowed because a foreign key field always has to have the same type as the field it references, 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 if it has data. Now let's say that I added a table that has a foreign key that references that my field id of "bla"

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

and then attempt 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 because both PK and FK that reference the PK need to keep the same type. To allow this change what you should do is:

1) remove the FK constraints that refer to the column you want to change

2) make the type change in the desired column and also in the FK columns (whose constraint you temporarily removed)

3) recreate the constraints FOREIGN KEY

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;)