SQLite Create Table

SQLite is a relational database management system that allows you to store and query data. To store data in SQLite, you need to create tables first. The CREATE TABLE statement is used to create a new table in an SQLite database.

Syntax for Creating SQLite Table

The basic syntax of CREATE TABLE statement is:

CREATE TABLE table_name (
   column1 datatype, 
   column2 datatype,
   column3 datatype,
   ....
);

To create a table, you need to specify the table and column names with their respective data types.

Here is an example:

CREATE TABLE users (
   id INTEGER PRIMARY KEY,
   name TEXT,
   email TEXT
);

This creates a table called users with three columns – id, name and email.

The id column is an integer primary key, name is a text field and email is also a text field.

Data Types

The commonly used data types in SQLite are:

• INTEGER – Used to store whole numbers.
• TEXT – Used to store text strings.
• REAL – Used to store floating point values.
• BLOB – Used to store binary data.
• DATE – Used to store date values.
• DATETIME – Used to store date and time.

For example:

CREATE TABLE books (
  id INTEGER PRIMARY KEY, 
  title TEXT,
  pub_year INTEGER,
  price REAL,
  thumbnail BLOB
);

This creates a books table with different data type columns.

Some other data types in SQLite include BOOLEAN, NUMERIC, VARCHAR, etc.

Adding a Primary Key While Creating Table

When creating a table, you can define a column as the Primary Key. This column uniquely identifies each row in the table.

To define a column as a primary key, use PRIMARY KEY constraint:

CREATE TABLE users (
  id INTEGER PRIMARY KEY, 
  name TEXT
);

Here the id column is defined as the primary key.

A primary key column has the following properties:

• Uniquely identifies each row in the table.
• It cannot contain NULL values.
• It cannot be modified once inserted.

You can also define a primary key over multiple columns as follows:

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER,
  PRIMARY KEY(order_id, customer_id)  
);

Here the combination of order_id and customer_id together forms the primary key.

AUTO INCREMENT in SQLite Table

If you want the primary key column values to generate automatically, you can use the AUTOINCREMENT keyword:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT 
);

This will automatically generate a unique integer whenever a new row is inserted.

Adding a NOT NULL Constraint

By default, a column can store NULL values. To prevent NULL values in a column, you can use the NOT NULL constraint:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

Now the name column cannot contain any NULL values.

UNIQUE Constraint While Creating Table

To prevent duplicate values in a column, you can use the UNIQUE constraint:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE
);

This ensures no two rows can have the same email value.

Setting DEFAULT Values

You can specify a default value for a column using the DEFAULT keyword:

CREATE TABLE users (
  name TEXT,
  registration_date TEXT DEFAULT '0000-00-00'
);

If no value is specified for registration_date during insertion, it will take the value ‘0000-00-00’.

CHECK Constraint

CREATE TABLE users (
  age INTEGER,
  CHECK (age >= 18)
);

This ensures only users with age 18 or above can be added.

Foreign Key Constraint While Creating Table

A foreign key allows you to establish a link between two tables. It refers to the primary key of another table:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  FOREIGN KEY(customer_id) REFERENCES customers(id)
);

Here customer_id is a foreign key referring to the primary key in the customers table.

This ensures that a non-existing customer can make no order.

Some points about foreign keys:

• The referred table (customers) must exist before creating the foreign key.
• The referred column (id) must be a primary key in the referred table (customers).
• Deleting a record that a foreign key refers to is not allowed.

Creating Table as Select (CTAS)

You can create a table by selecting data from another table using the CREATE TABLE AS syntax:

CREATE TABLE top_customers AS
SELECT * FROM customers
WHERE points > 3000;

This creates a new table top_customers by copying data from the customers table based on a condition.

Check if Table Exists

You can check if a table already exists before creating it using the IF NOT EXISTS clause:

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY, 
  name TEXT
);

This will create the table only if it does not exist. If it already exists, nothing happens.

Create Temporary Table

Temporary tables are useful when you need some temporary storage. They are dropped automatically when the database connection is closed.

To create a temporary table, use the TEMP or TEMPORARY keyword:

CREATE TEMPORARY TABLE temp_users (
  id integer PRIMARY KEY,
  name TEXT
);

This creates a temporary table temp_users that can be used for temporary storage.

Explicit Column Definition

By default, if you do not specify the column datatypes, they are inferred automatically by SQLite.

For example:

CREATE TABLE users (
  id, 
  name
);

Here id and name columns will be automatically typed as INTEGER and TEXT, respectively.

It is recommended to define the datatypes as: explicitly

CREATE TABLE users (
  id INTEGER,
  name TEXT 
);

This makes the table schemas clearer.

Insert Data

Once you have created a table, you can insert data into it using the INSERT statement:

INSERT INTO users VALUES (1, 'John');

INSERT INTO users(name, id) VALUES ('Mary', 2);

You can insert data either by specifying column names or not. Make sure the values are given in the order of the columns in the table.

Constraints Overview

Some key points about various constraints:

• NOT NULL – Ensures column cannot have NULL values
• UNIQUE – Ensures no duplicate values in the column
• PRIMARY KEY – Uniquely identifies rows (cannot be NULL)
• FOREIGN KEY – Creates a link between two tables
• CHECK – Limits values allowed in column
• DEFAULT – Provides a default value if none is provided

Using constraints properly ensures data integrity in the database.

Alter Table

You can modify an existing table using the ALTER TABLE statement. Some examples:

Add new column

ALTER TABLE users ADD COLUMN gender TEXT;

Drop column

ALTER TABLE users DROP COLUMN gender;

Rename column

ALTER TABLE users RENAME COLUMN name TO fullname;

Change column data type

ALTER TABLE users ALTER COLUMN fullname TEXT;

Add constraints

ALTER TABLE users ADD CHECK (age >= 18);

This allows changing the table schema even after initial creation.

Drop Table

To delete an existing table, use the DROP TABLE statement:

DROP TABLE users;

This will remove the table and all its data permanently. Make sure to backup first if required.

Summary

• Use CREATE TABLE to create new tables by specifying columns and datatypes.
• INTEGER, TEXT, REAL, BLOB are common datatypes.
• PRIMARY KEY uniquely identifies rows.
• AUTOINCREMENT generates auto integer values for primary key.
• NOT NULL prevents NULL values in column.
• UNIQUE prevents duplicates in column.
• FOREIGN KEY links two tables together.
• CHECK limits values allowed in column.
• DEFAULT provides a default value.
• Use constraints properly to enforce data integrity.
• ALTER TABLE allows modifying existing table.
• DROP TABLE deletes a table permanently.

I hope this detailed overview helps you learn how to create and manage tables in SQLite. Let me know if you have any other questions!