CREATE TABLE

Creates a table.

CREATE TABLE table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
    ...
);

Creates the table only if it does not already exist.

CREATE TABLE IF NOT EXISTS table_name (
    column1 data_type [constraint],
    ...
);

The DECIMAL(p, s) fixed-point type uses p for total digits and s for digits after the decimal point. For example, DECIMAL(8, 2) stores up to 6 integer digits and 2 decimal digits (e.g., 123456.78).

SQLite has a unique approach to data types — internally it only supports four types: INTEGER, REAL, TEXT, and BLOB. Writing VARCHAR(100) is valid syntax but the character limit is not enforced.

MySQL and MariaDB share nearly identical syntax. MariaDB is a fork of MySQL and is highly compatible. Use AUTO_INCREMENT for auto-incrementing columns.

The following example creates the members table shown below.

Creates a members table.

CREATE TABLE IF NOT EXISTS members (
    member_id     INT           NOT NULL AUTO_INCREMENT,
    name          VARCHAR(100)  NOT NULL,
    email         VARCHAR(255)  NOT NULL,
    points        INT           NOT NULL DEFAULT 0,
    registered_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id)
);

Query OK, 0 rows affected (0.03 sec)

Verifies the table definition.

SHOW COLUMNS FROM members;

+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| member_id     | int          | NO   | PRI | NULL              | auto_increment |
| name          | varchar(100) | NO   |     | NULL              |                |
| email         | varchar(255) | NO   |     | NULL              |                |
| points        | int          | NO   |     |                 0 |                |
| registered_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
+---------------+--------------+------+-----+-------------------+----------------+

PostgreSQL uses SERIAL or GENERATED AS IDENTITY for auto-incrementing columns. Use TIMESTAMP for date-time values, and BOOLEAN is natively supported.

Creates a members table using SERIAL for auto-increment.

CREATE TABLE IF NOT EXISTS members (
    member_id     SERIAL        NOT NULL,
    name          VARCHAR(100)  NOT NULL,
    email         VARCHAR(255)  NOT NULL,
    points        INT           NOT NULL DEFAULT 0,
    registered_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id)
);

CREATE TABLE

Verifies the table definition.

\d members

                                    Table "public.members"
    Column      |          Type          | Nullable |                  Default
----------------+------------------------+----------+-------------------------------------------
 member_id      | integer                | not null | nextval('members_member_id_seq'::regclass)
 name           | character varying(100) | not null |
 email          | character varying(255) | not null |
 points         | integer                | not null | 0
 registered_at  | timestamp              | not null | CURRENT_TIMESTAMP

SQLite is a lightweight database that requires no server. Its data type handling is flexible — declaring a column as INTEGER PRIMARY KEY is all you need for auto-increment. Date-time values are managed as strings (TEXT type).

Creates a members table. INTEGER PRIMARY KEY enables auto-increment. Date-time values are managed as strings (TEXT type).

CREATE TABLE IF NOT EXISTS members (
    member_id     INTEGER  PRIMARY KEY,
    name          TEXT     NOT NULL,
    email         TEXT     NOT NULL,
    points        INTEGER  NOT NULL DEFAULT 0,
    registered_at TEXT     NOT NULL DEFAULT (datetime('now'))
);

(No output. Returns to the prompt on success.)

Verifies the table definition.

.schema members

CREATE TABLE members (
    member_id     INTEGER  PRIMARY KEY,
    name          TEXT     NOT NULL,
    email         TEXT     NOT NULL,
    points        INTEGER  NOT NULL DEFAULT 0,
    registered_at TEXT     NOT NULL DEFAULT (datetime('now'))
);

For how to run .sql files, see Creating and Running .sql Files.

CREATE TABLE is classified as DDL (Data Definition Language). When creating a table, it is important to choose an appropriate data type for each column. For string data, choose between VARCHAR and TEXT based on your use case: use VARCHAR when there is a known character limit (such as names or email addresses), and TEXT when the length is open-ended (such as article bodies or descriptions).

Adding IF NOT EXISTS prevents an error if the table already exists. This is useful in migration scripts where you want idempotent behavior.

Even with the same CREATE TABLE statement, auto-increment, data types, and date-time handling differ by database. SQLite in particular only has four internal types (INTEGER, REAL, TEXT, BLOB), which requires a different design mindset compared to other databases. When learning SQL, always be aware of which database you are working with.

For details on constraints (NOT NULL, UNIQUE, PRIMARY KEY, etc.), see NOT NULL / UNIQUE / DEFAULT and PRIMARY KEY / FOREIGN KEY. To modify an existing table definition, see ALTER TABLE.