CREATE TABLE gives a table its name, then a list between brackets of columns
and table constraints, then options that apply to the whole table. Most of the
differences between dialects are in the words used inside that list.
The shape of the statement
CREATE TABLE orders (
id integer NOT NULL,
customer integer,
total numeric(10,2) DEFAULT 0,
CONSTRAINT orders_pk PRIMARY KEY (id),
CONSTRAINT orders_customer FOREIGN KEY (customer) REFERENCES customers (id)
);Each item is separated by a comma. An item is a column or a table constraint. Common
variations on the first line are CREATE TEMPORARY TABLE and
CREATE TABLE IF NOT EXISTS. A table can also be made from a query with
CREATE TABLE … AS SELECT, in which case there is no list of columns.
Columns
A column is a name, a type and any number of attributes, in almost any order.
| Attribute | Meaning |
|---|---|
NOT NULL, NULL | Whether the column may be empty. Without either, it may be, except in a primary key. |
DEFAULT value | What the column holds when a row does not say. Can be a literal or an expression, such as CURRENT_TIMESTAMP. |
PRIMARY KEY | The column alone is the table’s key. |
UNIQUE | No two rows may share a value. |
REFERENCES t (c) | A foreign key on this column. |
CHECK (expr) | A condition each row must meet. |
COLLATE name | How the column’s text sorts and compares. |
COMMENT 'text' | MySQL only: a note kept with the column. |
Values that a database makes for itself are written differently in each dialect:
| Dialect | Automatic number | Computed column |
|---|---|---|
| MySQL, MariaDB | int NOT NULL AUTO_INCREMENT | GENERATED ALWAYS AS (expr) STORED or VIRTUAL |
| PostgreSQL | A sequence and DEFAULT nextval('…'), or GENERATED … AS IDENTITY | GENERATED ALWAYS AS (expr) STORED |
| SQLite | INTEGER PRIMARY KEY AUTOINCREMENT | GENERATED ALWAYS AS (expr), STORED or VIRTUAL |
| SQL Server | [int] IDENTITY(1,1) | [x] AS (expr), optionally PERSISTED |
Types
A type can be several words and can have arguments in brackets. These are all single types:
varchar(255)
int unsigned
enum('red','green')
character varying(80)
timestamp without time zone
double precision
numeric(10,2)
text[]
[nvarchar](max)SQLite needs no type at all for a column, and accepts any words as one.
Keys and constraints
Table constraints name their columns in brackets, and can be given a name with
CONSTRAINT name.
| Constraint | Form |
|---|---|
| Primary key | PRIMARY KEY (a, b) |
| Unique | UNIQUE (a); in MySQL UNIQUE KEY name (a) |
| Check | CHECK (a > 0) |
| Foreign key | FOREIGN KEY (a) REFERENCES t (b), with optional ON DELETE and ON UPDATE |
| Index, in MySQL | KEY name (a), INDEX name (a), FULLTEXT KEY, SPATIAL KEY |
The actions after ON DELETE and ON UPDATE are
CASCADE, SET NULL, SET DEFAULT,
RESTRICT and NO ACTION. Foreign keys are how a dump shows
relationships between tables.
In SQL Server scripts a key part has ASC or DESC after it, and
the key says CLUSTERED or NONCLUSTERED. In MySQL an index part
can have a prefix length, such as name(10).
Table options
Options follow the closing bracket.
| Dialect | Options |
|---|---|
| MySQL, MariaDB | ENGINE=InnoDB, AUTO_INCREMENT=5, DEFAULT CHARSET=utf8mb4, COLLATE=utf8mb4_bin, COMMENT='…', and partitioning |
| PostgreSQL | INHERITS (parent), PARTITION BY …, WITH (…), TABLESPACE name |
| SQLite | WITHOUT ROWID, STRICT |
| SQL Server | ON [PRIMARY], the filegroup, and TEXTIMAGE_ON [PRIMARY] |
After CREATE TABLE
Not everything about a table is in its CREATE TABLE. Tools move some of it
later in the dump, so that loading is faster and tables can refer to each other:
ALTER TABLE ONLY public.orders
ADD CONSTRAINT orders_pkey PRIMARY KEY (id);
ALTER TABLE orders ADD KEY total_idx (total);
CREATE INDEX orders_customer_idx ON public.orders USING btree (customer);So to know a table’s full structure, a reader has to go through the file for
ALTER TABLE and CREATE INDEX too.