Learning SQL - Changing Table Definitions - Part 4 - Summary

This section is specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

A summary

After all that waffle a summary of what we have covered in the last three lessons:

Simple add Column

Adding a column to a database is simple ( as long as it is not a primary key, is not a unique column and you add a default value for not nulls - see Part 1).

alter table table_name add column datatype

You will also have to identify and recreate any views accessing the original column name

Simple table rename

Changing a table name that does not have Triggers or indexes is simple:

alter table original_table_name rename to new_table_name

You will have to identify and recreate any views accessing the original table name

Complex add/drop column, change table name

Removing a column, changing the table name (where indexes or triggers exist), adding primary or unique keys is not simple:

1) Create a temporary copy table - Column definitons can be 'looped' in a a program, Foreign Keys are more difficult!:

Query sqlite_master to find column names and defintions

Query sqlite_sequnce (if itexists) to identify AutoIncrement Primary Keys


Query sqlite_master to find Foreign Key definitions


Build Create Statement

Build Insert Statement

2) Get the index names from the original table using:

pragma index_list ( table_name )

3) Get the index columns from the original table using:

pragma index_info ( index_name )

4) Recreate the indexes on the temporay table

5) Get the Triggers using :

select sql from sqlite_master
where tbl_name like 'Table_name%'
and type = 'trigger'

6) Build an new Trigger creation Statement from Point 5 info, with new Table name - Don't run it yet

7) Get the SQL for ALL Views accessing the original table - painful if you don't know how many! Don't recreate them yet!

select sql from sqlite_master where type = 'view' and name = 'View_Name'

8) Drop the original table

drop table original_table;

9) Rename the temproary table to the required new name

alter table original_table_name rename to new_table_name

10) Run the Trigger Creation Statement from point 6.

11) Drop any views that access the original table name then....

12) Re-write the views (to access the new table ) and re-execute the View creation statements