Editing SQLite Cookbook (section)

== Defining and altering tables ==

=== Renaming or deleting a column from a SQLite table ===

* SQLite supports `ALTER TABLE [table_name] ADD COLUMN ([column_name] [column_properties])`.
* SQLite does not support `UPDATE COLUMN` or `DROP COLUMN`.
<syntaxhighlight lang="sql" enclose="div">
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
</syntaxhighlight>

=== Autoincrement primary key ===

Create a primary key with

<syntaxhighlight lang="sql">
CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    val TEXT
);
</syntaxhighlight>

Insert a new row in the table, placing a value one greater than the highest existing primary key in the `id` column, by assigning `NULL` to the `id` column <ref>[http://www.sqlite.org/faq.html#q1|SQLite Frequently Asked Questions]&mdash;sqlite.org</ref>: 

<syntaxhighlight lang="sql">
INSERT INTO my_table (id, val) VALUES (NULL, 'some value');
</syntaxhighlight>

=== `ON DELETE CASCADE` ===

Sqlite3 turns off foreign key support by default. With foreign key support off even when a table definition specifies `ON DELETE CASCADE` the rows in the foreign key tables will not be deleted.<ref>[https://stackoverflow.com/questions/15301643/sqlite3-forgets-to-use-foreign-keys SQLite3 forgets to use foreign keys]&mdash;StackOverflow</ref>

Before each command where foreign key support is needed it's necessary to issue the following command: `PRAGMA foreign_keys = ON;`

==== Turning on FK support by default ====

To turn on foreign key support by default, add the following line to the sqlite configuration file, `~/.sqliterc`:

<pre>
PRAGMA foreign_keys = ON;
</pre>

==== pyCharm ====

* Right click on the database connection > '''Properties'''
* '''Advanced''' tab > `foreign_keys` option: `true`

''N.B. I tried this with no effect, e.g. tested some code that deleted a record with FKs that were not deleted along with it.''