{"id":8141,"date":"2020-04-08T22:03:32","date_gmt":"2020-04-08T12:03:32","guid":{"rendered":"https:\/\/database.guide\/?p=8141"},"modified":"2025-11-15T11:56:27","modified_gmt":"2025-11-15T01:56:27","slug":"create-table-sqlite","status":"publish","type":"post","link":"https:\/\/database.guide\/create-table-sqlite\/","title":{"rendered":"Create a Table in SQLite"},"content":{"rendered":"\n

To create a table<\/a> in SQLite<\/a>, use the CREATE TABLE<\/code> statement.<\/p>\n\n\n\n

This statement accepts the table name, the column<\/a> names and their definitions, as well as some other options.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s a basic example.<\/p>\n\n\n\n

CREATE TABLE Products(\n  ProductId INTEGER PRIMARY KEY AUTOINCREMENT, \n  ProductName TEXT NOT NULL,\n  Price NUMERIC\n);<\/code><\/pre>\n\n\n\n
So in this case, Products<\/code> is the name of the table, and it contains three columns; ProductId<\/code>, ProductName<\/code>, and Price<\/code>.  <\/p>\n\n\n\n
In this example, I have added each column’s data type as well as some constraints, but these are all optional.<\/p>\n\n\n\n
\n
Specify the Schema<\/h2>\n\n\n\n
The table’s name can (optionally) be prefixed with the schema name. When doing this, the schema name must be either main<\/code>, temp<\/code>, or the name of an attached database.<\/p>\n\n\n\n
So I could do this instead:<\/p>\n\n\n\n
CREATE TABLE Store.Products(\n  ProductId, \n  ProductName,\n  Price\n);<\/code><\/pre>\n\n\n\n
In this case, Store<\/code> is the name of the attached database that I want to create the table in. <\/p>\n\n\n\n
The column name can be followed by the data type and any constraints. <\/p>\n<\/div><\/div>\n\n\n\n
Data Type is Optional<\/h2>\n\n\n\n
Yes, you read that right – the data type is actually optional. <\/p>\n\n\n\n
SQLite uses dynamic typing and so the data type of a value is associated with the value itself, not with its container (column). This is in contrast to most other SQL<\/a> database systems, where you must specify the data type when you create the column.<\/p>\n\n\n\n
So I could do this instead:<\/p>\n\n\n\n
CREATE TABLE Products(\n  ProductId, \n  ProductName,\n  Price\n);<\/code><\/pre>\n\n\n\n
Constraints & Other Options<\/h2>\n\n\n\n
You have the option of specifying any constraints or other options you’d like to be applied against each column. These include the following:<\/p>\n\n\n\n