Upgrade a Database table to add a new column

Question

Hi,

I need to convert an existing db table to a new db table which adds a new column. I have the following sql code which set the default value of this column to be -1.

// Insert new column for holding appWidgetIds
db.execSQL("ALTER TABLE favorites " +
   "ADD COLUMN appWidgetId INTEGER NOT NULL DEFAULT -1;");

But how can i set the default value of the column to increment one by one for each row?

Answer 1

This depends on which DBMS you're using. For MySQL, you can use:

ALTER TABLE x ADD COLUMN y INTEGER NOT NULL DEFAULT -1 AUTO_INCREMENT

See http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Answer 2

Are values auto-incremented for new rows AFTER your conversion too? If not, you could do 3 steps:

1. ALTER the table with NULLable column
2. UPDATE the new column with whatever rules you have
3. ALTER again to set NOT NULL.

Answer 3

It depends on your DBMS. MySQL uses AUTO_INCREMENT:

ALTER TABLE favorites ADD COLUMN appWidgetId INTEGER
  NOT NULL DEFAULT -1 AUTO_INCREMENT

PostgreSQL uses CREATE SEQUENCE:

CREATE SEQUENCE favorites_seq;
ALTER TABLE favorites ADD COLUMN appWidgetId INTEGER
  NOT NULL DEFAULT -1 nextval('favorites_seq')

SQL Server uses IDENTITY:

ALTER TABLE favorites ADD COLUMN appWidgetId INTEGER
  IDENTITY(1,1) NOT NULL DEFAULT -1

As a side note, I don't know why you're setting your default as -1. Why would you make your integers signed if they will always be positive? You should make the default 0 for efficiency.