Overview
The SQLite CREATE TABLE statement is used to create a new table in the database.
Before creating the table, we must design and decide basic table details and relationships with other tables. The ER diagram explains these details.
Basic table details involve:
- Table Name
- Column definitions and each column’s data type.
- Table Primary key and Foreign keys
- Table Constraints.
SQLite CREATE TABLE
The SQLite create table general syntax is as follows:
CREATE TABLE IF NOT EXISTS database_name.table_name(
column1 datatype PRIMARY KEY,
column2 datatype constraints,
column3 datatype,
…..
columnN datatype,
FOREIGN KEY( this_table_column_name) REFERENCES another_table(another_table_column)
);
The IF NOT EXISTS clause is used to prevent errors that may occur if you try to create a table or index that already exists in the database. The table will be created if it does not exist; otherwise, the statement will have no effect.
Example
Let’s create a table with the following columns.
CREATE TABLE emp ( empno INTEGER PRIMARY KEY, ename TEXT NOT NULL, jobtitle TEXT NOT NULL, salary INTEGER NOT NULL );
This statement creates a table with the specified columns and data types if the table does not already exist. If the table already exists, no error is thrown.
The .tables command displays the tables in the database.
/> .tables
Once the table is created, we can run simple SQL queries to insert or populate the table and view the data. We can use the INSERT statement to populate data into the table.
—
SQLite Tutorials
SQLite tutorials on this website:
https://www.testingdocs.com/sqlite-database-tutorials/
For more information on SQLite, visit the official website: