Building Tables in SQLite

Introduction

In this lab, you will learn the fundamental skills of creating and managing tables in SQLite, a lightweight and serverless database engine perfect for small-scale applications. You will explore SQLite data types, create tables with primary keys, apply constraints such as NOT NULL and UNIQUE, inspect table structures using the .schema command, and safely drop tables. Through step-by-step exercises in the LabEx VM environment, you will gain practical experience using the sqlite3 command-line tool to build and manage database tables. This lab is designed for beginners, providing a clear path to understanding SQLite table management.

Connect to SQLite and Create a contacts Table

In this first step, you will connect to a SQLite database and create your first table named contacts. SQLite is a lightweight database engine that stores data in a single file. You'll use the sqlite3 command-line tool to interact with the database.

Open a terminal in the LabEx VM environment. Ensure you are in the default working directory /home/labex/project.
Connect to a SQLite database named test.db using the sqlite3 tool. This command will create the database file if it doesn't already exist.
```
sqlite3 test.db
```
This command opens the SQLite shell, and you'll see a prompt like sqlite>, indicating you are inside the SQLite environment.
Create a table named contacts with columns for id, name, and phone. Enter the following SQL command at the sqlite> prompt:
```
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT
);
```
This command creates a table named contacts. The id column is an integer and is designated as the PRIMARY KEY, which means it will uniquely identify each row in the table. The name and phone columns are both of type TEXT, which can store any string data.

Let's break down the command:
- CREATE TABLE contacts: This tells SQLite to create a new table named contacts.
- id INTEGER PRIMARY KEY: This defines a column named id with the data type INTEGER. The PRIMARY KEY constraint ensures that each value in this column is unique and serves as the primary identifier for each row.
- name TEXT: This defines a column named name with the data type TEXT, which can store any string data.
- phone TEXT: This defines a column named phone with the data type TEXT, which can store any string data.

Insert Data into the contacts Table

Now that you have created the contacts table, let's insert some data into it. You'll use the INSERT INTO statement to add new rows to the table.

Insert a new contact into the contacts table. Run this command at the sqlite> prompt:
```
INSERT INTO contacts (name, phone) VALUES ('John Doe', '123-456-7890');
```
This command inserts a new row into the contacts table with the name 'John Doe' and the phone number '123-456-7890'. The id column will be automatically assigned a unique value because it is the primary key.

Let's break down the command:
- INSERT INTO contacts: This tells SQLite to insert data into the contacts table.
- (name, phone): This specifies the columns into which you want to insert data.
- VALUES ('John Doe', '123-456-7890'): This specifies the values to be inserted into the corresponding columns.
Insert another contact into the contacts table:
```
INSERT INTO contacts (name, phone) VALUES ('Jane Smith', '987-654-3210');
```
This command inserts another row into the contacts table with the name 'Jane Smith' and the phone number '987-654-3210'.

Retrieve Data from the contacts Table

Now that you have inserted data into the contacts table, let's retrieve it. You'll use the SELECT statement to query the table and display the data.

Retrieve all data from the contacts table. Run this command at the sqlite> prompt:
```
SELECT * FROM contacts;
```
This command selects all columns (*) from the contacts table and displays the results.

Expected Output:
```
1|John Doe|123-456-7890
2|Jane Smith|987-654-3210
```
The output shows the two contacts you inserted, along with their automatically assigned id values.

Let's break down the command:
- SELECT *: This tells SQLite to select all columns from the table.
- FROM contacts: This specifies the table from which you want to retrieve data.
Retrieve only the name and phone columns from the contacts table:
```
SELECT name, phone FROM contacts;
```
This command selects only the name and phone columns from the contacts table and displays the results.

Expected Output:
```
John Doe|123-456-7890
Jane Smith|987-654-3210
```

Create a users Table with Constraints

Constraints are rules that enforce data integrity in a table. Let's add NOT NULL and UNIQUE constraints to a new table called users.

Create a table named users with constraints. Run this command at the sqlite> prompt:
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
This command creates a table named users with the following constraints:
- username cannot be NULL and must be UNIQUE.
- email cannot be NULL.
Let's break down the command:
- CREATE TABLE users: This tells SQLite to create a new table named users.
- id INTEGER PRIMARY KEY: This defines a column named id with the data type INTEGER. The PRIMARY KEY constraint ensures that each value in this column is unique and serves as the primary identifier for each row.
- username TEXT NOT NULL UNIQUE: This defines a column named username with the data type TEXT. The NOT NULL constraint ensures that this column cannot be empty, and the UNIQUE constraint ensures that all values in this column are distinct.
- email TEXT NOT NULL: This defines a column named email with the data type TEXT. The NOT NULL constraint ensures that this column cannot be empty.
- age INTEGER: This defines a column named age with the data type INTEGER.
Now, let's try to insert some data that violates these constraints to see how they work. First, attempt to insert a user without a username:
```
INSERT INTO users (email, age) VALUES ('test@example.com', 25);
```
You'll see an error because the username column is defined as NOT NULL.

Expected Output:
```
Error: NOT NULL constraint failed: users.username
```
This error shows that SQLite enforces the NOT NULL constraint on username, preventing the insertion.
Next, attempt to insert a user with a duplicate username. First, insert a valid user:
```
INSERT INTO users (username, email, age) VALUES ('johndoe', 'john.doe@example.com', 30);
```
Then, try to insert another user with the same username:
```
INSERT INTO users (username, email, age) VALUES ('johndoe', 'jane.doe@example.com', 25);
```
You'll see an error because the username column is defined as UNIQUE.

Expected Output:
```
Error: UNIQUE constraint failed: users.username
```
This error demonstrates that SQLite enforces the UNIQUE constraint on username, preventing duplicate values.

Inspect Table Structure with .schema

You can inspect the structure of a table using the .schema command. This command displays the SQL statement used to create the table, including column names, data types, and constraints.

Inspect the structure of the users table. Run this command at the sqlite> prompt:
```
.schema users
```
Expected Output:
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
The output shows the CREATE TABLE statement for the users table, including the NOT NULL and UNIQUE constraints on the username column.

The .schema command is a useful tool for understanding the structure of your tables and verifying that the constraints are defined correctly.
Exit the SQLite shell:
```
.quit
```
This command will exit the SQLite shell and return you to the Linux terminal.

IMPORTANT: Make sure .quit before you click the "Continue" button. Otherwise, the step cannot be verified because the sqlite history will not be recorded.

Drop the contacts Table

You can drop a table from the database using the DROP TABLE command. This command permanently deletes the table and all its data, so use it with caution.

Connect to the test.db database:
```
sqlite3 test.db
```
Drop the contacts table. Run this command at the sqlite> prompt:
```
DROP TABLE contacts;
```
This command deletes the contacts table and all its data from the database.

Warning: This action is irreversible. Once you drop a table, you cannot recover it or its data.
Verify that the contacts table has been dropped by attempting to select from it:
```
SELECT * FROM contacts;
```
You'll see an error because the contacts table no longer exists.
Exit the SQLite shell:
```
.quit
```

Summary

In this lab, you have learned the fundamental skills of building and managing tables in SQLite. You explored SQLite data types, created tables with primary keys, applied constraints such as NOT NULL and UNIQUE, inspected table structures using the .schema command, and safely dropped tables. These skills are essential for working with SQLite databases and building data-driven applications.