Altering SQLite column type and adding PK constraint

Below is an excerpt from the SQLite manual discussing the ALTER TABLE command (see URL: SQLite Alter Table):

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a colum, remove a column, or add or remove constraints from a table.

As the manual states, it is not possible to modify a column's type or constraints, such as converting NULL to NOT NULL. However, there is a work around by

1. copying the old table to a temporary table,
2. creating a new table defined as desired, and
3. copying the data from the temporary table to the new table.

To give credit where credit is due, I learned this from the discussion on Issue #1 of hakanw's django-email-usernames project on bitbucket.org.

CREATE TABLE test_table(    id INTEGER,    salt TEXT NOT NULL UNIQUE,    step INT,    insert_date TIMESTAMP);ALTER TABLE test_table RENAME TO test_table_temp;CREATE TABLE test_table(    id INTEGER PRIMARY KEY,    salt TEXT,    step INT,    insert_date TIMESTAMP);INSERT INTO test_table SELECT * FROM test_table_temp;DROP TABLE test_table_temp;

Notes

1. I used the table name test_table since SQLite will generate an error if you try to name a table as table.
2. The INSERT INTO command will fail if your data does not conform to the new table constraints. For instance, if the original test_table contains two id fields with the same integer, you will receive an "SQL error: PRIMARY KEY must be unique" when you execute the "INSERT INTO test_table SELECT * FROM test_table_temp;" command.
3. For all testing, I used SQLite version 3.4.0 as included as part of Python 2.6.2 running on my 13" Unibody MacBook with Mac OS X 10.5.7.

Since RDBMS is not specified, these are DB2 queries:

1. Make ID as primary key:

   ALTER TABLE table    ADD CONSTRAINT pk_id    PRIMARY KEY (id)

2. Make salt as not UNIQUE:

   ALTER TABLE table    DROP UNIQUE <salt-unique-constraint-name>

3. Make salt nullable:

   ALTER TABLE table    ALTER COLUMN salt DROP NOT NULL

You will need to do a reorg after drop not null. This is to be done from the command prompt.

reorg table <tableName>

In this case you can make salt to nullable and remove unique constraint. Also If id column does not contain any null or duplicate values you can safely make it primary key using sql server management studio. below is the screen shot. hope it makes it clearer:alt text http://img265.imageshack.us/img265/7418/91573473.png

or use following sql:

alter table <TableName> modify salt text nullalter table <TableName> drop constraint <Unique Constraint Name>alter table <TableName> modify id int not nullalter table <TableName> add constraint pk<Table>d primary key (id)