How to Create a Table in SQL

The basic syntax for creating a table in SQL

To create a table in SQL, you write a CREATE TABLE statement that names the table and defines each column with a data type. The simplest version looks like this:

CREATE TABLE table_name (column_name data_type);

In practice, you almost always create multiple columns at once. Here's a real example that creates a table called employees with four columns:

CREATE TABLE employees (employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), hire_date DATE);

Each column name comes first, followed by its data type. The column definitions are separated by commas, and the entire statement ends with a semicolon.

Key Takeaways

A CREATE TABLE statement names the table and lists each column with its data type, separated by commas and ending with a semicolon.
Common data types include INT for whole numbers, VARCHAR for text of varying length, DATE for calendar dates, and DECIMAL for numbers with decimal places.
You can add constraints like NOT NULL (column must have a value) and PRIMARY KEY (column uniquely identifies each row) directly in the CREATE TABLE statement.
Most SQL databases — MySQL, PostgreSQL, SQL Server, SQLite — use the same basic CREATE TABLE syntax, though some constraints and data types vary slightly between them.

Choosing the right data type for each column

The data type tells SQL what kind of information the column will hold and how much space to reserve. Picking the right type prevents errors and makes your database run faster.

INT stores whole numbers with no decimal point — use it for counts, IDs, or ages. VARCHAR(n) stores text up to a maximum length you specify in the parentheses — VARCHAR(50) holds up to 50 characters. DATE stores calendar dates in YYYY-MM-DD format. DECIMAL(p,s) stores numbers with decimal places, where p is the total number of digits and s is how many come after the decimal point — DECIMAL(10,2) holds numbers like 12345678.90.

Other common types include FLOAT for approximate decimal numbers (faster but less precise than DECIMAL), BOOLEAN for true/false values, and TEXT for very long text without a character limit. If you're not sure which type fits your data, start with VARCHAR for text and INT for numbers, then adjust as you learn what your data actually contains.

Adding constraints to enforce data rules

A constraint is a rule that SQL enforces automatically. The most common constraints are NOT NULL (the column must always have a value) and PRIMARY KEY (the column's value must be unique and identify each row).

Here's an example that adds constraints:

CREATE TABLE customers (customer_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL, phone VARCHAR(20));

In this table, customer_id is the PRIMARY KEY — no two customers can have the same ID, and every row must have an ID. The email column has NOT NULL, so every customer record must include an email address. The phone column has no constraint, so it can be empty.

You can also add UNIQUE to make sure no two rows have the same value in that column (similar to PRIMARY KEY but a table can have multiple UNIQUE columns). DEFAULT sets a value that SQL uses if you don't provide one — for example, status VARCHAR(20) DEFAULT 'active' will set new rows to 'active' unless you specify otherwise.

Creating a table with a primary key and multiple constraints

Most real tables combine several constraints to keep data clean. Here's a practical example for a products table in an online store:

CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, stock_quantity INT DEFAULT 0, category VARCHAR(50));

This table requires every product to have an ID (PRIMARY KEY), a name (NOT NULL), and a price (NOT NULL). If you don't specify how many are in stock, SQL automatically sets it to 0. The category is optional.

When you run this statement in your SQL database, the table is created and ready to receive data. You can then insert rows using an INSERT statement, or query it with SELECT.

Differences between SQL databases

The CREATE TABLE syntax is nearly identical across MySQL, PostgreSQL, SQL Server, and SQLite, but a few details vary. AUTO_INCREMENT in MySQL automatically assigns the next number to a column — in PostgreSQL it's called SERIAL, and in SQL Server it's IDENTITY.

Data type names are mostly the same, but VARCHAR is the standard across all of them. Some databases support additional types — PostgreSQL has JSON and UUID types that MySQL doesn't have built-in. If you're learning SQL for a specific database, check its documentation for these small differences, but the core CREATE TABLE structure will work everywhere.

SQLite is simpler and more forgiving than the others — it doesn't enforce data types as strictly, so a column declared as INT will still accept text if you try to insert it. This makes SQLite good for learning, but the other databases will reject data that doesn't match the declared type.

Common mistakes when creating tables

The most frequent error is forgetting the semicolon at the end of the statement. SQL won't execute the command without it. The second is mismatching parentheses — every opening parenthesis must have a closing one, and the final closing parenthesis comes before the semicolon.

Another mistake is making VARCHAR too small. If you declare email VARCHAR(20), you can't store emails longer than 20 characters, and most real email addresses are longer. It's better to be generous with VARCHAR — there's no performance penalty for declaring VARCHAR(255) if you only use 50 characters.

Forgetting NOT NULL on columns that should always have data is also common. If you don't add the constraint, SQL allows empty values, and you'll end up with incomplete records that cause problems later when you try to use that data.

Frequently Asked Questions

Can I change a table after I create it?

Yes, using an ALTER TABLE statement. You can add columns, remove columns, change data types, or add constraints to an existing table. For example, ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2); adds a salary column to the employees table. However, some changes (like removing a column with data in it) may not be allowed depending on your database.

What happens if I try to create a table that already exists?

SQL will return an error saying the table already exists. To avoid this, use CREATE TABLE IF NOT EXISTS table_name — SQL will create the table only if it doesn't already exist, and do nothing if it does. This is useful in scripts that might run multiple times.

Do I have to specify a primary key?

No, but you should. A primary key uniquely identifies each row and makes it much easier to update or delete specific records later. Without one, SQL has no may provide way to tell two identical rows apart. Most real tables have a primary key, usually an ID column.

What's the difference between VARCHAR and TEXT?

VARCHAR requires you to specify a maximum length, while TEXT has no limit. VARCHAR is slightly faster for short strings and uses less storage. Use VARCHAR when you know the data has a reasonable upper bound (like a phone number or email), and TEXT when you don't know the length or expect very long content (like product descriptions).

Can I create a table with no columns?

No, SQL requires at least one column. Every table must have at least one column defined in the CREATE TABLE statement.

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.