Learning SQL -Changing Table Definitions - Part 2

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

In our previous lesson we discussed changing table definitions using Alter Table commands. One of the problems with SQlite is when we alter a table we can lose stuff like Not Null column settings, Constraints etc.

Setting up some data

DO A BACKUP BEFORE EXECUTING THIS CODE, OR CREATE A NEW DATABASE - THIS CODE WILL CORRUPT YOUR DATABASE!!!!

Lets create another table to store the books we are going to try and sell to our Parents:

create table QiSQL_Bbooks ( id integer primary key,
book_title text not null,
author text not null ,
book_cost numeric not null,
book_price numeric not null,
unique (book_title , author ) );


Note that we have added a Unique key on book_title / author. Now lets add an ISBN number column, add a new unique index on the ISBN number, a trigger to prevent anyone deleting any of our Books and a view to only show books by 'John Smith'.

alter table QiSQL_Bbooks add ISBN text;


create unique index Q_Bbooks_ix1 on QiSQL_Bbooks ( ISBN );


create trigger
QiSQL_Bbooks_No_delete
before delete on
QiSQL_BBooks
for each row begin
select raise ( ROLLBACK , '
Delete not allowed' );
end;


insert into QiSQL_Bbooks (book_title,ISBN,book_cost,book_price, author) values
('Big Book' ,'123-123-123' , 23.99 , 47.99 ,'John Smith');

insert into QiSQL_Bbooks (book_title,ISBN,book_cost,book_price,author ) values
('Small Book' ,'123-123-456' , 19.99,38.99 ,'Fred Bloggs');

create view johnsbooks as select * from QiSQL_Bbooks where author = 'John Smith' ;

Much more detail will be coming on triggers ( we've already covered a small section on triggers in a previous lesson ), so don't worry too much about them now. This trigger will prevent any deletions from our table and the database error will display our 'Delete not allowed' message. Check what this trigger does by typing:

delete from QiSQL_Bbooks;

Change the table name

Oh No! A disaster we have called our table QiSQL_Bbooks, too many Bees! Let's quickly use Alter Table to change the name. But, before we do that we can use another new concept to see what the 'unique (book_title)' clause in our create table and the 'Create unique Index' command has done:

pragma index_list ( QiSQL_Bbooks) ;

Pragma statements are very useful insights into what is going on under the bonnet of SQLite - there will be a complete section devoted to them later in the course.

Here is the data returned:

Seq Name Unique
0 Q_Books_ix1 1
1 sqlite_autoindex_QiSQL_Bbooks_1 1

This shows uas that SQLite has automatically added a Unique Constraint on our new table along with the unique index we created - Q_BooksIx1. Constraints were coverd in detail in the Constraints lesson. This basically means that SQLite will not allow us to enter duplicate values into this column.

Now let's rename our table without the excessive use of Bees.

alter table QiSQL_Bbooks rename to QiSQL_Books;

SQLite_Master Table

There is an interesting little table provided by SQLite - sqliite_master, This table will have its own special section later in the course. I mention it here because many people use this table to recreate existing database objects. If you issue this command:

select sql from sqlite_master
where type='table'
and tbl_name like 'QiSQL_Books%';

The query will return the SQL issued to create the table:

CREATE TABLE 'QiSQL_Books' ( id integer primary key,
book_title text not null,
book_cost numeric not null,
book_price numeric not null, ISBN text,
unique (book_title) )

This looks great - BUT! Where is the unique index Qi_Books_ix_1 we created on the book_title / author columns? DANGER WILL ROBINSON! Be careful when using sqlite_master.

EVEN WORSE! If you run this query on sqlite_master:

select type, name from sqlite_master
where tbl_name like 'QiSQL_Bbooks%';

type name
trigger QiSQL_Bbooks_No_delete

You will see that the Trigger we created on QiSQL_Bbooks is STILL shown on a table that no longer exists! What does this mean? It means your database is CORRUPTED!

So we have to get rid of that trigger before we rename the table (if you try to drop the trigger AFTER the rename you will get an 'no such trigger' error).

Backup To The Rescue!

OK, so now you can restore your Backup or recreate your test database. I did warn you in big red letters!

Re-run all the stuff up to the Alter Table Rename' command, here's all the code in easy to run format (just cut and paste the whole lot into QiSQL or your chosen SQLite application):

drop table if exists QiSQL_Bbooks;
drop table if exists TMP_QiSQL_BBooks;
drop table if exists QiSQL_Books;
drop view if exists johnsbooks ;
create table QiSQL_Bbooks ( id integer primary key,
book_title text not null,
author text not null ,
book_cost numeric not null,
book_price numeric not null,
unique (book_title , author ) );
alter table QiSQL_Bbooks add ISBN text;
create unique index Q_Bbooks_ix1 on QiSQL_Bbooks ( ISBN );
create trigger QiSQL_Bbooks_No_delete
before delete on QiSQL_BBooks
for each row begin
select raise ( ROLLBACK , 'Delete not allowed' );
end;
insert into QiSQL_Bbooks (book_title,ISBN,book_cost,book_price, author) values
('Big Book' ,'123-123-123' , 23.99 , 47.99 ,'John Smith');
insert into QiSQL_Bbooks (book_title,ISBN,book_cost,book_price,author ) values
('Small Book' ,'123-123-456' , 19.99,38.99 ,'Fred Bloggs');
create table TMP_QiSQL_BBooks as select * from QiSQL_Bbooks;
create view johnsbooks as select * from QiSQL_Bbooks where author = 'John Smith';

So now you have the Table QiSQL_Bbooks waiting to be renamed. There will probably be arguments about the best way to this but this is what we'll do at the moment.

You'll get a set of

create table TMP_QiSQL_BBooks as
select * from QiSQL_Bbooks;

Now we drop the original table:

drop table QiSQL_Bbooks;

If you now re-rerun the sqlite_master query to check if any rogue triggers are hanging around:

select type, name from sqlite_master
where tbl_name like 'QiSQL_Bbooks%';

You will see that there are no no rows returned. Dropping the table also dropped triggers associated with it - Good!

Now we can rename the Temporary table to our required name:

alter table TMP_QiSQL_Bbooks rename to QiSQL_Books;

OK. the table has been renamed BUT we have lost our indexes, our trigger and our view!

The next part of this lesson (yes, I know your bored with this but there will be a part 3!) will give you some ideas on how to sort out this mess!