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