SQL CREATE TABLE Explained for Beginners (Examples)
Learn SQL CREATE TABLE step by step: columns, data types, primary keys and NOT NULL, with copy-paste examples. A friendly guide for absolute beginners.
Learn SQL CREATE TABLE step by step: columns, data types, primary keys and NOT NULL, with copy-paste examples. A friendly guide for absolute beginners.
How do you create a table in SQL? You use the CREATE TABLE statement. You give the table a name, then list its columns, and for each column you say what kind of data it holds. That is the whole idea. In this guide you will learn it step by step, with small examples you can copy and run.
No experience is needed. Every new word is explained the first time it appears.
A database is a place where a computer stores information. Inside a database, information lives in tables. A table looks like a spreadsheet. It has columns (the headings, like name or email) and rows (one entry each, like one customer).
SQL stands for Structured Query Language. It is the language you use to talk to a database. Before you can save any data, you must first build the table that will hold it. That is what CREATE TABLE does.
Syntax means the shape a command must follow. Here is the shape of CREATE TABLE:
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
column3 data_type
);Line 1 says you want to create a table and gives it a name. The round brackets hold the list of columns. Each column has a name and a data type. A data type tells the database what kind of value goes in that column, such as a number or text. Columns are separated by commas, and the whole command ends with a semicolon.
Let us build a simple table to store customers.
CREATE TABLE customers (
id INT,
name VARCHAR(100),
email VARCHAR(255),
age INT
);Line 1 creates a table called customers. Line 2 adds a column named id that stores whole numbers (INT). Line 3 adds name, which stores text up to 100 characters (VARCHAR(100)). Line 4 adds email, text up to 255 characters. Line 5 adds age, another whole number. The table is empty for now, but its shape is ready.
Choosing the right data type keeps your data clean. A number column will not accept the word hello. These are the types you will use most. Names vary a little between databases such as MySQL, PostgreSQL and SQL Server, but these work almost everywhere.
| Data type | What it stores | Example value |
|---|---|---|
| INT | Whole numbers | 42 |
| DECIMAL(10,2) | Exact numbers with decimals, good for money | 19.99 |
| VARCHAR(n) | Text up to n characters | 'Maria' |
| TEXT | Long text with no small limit | A blog post |
| DATE | A calendar date | '2026-11-11' |
| BOOLEAN | True or false | TRUE |
A primary key is a column whose value is different for every row. It works like an ID card number: it lets you find exactly one row. A table should almost always have one.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
age INT
);The only change is PRIMARY KEY after the id column. Now the database refuses to store two customers with the same id. It also refuses an empty id.
Typing a new id every time is boring and easy to get wrong. Databases can count for you. The keyword is different in each database. MySQL uses AUTO_INCREMENT. PostgreSQL uses GENERATED ALWAYS AS IDENTITY.
-- MySQL
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
-- PostgreSQL
CREATE TABLE customers (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100)
);Lines starting with two dashes are comments; the database ignores them. In both versions the id becomes 1 for the first row, 2 for the second, and so on. You never type it yourself. Use the version that matches the database you installed.
You can add rules, called constraints, to a column. They stop bad data from getting in. Here are the three most useful ones:
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT,
country VARCHAR(50) DEFAULT 'Unknown',
created_on DATE
);The name column must always be filled in. The email column must be filled in and must be different for every customer. The age column is optional, so it may be empty. If you add a customer without a country, the database saves the word Unknown. Because we gave created_on no rules, it simply accepts a date or nothing.
After you create a table, add one row and read it back to prove it works.
INSERT INTO customers (id, name, email, age)
VALUES (1, 'Maria', 'maria@example.com', 30);
SELECT * FROM customers;The INSERT command adds one row. It lists the columns first, then the matching values in the same order. The SELECT command reads data back. The star means all columns. You should see Maria with the country set to Unknown, because of the DEFAULT rule.
If you run CREATE TABLE twice with the same name, you get an error. To avoid that, add IF NOT EXISTS.
CREATE TABLE IF NOT EXISTS customers (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);Now the database creates the table only if it is missing. If it is already there, it quietly does nothing. This is handy in scripts you run many times.
Now you can build a table. The next skill is filling it with data and changing it. Read our guide on SQL INSERT, UPDATE and DELETE to learn how. If you use PostgreSQL, the PostgreSQL create table guide covers the extra details of that database.
Next, learn how to add, change and remove rows in the table you just made.
Read SQL INSERT, UPDATE, DELETEUsing PostgreSQL? See the PostgreSQL version of this lesson.
PostgreSQL create table guideYou write CREATE TABLE, then the table name, then round brackets holding a list of columns. Each column has a name and a data type, separated by commas. End the command with a semicolon.
Yes, SQL allows it. But it is not a good idea. Without a primary key you cannot reliably tell rows apart, which makes updating or deleting one specific row hard.
INT stores whole numbers like 5 or 100 and can be used in maths. VARCHAR stores text such as names or emails, with a maximum length you choose.
Use DROP TABLE table_name;. Be careful, because this removes the table and all its data for good. If you only want to remove the rows, delete them instead.
Yes. The ALTER TABLE command lets you add, rename or remove columns later. So a small mistake in your first design is easy to fix.
Need help designing a database for your website or app? The Fepiq team can help you plan and build it.
Talk to FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.