SQL · Chapter 29 of 42

CREATE TABLE

CREATE TABLE defines a new table's structure: columns, types and constraints.

Add a PRIMARY KEY for row identity and choose types carefully — they're hard to change later.

Syntax

CREATE TABLE name (
  col1 TYPE constraints,
  col2 TYPE constraints,
  PRIMARY KEY (col1)
);

Column definition

`column_name TYPE [NOT NULL] [DEFAULT value] [UNIQUE] [PRIMARY KEY]`.

INT, BIGINT, TEXT/VARCHAR(n), DATE, TIMESTAMP, BOOLEAN, DECIMAL(p,s). Names vary slightly by database.

Example 1 (sql)

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Output

Table users created

Standard user table.

Example 2 (sql)

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL
);

Output

Table orders created

With a foreign key.

💡 Note: `BIGSERIAL` (Postgres) and `AUTO_INCREMENT` (MySQL) generate unique IDs automatically.