How to Create a Table in SQL: A Step-by-Step Guide

What a SQL table is and why you create one

A SQL table is a container for data organized into rows and columns — think of it like a spreadsheet inside a database. Each column holds one type of information (like names, dates, or prices), and each row holds one complete record. When you create a table, you are telling the database what columns to expect, what type of data goes in each one, and which columns must always have a value.

You create a table before you can store any data. The database needs to know the structure first — the column names, the data types (text, numbers, dates), and any rules about what is allowed. Once the table exists, you can add rows of data to it, search through it, and update it.

Key Takeaways

The CREATE TABLE command defines a new table by listing each column name, its data type, and whether it can be empty.
Common data types are VARCHAR for text, INT for whole numbers, DECIMAL for prices, and DATE for calendar dates.
A PRIMARY KEY is a column that uniquely identifies each row and prevents duplicates.
You run the CREATE TABLE command once; after that, the table exists and you add data to it with INSERT commands.

The basic CREATE TABLE syntax

The command always starts with CREATE TABLE, followed by the name you want to give the table, then a list of columns inside parentheses. Here is the simplest form:

CREATE TABLE table_name ( column_name data_type, column_name data_type );

Replace table_name with what you want to call your table — use lowercase letters and underscores, no spaces. Replace column_name with the actual column names you need. Replace data_type with the type of data that column will hold. End the entire command with a semicolon.

Here is a real example. Say you want a table to store information about books:

CREATE TABLE books ( book_id INT, title VARCHAR(255), author VARCHAR(100), publication_year INT, price DECIMAL(10, 2) );

This creates a table called books with five columns: book_id (a whole number), title (text up to 255 characters), author (text up to 100 characters), publication_year (a whole number), and price (a decimal number with two places after the decimal point).

Common data types and what they hold

VARCHAR holds text of varying length. The number in parentheses is the maximum length — VARCHAR(255) means up to 255 characters. Use this for names, addresses, descriptions, anything text-based.

INT holds whole numbers with no decimal point. Use this for counts, years, IDs, or any number that does not need decimal places.

DECIMAL holds numbers with decimal places. DECIMAL(10, 2) means 10 total digits with 2 after the decimal point — perfect for prices like 99.99 or 1234.56.

DATE holds calendar dates in the format YYYY-MM-DD. Use this for birthdays, order dates, or any date you need to search or sort by.

BOOLEAN holds true or false. Use this for yes/no questions, like whether an item is in stock or whether a customer is active.

Adding NOT NULL and PRIMARY KEY rules

By default, any column can be empty (NULL). If you want to require a value in a column, add NOT NULL after the data type. If you want a column to be the unique identifier for each row — the thing that makes each record different from every other record — mark it as PRIMARY KEY.

Here is the books table with rules added:

CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(100) NOT NULL, publication_year INT, price DECIMAL(10, 2) );

Now book_id is the PRIMARY KEY, meaning each book must have a unique ID number and the database will prevent duplicates. title and author are NOT NULL, meaning you cannot add a book without filling in both of those fields. publication_year and price can be left empty if needed.

PRIMARY KEY also makes searching faster — the database indexes it automatically so lookups by ID are nearly when ready.

AUTO_INCREMENT for ID columns

If you want the database to assign a new ID number automatically every time you add a row, use AUTO_INCREMENT. This saves you from having to pick ID numbers yourself and guarantees they will never repeat.

CREATE TABLE books ( book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, author VARCHAR(100) NOT NULL, publication_year INT, price DECIMAL(10, 2) );

Now when you add a new book, you do not provide a book_id — the database assigns the next available number. The first book gets ID 1, the second gets ID 2, and so on.

AUTO_INCREMENT is specific to MySQL and some other databases. In PostgreSQL, use SERIAL instead. In SQL Server, use IDENTITY. The idea is the same: let the database count for you.

Running the command and checking if it worked

Copy the CREATE TABLE command into your database client (the software you use to talk to the database — this might be MySQL Workbench, pgAdmin, SQL Server Management Studio, or a web interface). Paste it, then press Enter or click the Run button.

If the command succeeds, you will see a message like "Query OK" or "Table created successfully". If it fails, you will see an error message. Common errors are: a table with that name already exists, a column name is misspelled or repeated, or a data type is not recognized in your particular database system.

To see all the tables in your database, run SHOW TABLES; (in MySQL) or SELECT table_name FROM information_schema.tables; (in PostgreSQL). To see the structure of a table you just created, run DESCRIBE books; (in MySQL) or \d books (in PostgreSQL).

Frequently Asked Questions

What happens if I create a table with the same name twice?

The second CREATE TABLE command will fail with an error saying the table already exists. If you want to replace it, use DROP TABLE books; first to delete the old one, then run CREATE TABLE again. Be careful — DROP TABLE deletes the table and all the data in it.

Can I change a table after I create it?

Yes. Use ALTER TABLE to add columns, remove columns, or change data types. For example, ALTER TABLE books ADD COLUMN isbn VARCHAR(20); adds a new column called isbn. The exact syntax varies by database system.

What is the difference between VARCHAR and CHAR?

VARCHAR stores only the text you give it, so "John" takes 4 characters. CHAR reserves a fixed number of spaces, so CHAR(10) always uses 10 characters even if you only store "John". Use VARCHAR for most text because it saves space. Use CHAR only when every value is exactly the same length, like a two-letter state code.

Do I need a PRIMARY KEY?

Technically no, but you should almost always have one. A PRIMARY KEY prevents duplicate rows and makes searching much faster. It is the standard way databases keep records organized and unique.

What if I need a column that can be empty sometimes?

Do not add NOT NULL to that column. By default, columns allow NULL (empty) values. Only add NOT NULL to columns that must always have data.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.