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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

/* 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.

Migrating and Evolving Data

Let's apply some of the skills you have learned. I'll have you take your database and "evolve" the schema to a different form. You'll need to make sure you know the previous exercise well and have your code.sql working as we'll be. If you don't have either of these, then go back and get everything straightened out.

To make sure you are in the right state to attempt this exercise, when you run your code.sql you should be able to run .schema like this:

$ sqlite3 ex13.db < code.sql
$ sqlite3 ex13.db .schema
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);
CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);
CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER,
    dob DATETIME
);

Make sure your tables look like my tables, and if not, then go back and remove any commands that are doing ALTER TABLE or anything from the last exercise.

Exercise Challenge

What you're tasked with doing is the following list of changes to the database:

1. Add a dead column to person that's like the one in pet.
2. Add a phone_number column to person.
3. Add a salary column to person that is float.
4. Add a dob column to both person and pet that is a DATETIME.
5. Add a purchased_on column to person_pet of type DATETIME.
6. Add a parent to pet column that's an INTEGER and holds the id for this pet's parent.
7. Update the existing database records with the new column data using UPDATE statements. Don't forget about the purchased_on column in the person_pet relation table to indicate when that person bought the pet.
8. Add four more people and five more pets, and assign their ownership and which pet's are parents. On this last part remember that you get the id of the parent, then set it in the parent column.
9. Write a query that can find all the names of pets and their owners bought after 2004. The key to this is to map the person_pet based on the purchased_on column to the pet and parent.
10. Write a query that can find the pets that are children of a given pet. Again look at the pet.parent to do this. It's actually easy, so don't over think it.
11. Update your code.sql file you've been putting all the code in so that it uses the DROP TABLE IF EXISTS syntax.
12. Use ALTER TABLE to add a height and weight column to person and put that in your code.sql file.
13. Run your new code.sql script to reset your database, and you should have no errors.

You should do this by writing a ex13.sql file with these new things in it. You then test it by resetting the database using code.sql and then running ex13.sql to alter the database and run the SELECT queries that confirm you made the right changes.

Further Study

Continue reading the documentation for DROP TABLE https://sqlite.org/lang_droptable.html and ALTER TABLE https://sqlite.org/lang_altertable.html and then go to the SQLite3 language page at https://sqlite.org/lang.html and read the documentation for the remaining CREATE and DROP statements.