DbSchema | How to Create a Table in SQLite?

SQLite • 03-Jun-2023

by Malik Umair Sajjad

SQLite alt >

Introduction
Prerequisites
What is a Data Type?
SQLite Data Types
Creating a Table in SQLite3
Creating a Table in DbSchema
Conclusion
References

1. Introduction

SQLite is a self-contained, __serverless, zero-configuration, and __transactional SQL database engine. It’s highly reliable and used in various applications, from embedded devices to web browsers. This article will guide you through creating a table in SQLite using both the __sqlite3 and __DbSchema tools.

2. Prerequisites

To follow along with this guide, you’ll need the following:

A basic understanding of __SQL` (Structured Query Language)
__SQLite` installed on your system (sqlite3 tool for command line usage)
__DbSchema` (A visual database design & management tool)

For installation and establishing connection you can read our article SQLite-How to create a database?

3. What is a Data Type?

A __data type is an attribute that tells the __type of data that a column can store in a database. It defines the __kind of values` that a column can accept. Different data types can include integers, decimal numbers, strings, dates, etc.

4. SQLite Data Types

In SQLite, data types are __categorized` into five storage classes:

SQLite Datatypes alt >

*Storage Class*	*Description*
__NULL`	The value is a NULL value.
__INTEGER`	The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude.
__REAL`	The value is a floating point value, stored as an 8-byte IEEE floating point number.
__TEXT`	The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
__BLOB`	The value is a blob of data, stored exactly as it was input.

Understanding Concept of Primary Key:

A __primary key is a field or set of fields in a database table that __uniquely identifies each record or row in the table. It ensures that each entry in the table has a __unique identifier, which helps in __identifying, __retrieving, and __manipulating specific records in the database.

Key Characteristics of Primary Key:

Here are some key characteristics of a primary key:

Uniqueness: Every value in the primary key column(s) must be __unique`. No two records can have the same primary key value.
Non-nullability: A primary key value cannot be __NULL or empty`. It must have a valid value for every record.
Immutable: The value of a primary key should not __change over time`. It provides a stable and reliable reference to a specific record.
Single value or combination: A primary key can be a single field or a combination of multiple fields (composite key), depending on the requirements of the database.
Indexing: The primary key is typically __indexed, which helps improve the performance of __searching, __sorting`, and joining operations on the table.

5. Creating a Table in SQLite3

Step 1: Start SQLite3

Firstly, you need to start __SQLite3`. Open your terminal or command line interface and type:

sqlite3

This will open the SQLite3 interactive shell.

Step 2: Create a New Database

Let’s create a new database named “SchoolDB”. To do this, use the __.open` command followed by the name of the database you want to create:

.open SchoolDB.db

This will create a new database file named “SchoolDB.db”. If the file already exists, it will open the __existing file`.

To know more about creating a database you can read our article SQLite-How to create a database?

Result from Query:

Following is the result obtained by executing the query

*Seq*	*Name*	*File*
__0`	main	/home/sqlite/SchoolDB.db

Step 3: Create a New Table

To create a table in SQLite3, you use the __CREATE TABLE` statement. The general syntax is:

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY(one or more columns)
);

Refer to the Understanding SQLite Data Types section if you need a reminder of the different data types.

Now, let’s create a table named “Students”. You can do this using the __CREATE TABLE` statement.

CREATE TABLE Students (
   StudentId INTEGER,
   FirstName TEXT,
   LastName TEXT,
   BirthDate TEXT,
   PRIMARY KEY(StudentId)
);

This command will create a __new table` named “Students” with columns for “StudentId”, “FirstName”, “LastName”, and “BirthDate”. The “StudentId” column is defined as the primary key.

Step 4: Verify the Table Creation

You can __verify the creation of the table by listing all tables in the database with the __.tables command:

.tables

If the “Students” table was created successfully, it should be listed in the output of this command.

Result from Query:

Following is the result obtained by executing the query

sqlite>.tables
Students

Step 5: Exit SQLite3

Once you’re done with your database operations, you can exit the SQLite3 interactive shell using the __.quit` command:

.quit

This completes the process of creating a table in SQLite3.

6. Creating a Table in DbSchema

Creating a table in DbSchema involves the use of a graphical interface, which makes the process more intuitive and user-friendly.

__Launch` DbSchema and create a new project. Connect to your SQLite database.
Right-click anywhere on the __Layout` and select ‘Create Table’.
A __dialog` will appear where you can set the table name (e.g., ‘Students’) and start defining columns with their names, data types, and constraints.
After defining all the columns and __constraints`, click ‘Apply’ to create the table.

For our ‘Students’ table, you would define the same columns and data types as in the SQLite3 example.

Create Tables and Visually Manage SQLite using DbSchema

DbSchema is a SQLite client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Table >

Create Table

Start the application and connect to the SQLite database. Right-click the table folder to create a table.

Add Columns >

Add Table Columns

Add the columns to the table.

7. Conclusion

SQLite is a __versatile database solution that supports multiple data types and allows for easy __creation and __management of tables, whether you're working directly with the __sqlite3 tool or utilizing the __DbSchema tool's graphical interface. Both methods have their unique advantages; sqlite3 commands offer a quick, __scriptable solution, while DbSchema provides a more visually intuitive experience.

8. References

SQLite Documentation: SQLite Datatypes
SQLite Documentation: SQLite Create Table
DbSchema Documentation: Create a Table