StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Below is an excerpt from the SQLite manual discussing the ALTER TABLE command (see URL: <a href="http://www.sqlite.org/lang_altertable.html" rel="noreferrer">SQLite Alter Table</a>):</p> <blockquote> <p>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.</p> </blockquote> <p>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 </p> <ol> <li>copying the old table to a temporary table, </li> <li>creating a new table defined as desired, and </li> <li>copying the data from the temporary table to the new table. </li> </ol> <p>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.</p> <pre><code>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; </code></pre> <p><em>Notes</em> </p> <ol> <li>I used the table name <code>test_table</code> since SQLite will generate an error if you try to name a table as <code>table</code>.</li> <li>The <code>INSERT INTO</code> command will fail if your data does not conform to the new table constraints. For instance, if the original <code>test_table</code> contains two <code>id</code> fields with the same integer, you will receive an "<code>SQL error: PRIMARY KEY must be unique</code>" when you execute the "<code>INSERT INTO test_table SELECT * FROM test_table_temp;</code>" command.</li> <li>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.</li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload