Creating and deleting databases and tables with SQLite

How to use SQLite's CREATE TABLE command

In this section, we will add the data structure to our newly created people.db database by creating a student table. To create a table within your database, you will utilize the CREATE TABLE statement with the following syntax:

CREATE TABLE student (
    id INTEGER PRIMARY KEY, 
    first_name TEXT, 
    last_name TEXT, 
    age INTEGER,
    student_email TEXT NOT NULL, 
    class TEXT
    );

We can break down the above statement into the following pieces:

• CREATE TABLE <table name>: This is the basic command statement. In the example, the <table name> is student and should be whatever you intend to name your table.
• <column name> <data type>: This syntax defines a basic column within the table. In the example, a column name would be first_name and its corresponding data type defined by SQLite Data Types is TEXT.
• <column constraint>: Column constraints are optional restraints adding additional requirements for the data entering your table. In the example, the column constraint Not Null is added to the student_email column. This ensures no entry is made without this column being populated.
• <table constraint>: Like a column constraint, table constraints are optional to add additional requirements to your data. There is an exception if the constraint affects the interaction of multiple columns instead of a singular column. In our example, the addition of PRIMARY KEY to the id field is an example of a table constraint.

It is important to note that the CREATE TABLE statement will create the table you specify in the main database by default. If you have multiple connections open to databases, you need to specify <database>.<table> in your statement to create the table in a database other than main. Adjusting the previous example will look like this to create the table in test:

CREATE TABLE test.student (
    id INTEGER PRIMARY KEY, 
    first_name TEXT, 
    last_name TEXT, 
    age INTEGER,
    student_email TEXT NOT NULL, 
    class TEXT
    );

How to create tables only if they do not already exist

To ensure that you are creating a table that does not already exist, the optional IF NOT EXISTS clause can be added to the previous example as follows:

CREATE TABLE IF NOT EXISTS student (
    id INTEGER PRIMARY KEY, 
    first_name TEXT, 
    last_name TEXT, 
    age INTEGER,
    student_email TEXT NOT NULL, 
    class TEXT
    );

SQLite will throw an error when attempting to create an already present table without the IF NOT EXISTS clause by default. By adding this clause, the default behavior is overridden with a warning instead of an error. The rest of the command's behavior remains the same.

How to validate your table with .schema

After creating your table, you can verify the structure of your table by using the .schema command. If we want to make sure the previously created student table is structured the way we intended, we can check using the following syntax:

.schema student

The returned result will look as follows for the student table:

sqlite> .schema student
CREATE TABLE student (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, age INTEGER, student_email TEXT NOT NULL, class TEXT);
sqlite>

To get a more easily read result, you can use the .fullschema --indent command. This will show you the schema of the connected database with better spacing:

.fullschema --indent

sqlite> .fullschema --indent
CREATE TABLE student (
    id INTEGER PRIMARY KEY, 
    first_name TEXT, 
    last_name TEXT, 
    age INTEGER, 
    student_email TEXT NOT NULL, 
    class TEXT
);
sqlite>

Note: The .fullschema command will also include dumps of the statistics tables if they exist. We won't cover that for now, but it can be useful to have this output in some cases.