How to change the type of a column in a SQLite 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.
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
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
test_table
since SQLite will generate an error if you try to name a table as table
.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.Since RDBMS is not specified, these are DB2 queries:
Make ID as primary key:
ALTER TABLE table
ADD CONSTRAINT pk_id
PRIMARY KEY (id)
Make salt as not UNIQUE:
ALTER TABLE table
DROP UNIQUE <salt-unique-constraint-name>
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With