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.
CREATE TABLE tags ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE );
Try it
Press Run (or Ctrl + Enter) to see the result here.
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.
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
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 |
-- 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,CHECKand 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 = ONfor each connection. The sample databases here also leave them off unless a TryIt turns them on. - Column order matters only for
INSERTwithout a column list. Always list the columns inINSERTstatements. - Name constraints with
CONSTRAINT nameto get readable error messages and to drop them later. - SQLite types are advisory unless the table is declared
STRICT; aTEXTvalue can be stored in anINTEGERcolumn 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 useDROP TABLE.