sqlite
  1. sqlite-create-table
PreviousNext

SQLite Create Table

SQLite is a lightweight relational database management system that stores data in local files and is widely used in embedded systems and mobile applications. In order to create a table in SQLite, you need to define the table schema in a SQL statement.

Syntax

The syntax for creating a table in SQLite is as follows:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    .....
    PRIMARY KEY (column1, column2, ...),
    FOREIGN KEY (column_name) REFERENCES table_name(column_name),
    .....
);

Example

Let's create a table for storing employee information that includes their id, first_name, last_name, email, and salary.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT UNIQUE,
    salary REAL
);

Output

Executing the above query would create a table called employees with columns for id, first_name, last_name, email, and salary.

Explanation

In the example above, we define a table called employees with columns for the employee id, first_name, last_name, email, and salary. We define the data types for each column, as well as any constraints that need to be enforced.

The id column is defined as an INTEGER data type and also set as the PRIMARY KEY for the table. This means that the values in this column must be unique, and every row in the table must have a value for this column.

The first_name and last_name columns are both defined as TEXT data types, while the email column is defined as TEXT with a UNIQUE constraint. This means that each email address must be unique in the table.

The salary column is defined as a REAL data type, which is used for storing floating-point numbers.

Use

Creating tables in SQLite is necessary for storing data in a structured and organized way. You can use SQL queries to create tables that match your data models, allowing you to store and retrieve data efficiently.

Important Points

  • Every table in SQLite must have a PRIMARY KEY, which uniquely identifies each row in the table.
  • You can create tables with other constraints, such as UNIQUE, NOT NULL, CHECK, and FOREIGN KEY constraints.
  • Data types in SQLite are dynamic and usually assigned based on the value you insert into a column. However, it's a good practice to define the data types explicitly when creating tables.
  • Use the appropriate data type for each column based on the data you plan to store in it.

Summary

In this tutorial, we learned how to create a table in SQLite by defining the table schema using SQL statements. We saw an example of creating a table for storing employee information and how to set PRIMARY KEYs and enforce constraints. Creating tables in SQLite is an important step in designing a database that can efficiently store and retrieve data.

Detach DBDrop Table
Published on: