SQLite create table

Introduction to SQLite create table

SQLite provides a create table statement to the user, in which we are able to create a new table with specified name. Normally in create table statement create table statement followed by table name with column name and data type. We have different options to create a table that means where we want to store the table, user defined database or default database that depends on the user. If we need to specify the database then we can specify the database name as per requirement otherwise we can use default database name. If the table name is already present in the database then it shows an error message.

Syntax of SQLite create table

Given below is the syntax of SQLite create table:

create table [If table name not exists] specified table name (colm name 1 data type, colm name 2 data type, colm N data type) without rowid;

Explanation:

• In the above syntax we use the create table statement to create a new table, here the specified table name means the name of the table that we need to create. In SQLite table names we cannot start with sqlite because of the internal use of sqlite.
• After that we use if table name not exists it is an optional part of this syntax to create a new table. If we need to create a new table without using if not exists and the table name already exists then it shows the error message.
• Colm name is used for column name that we need to add into the table and we apply different constraints with column name as per requirement.
• Without rowid is an optional part of creating a table statement, in SQLite database table has a different column and which is referred as rowid, oid and _rowid_ column. Normally the rowid column used 64 bit to store the signed integer number and uniquely identified in the table.
• If we don’t need to create a rowid then we can use it without rowid keyword.

How to create table in SQLite?

Now let’s see how table statements work in SQLite as follows.

Some things we must know at the time of table creation are mentioned below:

• If we need to create a table with the same name that means if the table name already existed then we need to drop that table first then we are able to create a table with the required name.
• If we use IF NOT EXISTS clause at the time of table, if the table name already existed then it shows an error message.
• Another way to create tables is by using LIKE clause, if we need to create the same table from another definition of table at that time we use LIKE keyword.
• We can use the NOT NULL option and it is used to specify the value of a particular column may or may not be NULL.
• Default option is also available or we can say the default clause we can use at the time of table creation. When we assign a default clause to a specified column that means it uses default value to that column.
• SQLite also provides an auto_increment option to users. When we use the auto_increment option, SQLite automatically generates sequential integer numbers for each insertion operation.
• The primary key option user can use primary key option at the time of table creation. It used to define a unique index on a specific column unless the specified column is not null.
• SQLite also provides a unique key option. Use of this is that to specify all values of specified columns must be distinct from each other. Unless the specified column name is not null.

Example of SQLite create table

Given below is the example of SQLite create table:

Suppose we need to manage an employee list then we requires the following field as follows.

• emp_id: It is used to store the employee id.
• emp_first_name: It is used to store the name of an employee.
• emp_last_name: It is used to store the name of an employee.
• emp_email: Store employee email id.
• emp_phone: Store the contact number of the employee.

Now use the create table statement to create a new table as per your requirement as follows.

Code:

create table emp (emp_id integer PRIMARY KEY AUTOINCREMENT, emp_first_name text not null, emp_last_name text not null, emp_email text not null unique, emp_phone text not null unique);

Explanation:

• In the above example, we use create table statement to create new table name as emp with a different attributes such as emp_id with integer data type and we assign primary key and autoincrement constraint, emp_first_name with text data type with not null constraint, emp_last_name with text data type with not null constraint, emp_email with text data type with not null and unique constraint and emp_phone with text data type and not null and unique constraint.
• See here we just use create table statement that means without IF NOT EXISTS and WITHOUT ROWID option.
• The end out of the above statement we illustrated by using the following screenshot.

Code:

.table

Output:

Now let’s see what happens. We try to create the same table as follows.

Code:

create table emp(id integer, name text);

Explanation:

• In the above example, we try to create the same table that means the table name already exists in the database, so at that time it shows an error message.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Now create a table without rowid as follows.

Use the same statements that create table statements to create a new table as follows.

Code:

create table emp1 (id integer primary key, name text) without rowid;

Explanation:

• In the above example we use create a table statement to create a new table name as emp1 with different attributes such as id with integer data type and primary key constraint and name with text data type as shown in above statement. Here we use without rowid option.
• The end out of the above statement we illustrated by using the following screenshot.

Output:

Conclusion

From the above article we saw the basic syntax of creating table statements and we also saw different examples of creating tables. We also saw the rules of creating tables. From this article we saw how and when we use the SQLite create table statement.

Recommended Articles

This is a guide to SQLite create table. Here we discuss the introduction, how to create table in SQLite? and example respectively. You may also have a look at the following articles to learn more –

1. SQL DDL Commands
2. SQL Clustered Index
3. SQL Super Key
4. SQL LOCK TABLE