SQLite - CREATE Table

SQLite - Creating Tables

The CREATE TABLE statement is used to create a new table in a database. Creating a table involves defining its name, the columns it contains, and the data type for each column. Modern database design also involves defining constraints to ensure data integrity.

Modern Syntax and Best Practices

Here is the modern, recommended syntax for the CREATE TABLE statement, including common constraints.

CREATE TABLE [IF NOT EXISTS] table_name (
   column1_name data_type [PRIMARY KEY] [constraints],
   column2_name data_type [constraints],
   ...,
   [FOREIGN KEY (column_name) REFERENCES other_table (other_column_name)]
);

IF NOT EXISTS is an optional but useful clause that prevents an error if the table already exists. Let’s break down the key components:

Data Types (Type Affinity)

SQLite uses a dynamic type system called ‘type affinity’. A column can store any type of value, but the affinity is the recommended type. The main affinities are:

• INTEGER: For whole numbers. A column defined as INTEGER PRIMARY KEY will auto-increment, which is the standard way to create unique IDs.
• TEXT: For text strings.
• REAL: For floating-point (decimal) numbers.
• BLOB: For storing data exactly as it was input (e.g., images, files).
• NUMERIC: Can store values of any format, but tries to convert them to INTEGER or REAL if possible.

Constraints

Constraints are rules enforced on the data in a table. They are crucial for maintaining data integrity.

• PRIMARY KEY: Uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values.
• FOREIGN KEY: Links two tables together. It’s a key used to enforce a link between data in two tables, preventing actions that would destroy links between them.
• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.
• DEFAULT value: Provides a default value for a column when none is specified.
• CHECK (condition): Ensures that all values in a column satisfy a specific condition.

Example: A Modern Approach

Let’s create a relational schema for a small company, with DEPARTMENTS and EMPLOYEES tables. This demonstrates modern best practices like foreign keys.

-- First, create the DEPARTMENTS table
sqlite> CREATE TABLE DEPARTMENTS (
   id INTEGER PRIMARY KEY, -- Auto-incrementing primary key
   name TEXT NOT NULL UNIQUE
);

-- Now, create the EMPLOYEES table with a foreign key
sqlite> CREATE TABLE EMPLOYEES (
   id INTEGER PRIMARY KEY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL CHECK(age > 18),
   address TEXT,
   salary REAL DEFAULT 0.00,
   department_id INTEGER,
   FOREIGN KEY (department_id) REFERENCES DEPARTMENTS (id)
);

Verifying Table Creation

You can verify that your tables were created successfully using the .tables command in the SQLite command-line tool.

sqlite> .tables
DEPARTMENTS  EMPLOYEES

To see the complete CREATE TABLE statement for a table, use the .schema command. This is very useful for debugging.

sqlite> .schema EMPLOYEES
CREATE TABLE EMPLOYEES (
   id INTEGER PRIMARY KEY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL CHECK(age > 18),
   address TEXT,
   salary REAL DEFAULT 0.00,
   department_id INTEGER,
   FOREIGN KEY (department_id) REFERENCES DEPARTMENTS (id)
);