Exercise 43: SQL Administration

The word "administration" is overloaded in databases. It can mean "making sure a PostgreSQL server keeps running", or it can mean "altering and migrating tables for new software deployments". In this exercise I'm only covering how to do simple schema alterations and migrations. Managing a full database server is outside the scope of this book.

Destroying and Altering Tables

You've already encountered DROP TABLE as a way to get rid of a table you've created. I'm going to show you another way to use it and also how to add or remove columns from a table with ALTER TABLE.

/* Only drop table if it exists. */
DROP TABLE IF EXISTS person;

/* Create again to work with it. */
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

/* Rename the table to peoples. */
ALTER TABLE person RENAME TO peoples;

/* Add a hatred column to peoples. */
ALTER TABLE peoples ADD COLUMN hatred INTEGER;

/* Rename peoples back to person. */
ALTER TABLE peoples RENAME TO person;

.schema person

/* We don't need that. */
DROP TABLE person;

I'm doing some fake changes to the tables to demonstrate the commands, but this is everything you can do in SQLite3 with the ALTER TABLE and DROP TABLE statements. I'll walk through this so you understand what's going on:

• ex21.sql:2: Use the IF EXISTS modifier, and the table will be dropped only if it's already there. This suppresses the error you get when running your .sql script on a fresh database that has no tables.
• ex21.sql:5: Just recreating the table again to work with it.
• ex21.sql:13: Using ALTER TABLE to rename it to peoples.
• ex21.sql:16: Add a new column hatred that is an INTEGER to the newly renamed table peoples.
• ex21.sql:19: Rename peoples back to person because that's a dumb name for a table.
• ex21.sql:21: Dump the schema for person so you can see it has the new hatred column.
• ex21.sql:24: Drop the table to clean up after this exercise.