Build SQLite Database For Contacts

Introduction

In this challenge, you will build an SQLite database for storing customer contact information. The goal is to design and create a customers table within the customers.db database, located in the ~/project directory.

You will define columns for id (INTEGER PRIMARY KEY), name (TEXT), and email (TEXT), ensuring that the email column has a UNIQUE constraint to prevent duplicate email addresses. The challenge involves using the sqlite3 shell to execute SQL commands, specifically the CREATE TABLE statement, and verifying the table structure using the .schema customers command.

Build SQLite Database For Contacts

Design a database table for storing customer information.

Tasks

• Create a table named customers in the customers.db database with columns id, name, and email. The id column should be the primary key.
• Add a UNIQUE constraint to the email column in the customers table to ensure each customer has a unique email address.

Requirements

1. Connect to the SQLite database customers.db located in the ~/project directory.
2. Create the customers table using the CREATE TABLE statement.
3. The id column must be an INTEGER and the PRIMARY KEY.
4. The name and email columns must be of type TEXT.
5. Implement the UNIQUE constraint on the email column.
6. All SQL commands must be executed within the sqlite3 shell.

Examples

After successfully creating the table, the PRAGMA table_info(customers); command should output:

0|id|INTEGER|0||1
1|name|TEXT|0||0
2|email|TEXT|0||0

Hints

• Use the sqlite3 command to connect to the database: sqlite3 customers.db.
• The CREATE TABLE statement is used to create a new table.
• The PRIMARY KEY constraint ensures that the id column uniquely identifies each row.
• The UNIQUE constraint ensures that all values in the email column are distinct.
• Use .schema customers to verify the table structure.

Summary

In this challenge, a SQLite database named customers.db was created to store customer information. The task involved designing and implementing a customers table with columns for id (INTEGER PRIMARY KEY), name (TEXT), and email (TEXT).

The key learning point was the application of constraints, specifically setting the id column as the primary key and enforcing uniqueness on the email column using the UNIQUE constraint. The .schema customers command was used to verify the successful creation and structure of the table.