Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL CREATE TABLE statement

Reference for the SQL CREATE TABLE statement: columns, data types, PRIMARY KEY, NOT NULL, DEFAULT, CHECK, foreign keys, IF NOT EXISTS and dialect differences.

CREATE TABLE defines a new table: its name, its columns with their data types, and the rules (constraints) the data must obey. It is a data definition (DDL) statement, so it changes the schema rather than the rows. Right after creation the table is empty.

A column definition is a name, a type and optional column constraints such as NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY or CHECK. Rules that involve several columns, such as a composite primary key or a foreign key, are written as table constraints after the columns. Using IF NOT EXISTS makes the statement do nothing instead of failing when the table is already there.

SQL
CREATE TABLE tags (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

Try it

SQL A table with common constraints

Press Run (or Ctrl + Enter) to see the result here.

SQL Composite key, foreign key and rejected rows (the last insert fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The final insert repeats a key pair and fails with UNIQUE constraint failed: wishlist_items.customer_id, wishlist_items.product_id.

SQL IF NOT EXISTS and inspecting the result

Press Run (or Ctrl + Enter) to see the result here.

The second statement is silently ignored, so notes keeps its first definition. The last query lists the columns SQLite stored.

Syntax

SQL
CREATE [ TEMPORARY ] TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type [ column_constraint ... ]
  [, ... ]
  [, table_constraint ... ]
);

-- column constraints
NOT NULL | NULL | DEFAULT value | UNIQUE | PRIMARY KEY
CHECK (condition) | REFERENCES other_table (column)

-- table constraints
[ CONSTRAINT name ] PRIMARY KEY (col, ...)
[ CONSTRAINT name ] UNIQUE (col, ...)
[ CONSTRAINT name ] CHECK (condition)
[ CONSTRAINT name ] FOREIGN KEY (col, ...) REFERENCES other_table (col, ...)
Part Description
table_name The new table's name. Optionally schema-qualified (schema.table) where schemas exist.
column_name data_type One column and its type. Every column needs a name; in SQLite the type is optional.
DEFAULT The value used when an INSERT leaves the column out.
NOT NULL Rejects NULL. Columns allow NULL unless declared otherwise (or part of a primary key in most databases).
PRIMARY KEY Unique, not-null identifier of a row. Only one per table; may span several columns.
UNIQUE, CHECK, REFERENCES Uniqueness, a custom condition and a foreign key. See UNIQUE, CHECK, FOREIGN KEY.
IF NOT EXISTS Skips creation if a table of that name exists. It does not compare definitions.

Result: no rows; the statement succeeds or raises an error such as "table already exists".

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
Auto-numbered key INTEGER PRIMARY KEY GENERATED ... AS IDENTITY AUTO_INCREMENT IDENTITY(1,1) GENERATED ... AS IDENTITY (12c+)
IF NOT EXISTS Yes Yes Yes No (check OBJECT_ID) 23ai+
Types enforced Loosely (affinity), STRICT option Strictly Strictly Strictly Strictly
Text type TEXT text, varchar(n) VARCHAR(n), TEXT varchar(n), nvarchar(n) VARCHAR2(n)
Foreign keys enforced Only with PRAGMA foreign_keys = ON Always InnoDB: always Always Always
Default expression DEFAULT (expr) needs parentheses DEFAULT expr DEFAULT (expr) for expressions DEFAULT expr DEFAULT expr
SQL
-- PostgreSQL
CREATE TABLE members (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     text NOT NULL UNIQUE,
  joined_at timestamptz NOT NULL DEFAULT now()
);

-- MySQL
CREATE TABLE members (
  id        BIGINT AUTO_INCREMENT PRIMARY KEY,
  email     VARCHAR(255) NOT NULL UNIQUE,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

-- SQL Server
CREATE TABLE members (
  id        bigint IDENTITY(1,1) PRIMARY KEY,
  email     varchar(255) NOT NULL UNIQUE,
  joined_at datetime2 NOT NULL DEFAULT SYSDATETIME()
);

-- Oracle
CREATE TABLE members (
  id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     VARCHAR2(255) NOT NULL UNIQUE,
  joined_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

Usage notes

  • Declare constraints in the table, not in the application. NOT NULL, CHECK and keys keep every program honest, including ones you have not written yet.
  • Choose a primary key for every table. A surrogate integer id is the usual choice; use a composite key for pure link tables.
  • Foreign keys in SQLite are off by default. Run PRAGMA foreign_keys = ON for each connection. The sample databases here also leave them off unless a TryIt turns them on.
  • Column order matters only for INSERT without a column list. Always list the columns in INSERT statements.
  • Name constraints with CONSTRAINT name to get readable error messages and to drop them later.
  • SQLite types are advisory unless the table is declared STRICT; a TEXT value can be stored in an INTEGER column when it cannot be converted. Other databases reject it.
  • Changing a table later is possible with ALTER TABLE, but SQLite supports only a subset; design carefully first.
  • To copy structure and data from a query use CREATE TABLE AS. To remove a table use DROP TABLE.