Altering SQL column type and adding PK constraint

Question

How to change the type of a column in a SQL table?

I've got:

    CREATE TABLE table(
        id INTEGER,
        salt TEXT NOT NULL UNIQUE,
        step INT,
        insert_date TIMESTAMP
    );

I'd like to change salt's type to just TEXT and id's type to INTEGER PRIMARY KEY.

UPD: I'm using SQLite.

Answer 1

In Oracle:

ALTER TABLE table MODIFY salt TEXT NULL
ALTER TABLE table MODIFY id INT NOT NULL
ALTER TABLE table ADD CONSTRAINT pk_table_id PRIMARY KEY (id)

Answer 2

As a side note if there is already data within that table you could get data conversion issues when changing the types of columns.

Just something to consider.

Answer 3

Assuming it is MSSQL:

-- // 0. assuming yout table does not have PK already:
-- // 0.1. make it not nullable
ALTER TABLE "table" ALTER COLUMN "id" INTEGER NOT NULL
GO
-- // 0.? I would just add a PK contraint so that you can control its name as well
ALTER TABLE "table" ADD CONSTRAINT "table_PK" PRIMARY KEY ("id")
GO

-- // 1. first you need to find out the name of that 
-- // unique contraint that you have and delete it
-- // 1.1. now change the type
ALTER TABLE "table" ALTER COLUMN "salt" TEXT
GO
-- // 1.2.? add contraint again if you care

Answer 4

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>

Answer 5

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.