Changing int to bigint difficulty

Databases
1

Hi,

I have an ‘id’ column with the type set to INT. I want to change this to BIGINT but there are numerous constraints already being applied.

is it possible to change the data type without removing and then re-setting the constraints?

Or have I learned another lesson in db modelling?

bazz

2

From my Oracle days I had to disable constraints (without actually deleting them) and then re-enable them.

You should be able to disable constraints in MySQL as well but I’m not sure as I have never had to up untill now.

3

apparently not yet :slight_smile:

an “id” column is typically used for a surrogate key, and almost invariably, it is an auto_increment

i have yet to hear a good explanation of why the switch needs to be made to BIGINT when there are 2 to 4 billion numbers available (depending on whether you use UNSIGNED, which you should)

how close are you to reaching even 1 billion rows in the table?

4

I know that both are normally auto_increment surrogate PKs. :slight_smile:

I want to know the process which I must follow, if I ever want to change to BIGINT from INT. I am not yet near haveing exhausted the range of INT but if I ever got close, I don’t want to run out of time learning where in doing so, I exceed the INT range.

Is it possible to change from INT to BIGINT wihtout having to reset each constraint manually?

I found this in the docs but I don’t know what the ‘do your processing’ part should include although it has to be the command to change the col data type to BIG INT.


ALTER TABLE yourtablename DISABLE KEYS;
SET FOREIGN_KEY_CHECKS=0;
... do your processing ...
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE yourtablename ENABLE KEYS;

Bazz

5

“do your processing” would be whatever you want to do that’s gonna mess with the keys

in your case, an ALTER TABLE to change the column

6

Ah, so that would be…


ALTER TABLE yourtablename DISABLE KEYS;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE businesses MODIFY id BIGINT;
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE yourtablename ENABLE KEYS;

which is meant to changethe column id, in table businesses to BIGINT.

If you were able to confirm that, I shall read up on using UNSIGNED. I want the auto_increment to being from 1 but maybe that is erroneous since -1 is just as unique?

bazz

7

Ah, I remember now…

All integer types can have an optional (nonstandard) attribute UNSIGNED

I try to use standrd sql wherever possible and so I didn’t specify the signed/unsigned part so it defaults to signed. dat right?

So if I retain the standardisation of sql, by using onyl the positive digits of the permmitted range, I may run outside that range sooner? and so I may need to use BIGINT sooner than anticipated (if at all).

Is this correct so far?

bazz

8

what happened when you tested it? :wink:

as for the auto_increment, INTEGER starts at 1 and goes to 2 billion, INTEGER UNSIGNED starts at 1 and goes to 4 billion

9

uh-oh. my understanding of the docs is that int runs from

INT 4 -2147483648 2147483647

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Is it perhaps the auto_increment setting that forces it to start from 1 instead of -2147483648?

10

yes :slight_smile:

you might also have asked the same question in a slightly different way –

i’ve never seen a table with an auto_increment id where the first row gets an id value of -2147483648 … is that because of the auto_increment?