In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint.

For a few of those, it's an imminent problem, since this optimistic optimization clashes with the fact that we'll (in some cases, very soon) need more rows for these tables than what tinyint allows.

For other tables, the problem lies in the near future, since it has been decided we'll switch providers and DB engine to a mostly compatible RDBMS, but that lacks tinyint support...

Each of these tables has as well between 2 to 6 foreign key relationships pointing to them, so not only we have to change these columns but the FK ones as well.

Could it be possible to somehow script this change in masse, changing all columns where name = id and type = tinyint (e.g. to smallint or even int) and at the same time alter all the tables with FK referencing these columns so they match as well?

I've seen this for changing all columns from one type to another; but I have yet to deal with the foreign-key situation.


This might work:

  1. (backup db just in case)
  3. Using a suitable SELECT into information_schem.columns, construct the ALTER TABLE statements.
  4. Enable FKs.

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.