SQLite Constraint Management

Introduction

In this lab, you will explore SQLite constraint management to ensure data integrity. You'll begin by defining foreign key constraints to establish relationships between tables, implementing CHECK constraints, creating composite keys, and finally, testing constraint violations to understand how SQLite enforces these rules. This hands-on experience will provide a practical understanding of how to maintain data consistency within your SQLite databases.

Create Tables with a Foreign Key Constraint

In this step, you will create two tables, customers and orders, and establish a foreign key constraint between them. This constraint will ensure that each order is associated with a valid customer.

First, open the SQLite shell by running the following command in your terminal:

sqlite3 /home/labex/project/database.db

This command opens a connection to the SQLite database file named database.db. If the file doesn't exist, SQLite will create it. You should now see the sqlite> prompt.

Now, create the customers table with the following SQL command:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

This command creates a table named customers with four columns: customer_id, first_name, last_name, and email. The customer_id column is the primary key and will automatically increment for each new customer.

Next, create the orders table with a foreign key constraint referencing the customers table:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This command creates a table named orders with four columns: order_id, customer_id, order_date, and total. The order_id column is the primary key. The FOREIGN KEY (customer_id) REFERENCES customers(customer_id) clause establishes a foreign key constraint, ensuring that the customer_id in the orders table references a valid customer_id in the customers table.

To verify the tables have been created, you can use the following command:

.tables

This command will list all the tables in the database. You should see both customers and orders in the output.

Insert Data and Test the Foreign Key Constraint

In this step, you will insert data into the customers and orders tables and test the foreign key constraint.

First, you need to enable foreign key constraints in SQLite (they are disabled by default):

PRAGMA foreign_keys = ON;

Now, insert some data into the customers table:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');

This command inserts two new customers into the customers table. The customer_id will be automatically assigned.

Now, insert an order into the orders table, referencing one of the existing customers:

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

This command inserts a new order into the orders table, associating it with the customer whose customer_id is 1.

Next, try to insert an order with a customer_id that does not exist in the customers table:

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

You will see an error message similar to this: Error: FOREIGN KEY constraint failed. This confirms that the foreign key constraint is working correctly, preventing you from creating an order for a non-existent customer.

To view the data in the tables, you can use the following commands:

SELECT * FROM customers;
SELECT * FROM orders;

These commands will display the contents of the customers and orders tables, respectively.

Implement a CHECK Constraint

In this step, you will create a new customers_with_check table that includes a CHECK constraint to ensure that all email addresses contain the @ symbol.

First, let's create a new table with the CHECK constraint directly:

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

This command creates a new table named customers_with_check with a CHECK constraint on the email column that ensures it contains the @ symbol. The LIKE operator is used for pattern matching, and % is a wildcard character that matches any sequence of characters.

Now, let's copy the existing customer data to the new table:

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

Now, try to insert a new customer with an invalid email address:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

You should see an error message similar to this: Error: CHECK constraint failed: email. This indicates that the CHECK constraint prevented the insertion of the invalid email address.

To verify that the CHECK constraint is working, insert a customer with a valid email address:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');

This command should execute successfully, as the email address contains the @ symbol.

Create a Table with a Composite Key

In this step, you will create a table named enrollments with a composite key consisting of the student_id and course_id columns.

Execute the following SQL statement in the SQLite shell:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

This command creates a table named enrollments with three columns: student_id, course_id, and enrollment_date. The PRIMARY KEY (student_id, course_id) clause specifies that the primary key for the table consists of both the student_id and course_id columns. This means that the combination of student_id and course_id must be unique for each row in the table.

Now, insert some data into the enrollments table:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

This will insert three rows into the enrollments table.

Next, try to insert a row with the same student_id and course_id as an existing row:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

You should see an error message similar to this: Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. This indicates that the composite key constraint prevented the insertion of the duplicate row.

To verify that the table has been created correctly, you can use the .tables command in the SQLite shell:

.tables

You should see enrollments in the output.

Summary

In this lab, you have learned how to define and implement constraints in SQLite to ensure data integrity. You created tables with foreign key constraints, implemented CHECK constraints to validate data, and created tables with composite keys to uniquely identify rows based on multiple columns. By understanding and utilizing these constraints, you can build robust and reliable databases that maintain data consistency.