Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify the default but can't seem to find how to do it.

Is it possible?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

SQLite only implements a very limited subset of ALTER TABLE statements. You'll have to drop and recreate the table, preferably in one transaction:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE tbl_tmp(col1, col2);
INSERT INTO tbl_tmp SELECT col1, col2 FROM tbl;
DROP TABLE tbl;
CREATE TABLE tbl(col1 integer, col2 text DEFAULT 'foo'); -- with new default
INSERT INTO tbl SELECT col1, col2 FROM tbl_tmp;
DROP TABLE tbl_tmp;
COMMIT;
share|improve this answer
    
Thanks heaps, Erwin. Answers my question and solves my problem. Much appreciated. –  Cannis Lupus Jul 4 '12 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.