Suppose I have a database sitedb and in that I have 20 tables and a primary key(say uid) for one table is used in other tables as dependencies.
uid is of type int with length 10. Lets say that the number of entries in the table is full and I need to increase the capacity of the uid to the length 11.
Is it right to do so? What will happen to the related tables and how will it affect the relation?
What if I change the value of uid in all the tables to length 11. Is there a tool which will allow me to check and change the value of every related uid in all the tables in the database? Updates?

  • 2
    int(10) does not mean capacity 10. It means "allocate at least 10 chars for output." – ypercubeᵀᴹ May 30 '12 at 11:53
  • 2
    int(10) unsigned means you can store numbers from 0 up to 2^32 - 1 which is a bit more than 4-billion. Is your table really full? – ypercubeᵀᴹ May 30 '12 at 12:09
  • @ypercube no it isn't ;-) actually I thought about the requirement of a million user site, and thought of to put it in a scenario; so asked this question. does it make any difference if I change it to int(11)? – Ajit S May 30 '12 at 12:20
  • No, if your column is UNSIGNED (as the int(10) suggests), you can modify it to int(11) unsigned. This has no effect on the data that is or can be stored inside the column and no effect on the Foreign Key dependencies. – ypercubeᵀᴹ May 30 '12 at 12:31
  • 2
    Read this: Numeric Type Attributes – ypercubeᵀᴹ May 30 '12 at 12:39

Modify column by name(s): you can also use any SQL Where, such as column_name in (...) or using other columns in information_schema.columns table.

select  concat('alter table ',table_schema,'.',table_name,' change column ... ;') from information_schema.columns  where column_name='r_id'

use group_concat or similar and just copy/paste.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.