--Other SQL Lessons Sections-- What is a Relational Database? The Basics Select - Finding Your Data Select Comparing Values Joining Tables Creating Basic Triggers Starting with Views Data Types Primary Keys Foreign Keys Outer Joins Constraints Resolving Many To Many Maintaining Referential Integrity Triggers As Constraints Changing Table Definitions 1 Changing Table Definitions 2 Changing Table Definitions 3 Changing Table Definitions 4 - Summary Nulls and Nothings Self Referencing, Lookup Table Tips and Tricks

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