Learning SQL - Changing Table Definitions - Part 1
This section is specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.
DDL - Data Definition Language
We spoke about DDL in some of our earlier lessons. Creating tables, views, indexes etc. all use DDL. Now we come to the tricky bit of SQLite - changing table definitions. No matter how carefully you have designed your database you WILL want to change things as you go along. You would think that it would be a simple matter to drop a few columns, change the data type of columns and add stuff wouldn't you? Well, you would be disappointed!
Adding a column
Some columns may be easily added to SQLite database. You can add any column as long as:
- It is not a Primary Key Column
- It does not have a Unique constraint
- If is Not Null then you must also add a Default Value
- The Default Value may not be one the Current_Date stamps
To add a standard column not covered by the above rules you just need to execute an Alter Table command. Let's add some columns to our QiSQL_Parents table:
alter table QiSQL_Parents add title text;
alter table QiSQL_Parents add middle_name text;
See - it's easy! Now we'll create another table to add to our Schema. This table will store the various after school classes we are going to offer our nice little kiddies.
create table QiSQL_Classes ( class_name text not null );
insert into QiSQL_Classes ( class_name ) values ( 'Cooking' );
insert into QiSQL_Classes ( class_name ) values ( 'Needlework' );
insert into QiSQL_Classes ( class_name ) values ( 'Vegetable Gardening' );
Oh No! We've forgotten to include a primary key - let's try and add one using alter table
alter table QiSQL_Classes add id integer primary key ;
Nope! We get a "Cannot add a PRIMARY KEY column" error. This would not be a major problem as we only have three rows of data so we could drop the table and start again, however imagine we have thousands of rows of vitally important data!
We can rebuild it
We NEED to add a Primary Key column to the QiSQL_Classes table. The first thing we must do is to create a Temporary copy of this table - including a Primary Key column:
create table TMP_QiSQL_Classes ( id integer primary key , class_name text );
Now we can copy the data from the original QiSQL_Classes table into our temporary table:
insert into TMP_QiSQL_Classes ( class_name )
select class_name from QiSQL_Classes;
As our ID column (in the temporary table ) is an Integer Primary Key, SQLite will automatically populate this field for us.
Check the data in the temporary table to make sure it has all been successfully copied.
select * from TMP_QiSQL_Classes;
Here is the data returned:
OK. everything is fine so we can now get rid of our original table by executing a Drop Table command:
drop table QiSQL_Classes;
Now we rename our temporary table so it becomes our shiny new QiSQL_Classes table with its very own primary key:
alter table TMP_QiSQL_Classes rename to QiSQL_Classes;
Some new concepts have been covered in this lesson:
Inserting data into a table using a select command
insert into Table_Name ( column_name , column_name2, .... )
select column_name , column_name2 , .... from Other_Table
Adding a column using 'Alter Table'
alter table Table_Name add Column_Name Datatype
Dropping a Table using 'Drop Table'
drop table Table_Name
Renaming a Table using 'Alter Table'
alter table Table_Name rename to New_Table_Name
This has been a basic look at altering the structure of tables - it gets much more complicated (sorry!), the second part of this lesson will look at how we maintain our rules, constraints and referential integrity when altering table definitions.