Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
1  
int(10) does not mean capacity 10. It means "allocate at least 10 chars for output." – ypercube May 30 '12 at 11:53
1  
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)? – develkar 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
show 3 more comments

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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