DbSchema Database Designer

DbSchema | How to Create a Table in SQLite?

Publish on DbSchema Blog >>>

SQLite alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. What is a Data Type?
  4. SQLite Data Types
  5. Creating a Table in SQLite3
  6. Creating a Table in DbSchema
  7. Conclusion
  8. 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:

  1. Uniqueness: Every value in the primary key column(s) must be __unique`. No two records can have the same primary key value.

  2. Non-nullability: A primary key value cannot be __NULL or empty`. It must have a valid value for every record.

  3. Immutable: The value of a primary key should not __change over time`. It provides a stable and reliable reference to a specific record.

  4. 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.

  5. 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:


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.

   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:


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


Step 5: Exit SQLite3

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


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.

  1. __Launch` DbSchema and create a new project. Connect to your SQLite database.
  2. Right-click anywhere on the __Layout` and select ‘Create Table’.
  3. 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.
  4. 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

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.