SQLite Error Handling

Introduction

In this lab, you will learn how to effectively handle errors in SQLite databases. The primary focus is on managing constraint violations during INSERT operations using the ON CONFLICT clause.

You'll begin by creating a database and a table with a UNIQUE constraint. Then, you'll explore different actions that can be taken when a conflict occurs, such as ROLLBACK, ABORT, FAIL, IGNORE, or REPLACE, specifically using ON CONFLICT IGNORE to prevent insertion when a unique constraint is violated. The lab will guide you through practical examples of inserting data and handling potential errors.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_row("Insert Single Row") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/start_trans("Begin New Transaction") sqlite/SQLiteGroup -.-> sqlite/undo_trans("Rollback Transaction") subgraph Lab Skills sqlite/init_db -.-> lab-552550{{"SQLite Error Handling"}} sqlite/make_table -.-> lab-552550{{"SQLite Error Handling"}} sqlite/add_row -.-> lab-552550{{"SQLite Error Handling"}} sqlite/add_rows -.-> lab-552550{{"SQLite Error Handling"}} sqlite/start_trans -.-> lab-552550{{"SQLite Error Handling"}} sqlite/undo_trans -.-> lab-552550{{"SQLite Error Handling"}} end

Create a SQLite Database and Table with a UNIQUE Constraint

In this step, you will create a SQLite database and a table with a UNIQUE constraint. This constraint will help you understand how to handle errors when inserting duplicate data.

First, open your terminal in the LabEx VM. Your default path is /home/labex/project.

Now, let's create a SQLite database named my_database.db. Run the following command to create the database file and open the SQLite command-line tool:

sqlite3 my_database.db

You will see a prompt indicating that you are now inside the SQLite shell:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Next, create a table named users to store user information. This table will have three columns: id, username, and email. The username column will have a UNIQUE constraint, meaning that each username must be unique within the table. Enter the following SQL command at the sqlite> prompt and press Enter:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL
);

This command sets up the users table where:

id is an integer that automatically increases for each new entry. The PRIMARY KEY constraint ensures that each id is unique, and AUTOINCREMENT makes it increase automatically.
username is a text field that cannot be left empty (NOT NULL) and must be unique (UNIQUE).
email is also a text field that cannot be left empty (NOT NULL).

You won't see any output if the command runs successfully.

Insert Data into the Table

Now that you have created the users table, let's add some data to it. We'll insert a user record into the table.

Insert a user record into the users table by running this command at the sqlite> prompt:

INSERT INTO users (username, email) VALUES ('Alice', '[email protected]');

This command adds a row to the users table.

INSERT INTO users (username, email) specifies that you are inserting data into the username and email columns of the users table.
VALUES ('Alice', '[email protected]') provides the values to be inserted for each record.

To confirm that the data was added correctly, run this command to view all records in the table:

SELECT * FROM users;

Expected Output:

1|Alice|[email protected]

This output shows the id, username, and email for the record. The SELECT * command retrieves all columns from the specified table.

Attempt to Insert Duplicate Data

In this step, you will attempt to insert a record with a duplicate username. This will violate the UNIQUE constraint and cause an error.

Run the following command at the sqlite> prompt:

INSERT INTO users (username, email) VALUES ('Alice', '[email protected]');

You will see an error message similar to this:

Error: UNIQUE constraint failed: users.username

This error message indicates that the UNIQUE constraint on the username column has been violated. SQLite prevents the insertion of duplicate usernames to maintain data integrity.

Use ON CONFLICT IGNORE to Handle Constraint Violations

In this step, you will learn how to use the ON CONFLICT IGNORE clause to handle constraint violations. This clause tells SQLite to ignore the insertion if it would violate a constraint.

Run the following command at the sqlite> prompt:

INSERT OR IGNORE INTO users (username, email) VALUES ('Alice', '[email protected]');

This command attempts to insert a new record with the username 'Alice'. However, because the ON CONFLICT IGNORE clause is used, SQLite will ignore the insertion and no error will be raised.

To confirm that the duplicate record was not inserted, run this command:

SELECT * FROM users;

Expected Output:

1|Alice|[email protected]

The output shows that only the original record with the username 'Alice' exists in the table. The duplicate record was ignored.

The INSERT OR IGNORE statement provides a way to prevent errors and maintain data integrity when inserting data that might violate constraints.

Use ON CONFLICT REPLACE to Handle Constraint Violations

In this step, you will learn how to use the ON CONFLICT REPLACE clause to handle constraint violations. This clause tells SQLite to replace the existing record with the new record if it would violate a constraint.

Run the following command at the sqlite> prompt:

INSERT OR REPLACE INTO users (id, username, email) VALUES (1, 'Alice', '[email protected]');

This command attempts to insert a new record with the username 'Alice'. Because the ON CONFLICT REPLACE clause is used, SQLite will replace the existing record with the new record. Note that we need to specify the id in this case, as REPLACE will delete the old row and insert a new one.

To confirm that the record was replaced, run this command:

SELECT * FROM users;

Expected Output:

1|Alice|[email protected]

The output shows that the email address for the record with the username 'Alice' has been updated to [email protected].

The INSERT OR REPLACE statement provides a way to update existing records while maintaining data integrity.

Finally, exit the SQLite shell:

.exit

This will close the database connection and return you to the terminal.

Summary

In this lab, you have learned how to effectively handle errors in SQLite databases, focusing on constraint violations during INSERT operations. You created a database and a table with a UNIQUE constraint, inserted data, and then attempted to insert duplicate data to trigger an error. You then explored how to use the ON CONFLICT IGNORE and ON CONFLICT REPLACE clauses to handle these constraint violations, allowing you to control how SQLite responds to potential errors and maintain data integrity.