SQL CREATE TABLE: Define a New Table Step by Step

Every table you have queried so far had to be created first. CREATE TABLE defines a new table: its name, its columns, the type of data each column holds, and the rules the data must follow.

Syntax#

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

Each column is defined on its own line with a name, a data type, and optional constraints (rules).

A first table#

Let's create a table of online courses:

CREATE TABLE courses (
  course_id    INTEGER PRIMARY KEY,
  title        VARCHAR(100) NOT NULL,
  level        VARCHAR(20) DEFAULT 'beginner',
  price        DECIMAL(8, 2),
  published_on DATE
);

Reading it line by line:

Now add two rows and look at the result:

INSERT INTO courses (course_id, title, price)
VALUES (1, 'SQL Basics', 0);

INSERT INTO courses (course_id, title, level, price, published_on)
VALUES (2, 'Advanced SQL', 'advanced', 999.00, '2026-10-01');

SELECT * FROM courses;

course_id | title        | level    | price  | published_on
----------+--------------+----------+--------+-------------
1         | SQL Basics   | beginner | 0.00   | NULL
2         | Advanced SQL | advanced | 999.00 | 2026-10-01

The first course did not specify a level, so the DEFAULT filled in "beginner". It did not specify published_on either, and that column has no default, so it is NULL.

Rules are enforced#

Try inserting a course without a title:

INSERT INTO courses (course_id) VALUES (3);

ERROR: NOT NULL constraint failed: courses.title

The table's definition protects the data from the very first row. You will learn all the rules in Constraints, and the available types in Data Types.

Auto-generated ids#

Usually you want the database to create ids for you. Each database has its own syntax:

-- PostgreSQL (and the SQL standard)
CREATE TABLE students (
  student_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       VARCHAR(100) NOT NULL
);

-- MySQL
CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL
);

-- SQL Server
CREATE TABLE students (
  student_id INT IDENTITY(1,1) PRIMARY KEY,
  name       NVARCHAR(100) NOT NULL
);

-- SQLite
CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,   -- automatically generated
  name       TEXT NOT NULL
);

Then insert without the id: INSERT INTO students (name) VALUES ('Asha');

A table with a foreign key#

Tables are linked with foreign keys. Here every enrollment must point to an existing student and course:

CREATE TABLE enrollments (
  student_id  INTEGER NOT NULL REFERENCES students(student_id),
  course_id   INTEGER NOT NULL REFERENCES courses(course_id),
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

PRIMARY KEY (student_id, course_id) is a composite key: the pair must be unique, so a student cannot enrol in the same course twice.

IF NOT EXISTS#

Creating a table that already exists is an error. IF NOT EXISTS makes the statement do nothing instead, which is useful in setup scripts:

CREATE TABLE IF NOT EXISTS courses (
  course_id INTEGER PRIMARY KEY,
  title     VARCHAR(100) NOT NULL
);

It does not compare definitions. If a courses table exists with different columns, it is left exactly as it is. Supported by PostgreSQL, MySQL, SQLite and Oracle 23c+. SQL Server has no such clause; there you write IF OBJECT_ID('courses') IS NULL CREATE TABLE courses (...).

CREATE TABLE AS SELECT#

You can create a table from a query's result, columns and data together:

CREATE TABLE expensive_products AS
SELECT name, price
FROM products
WHERE price > 10000;

SELECT * FROM expensive_products;

name    | price
--------+------
Laptop  | 55000
Monitor | 12000

This is great for quick snapshots and reports. The new table copies column names and types, but not primary keys, defaults, or other constraints. SQL Server uses SELECT ... INTO new_table FROM ... instead.

Naming tips#

• Use lowercase snake_case: order_items, not OrderItems or order items.
• Plural table names (customers) and singular column names (name) are the most common convention.
• Avoid reserved words (order, user, group) as names.
• Name id columns clearly: customer_id is easier to join on than id everywhere.

Common mistakes#

Practice#

CREATE TABLE books (
  book_id  INTEGER PRIMARY KEY,
  title    VARCHAR(200) NOT NULL,
  author   VARCHAR(100) NOT NULL,
  pages    INTEGER,
  in_stock INTEGER DEFAULT 0
);

INSERT INTO books (book_id, title, author, pages)
VALUES (1, 'Learning SQL', 'A. Writer', 350);

SELECT * FROM books;

book_id | title        | author    | pages | in_stock
--------+--------------+-----------+-------+---------
1       | Learning SQL | A. Writer | 350   | 0

Key takeaway#