SQL CREATE TABLE: How to Define a Database Table (with Examples)

• 30 June 2026
• by: Renata Cardoso
• in: MySQL/MariaDB
• Tags: create table, SQL, sql create table
• note: no comments

If you have ever opened a fresh database and stared at the blinking cursor wondering where everything *lives*, this is the answer. Tables are where your data actually sits, and the SQL CREATE TABLE statement is how you build them. Get comfortable here and the rest of SQL starts to click, because almost everything you will ever do later, inserting rows, running queries, joining data, depends on tables you defined well at the start.

I want to walk you through this the way I would if we were sitting side by side. We will start with the plain syntax, then look at the data types and constraints that do the real work, and finish with a complete, realistic example you can adapt for your own projects. No prior experience assumed, just curiosity.

This guide is part of our broader MySQL/MariaDB: The Complete Guide for Website Owners, so once you finish here you will know exactly where to go next.

Key Takeaways
• `CREATE TABLE` defines a new table by naming its columns, assigning each a data type, and attaching constraints (rules the database enforces).
• The most important decisions are the data types and constraints, not the column names, because those define what your data can and cannot be.
• A PRIMARY KEY uniquely identifies each row; pairing it with AUTO_INCREMENT lets the database assign IDs for you.
• Constraints like NOT NULL, UNIQUE, and DEFAULT make whole categories of bad data impossible.
• Use `IF NOT EXISTS` to avoid errors, `DESCRIBE` to inspect structure, `ALTER TABLE` to change it later, and `DROP TABLE` with great care.

What does the CREATE TABLE statement actually do?

When you run `CREATE TABLE`, you are telling the database three things at once: what the table is called, what columns it holds, and what type and rules apply to each column. The database stores this definition (its *schema*) and then enforces it on every single row you ever insert.

Here is the simplest possible version:

“`sql CREATE TABLE users ( id INT, name VARCHAR(100), email VARCHAR(255) ); “`

Read it top to bottom. We are creating a table named `users`. It has three columns: `id`, which holds whole numbers (`INT`); `name`, which holds text up to 100 characters; and `email`, text up to 255 characters. The parentheses wrap the column list, each column sits on its own line for readability, and commas separate them. That is the whole shape of every `CREATE TABLE` statement, no matter how complex it gets later.

Run it and the database confirms:

“`text Query OK, 0 rows affected (0.04 sec) “`

Zero rows because the table is empty, it is just a structure waiting to be filled. So far so good. But notice we have not told the database much about the *rules* yet, and that is where the real craft begins.

Which data types should you use for each column?

Every column needs a data type, and choosing the right one matters more than beginners expect. The type decides what the column can store, how much space it uses, and how the database treats the value when you sort, compare, or do math. Pick `VARCHAR` for a date and you lose the ability to sort chronologically; pick `INT` for a phone number and the leading zero disappears.

Here are the types you will reach for most often in MySQL and MariaDB:

Data type	Stores	Example value	Use it for
`INT`	Whole numbers (approx -2.1B to 2.1B)	`42`	IDs, counts, ages
`BIGINT`	Very large whole numbers	`9000000000`	Big IDs, large counters
`VARCHAR(n)`	Text up to `n` characters	`’Renata’`	Names, emails, short text
`TEXT`	Long text (up to ~65KB)	`’A long article…’`	Articles, comments, descriptions
`DATE`	A calendar date	`’2026-06-29’`	Birthdays, due dates
`DATETIME`	Date and time	`’2026-06-29 14:30:00’`	Event timestamps
`TIMESTAMP`	Date and time (auto-updates available)	`’2026-06-29 14:30:00’`	Created/updated tracking
`DECIMAL(p,s)`	Exact decimal numbers	`19.99`	Prices, money, precise values
`BOOLEAN` / `TINYINT(1)`	True/false (0 or 1)	`1`	Flags, yes/no fields

A few notes from experience. For money, always use `DECIMAL`, never a floating-point type, because `DECIMAL(10,2)` stores `19.99` exactly while floats introduce tiny rounding errors that compound over thousands of transactions. For text, prefer `VARCHAR(n)` with a sensible limit over `TEXT` when you know the field is short, it is faster and signals intent. And `BOOLEAN` in MySQL is really just `TINYINT(1)` under the hood, so do not be surprised when you see `0` and `1` rather than `true` and `false`.

The guiding question for each column is simple: *what is the tightest type that still fits every legitimate value?* Answer that honestly and most of your schema design is already done.

What is a PRIMARY KEY and why does every table need one?

A PRIMARY KEY is the column that uniquely identifies each row. No two rows can share the same primary key value, and it can never be empty. Think of it as the social security number for your data: one row, one unique identifier, no exceptions.

“`sql CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) ); “`

Now `id` is the unique handle for every user. When you later want to update one specific person or join this table to another, you reference that `id`. Without a primary key, the database has no reliable way to tell two otherwise-identical rows apart, and queries become slow and ambiguous. Give every table a primary key. It is one of the few rules I would call absolute.

How does AUTO_INCREMENT assign IDs automatically?

Typing in your own ID numbers is tedious and error-prone. AUTO_INCREMENT tells the database to assign the next number for you, automatically, every time you insert a row.

“`sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) ); “`

Now you simply leave `id` out when inserting, and the database fills it in: the first row gets `1`, the next `2`, and so on, never reusing a number even if you delete rows. This pairing of `INT AUTO_INCREMENT PRIMARY KEY` is the single most common way to define a primary key in MySQL and MariaDB. You will use it constantly.

How do constraints like NOT NULL, DEFAULT, and UNIQUE protect your data?

This is where a schema stops being a list of columns and becomes a set of *guarantees*. Constraints are rules the database enforces on your behalf, and they are the difference between a clean dataset and a perpetual cleanup job.

• `NOT NULL` forbids empty values. The database will refuse to store a row missing this field.
• `DEFAULT` supplies a value automatically when you do not provide one.
• `UNIQUE` forbids duplicate values across rows, perfect for emails or usernames.

“`sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, is_active BOOLEAN DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); “`

Read what we just promised the database. Every user must have a name and an email (`NOT NULL`). No two users can share an email (`UNIQUE`). New users are active by default (`DEFAULT 1`), and the moment of creation is recorded automatically (`DEFAULT CURRENT_TIMESTAMP`). Try to insert a user with no email or a duplicate one, and the database stops you cold:

“`text ERROR 1062 (23000): Duplicate entry ‘[email protected]’ for key ’email’ “`

That error is a *feature*. The database just prevented a bug you would otherwise have to catch in application code, on every form, forever.

Here is the thing I most want you to take from this guide. The most consequential decisions in `CREATE TABLE` are not the column names, they are the data types and constraints, because those define what your data *can* and *cannot* be, forever. A schema is a set of promises the database enforces. Declare a column `NOT NULL` and the database will refuse to ever store a row missing it. Make it `UNIQUE` and duplicates become impossible. Pick `INT` instead of `VARCHAR` and you have decided whether `’007’` is the number `7` or the text `’007’`. Beginners treat `CREATE TABLE` as “naming some columns” and pick loose types (`TEXT` for everything, no constraints) to avoid thinking, then spend years fighting bad data the schema should have prevented. The professional habit is the opposite: invest thought up front in the tightest correct type and the strongest honest constraint for each column, because a well-designed schema makes whole categories of bugs *impossible* (you cannot insert a duplicate email, a null name, or a string where a date belongs) while a sloppy one lets garbage in that your application then has to defend against forever. The table definition is where you encode the rules of your data. Get it right at `CREATE` time, because the database enforces exactly what you declared, and re-declaring it later is the expensive part.

How do FOREIGN KEYs connect tables together?

Real applications rarely have just one table. A FOREIGN KEY links a column in one table to the primary key of another, modeling relationships like “this order belongs to that user.”

“`sql CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, total DECIMAL(10,2) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ); “`

The `FOREIGN KEY (user_id) REFERENCES users(id)` line tells the database that every `user_id` in `orders` must match a real `id` in `users`. Try to create an order for a user that does not exist and the database rejects it. This keeps your data *consistent*, no orphaned orders pointing at users who were never there. We will go deeper on relationships in the database basics guide, but know that this is how tables join into a coherent whole.

What does a complete, realistic CREATE TABLE look like?

Let us put everything together into a `products` table, the kind you might build for a real store. Watch how each column earns its type and constraints.

“`sql CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, stock INT NOT NULL DEFAULT 0, is_active BOOLEAN NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); “`

Every choice here is deliberate. The `sku` is `UNIQUE` because two products should never share one. `price` is `DECIMAL(10,2)` so money stays exact. `description` is `TEXT` because it can be long and we do not know the length. `stock` defaults to `0` so a new product is never accidentally null. And `updated_at` uses `ON UPDATE CURRENT_TIMESTAMP`, which refreshes the timestamp automatically every time the row changes, a small touch that saves real effort later. This is what “investing thought up front” looks like in practice.

How do you avoid errors with IF NOT EXISTS?

If you run a `CREATE TABLE` statement for a table that already exists, the database throws an error and stops. In setup scripts that run repeatedly, that is annoying. Add `IF NOT EXISTS` and the statement quietly does nothing when the table is already there:

“`sql CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL ); “`

“`text Query OK, 0 rows affected, 1 warning (0.00 sec) “`

A warning instead of an error means your script keeps running. This is a small habit that makes deployment and migration scripts far more reliable.

How do you view a table’s structure after creating it?

Once a table exists, you will often want to confirm exactly how it is defined. Two commands do this. `DESCRIBE` gives a clean summary:

“`sql DESCRIBE products; “`

“`text +————-+—————+——+—–+——————-+

| Field | Type | Null | Key | Default | +————-+—————+——+—–+——————-+

| id | int | NO | PRI | NULL |

| sku | varchar(50) | NO | UNI | NULL |

| name | varchar(150) | NO | | NULL |

| price | decimal(10,2) | NO | | 0.00 |

| stock | int | NO | | 0 | +————-+—————+——+—–+——————-+ “`

For the exact statement that built the table, including constraints and engine details, use `SHOW CREATE TABLE`:

“`sql SHOW CREATE TABLE products; “`

This returns the full `CREATE TABLE` definition the database is actually using, which is invaluable when you inherit a database and need to understand what someone else built.

DarazHost: build and manage your database tables with confidence

Designing a schema is a lot easier when the tools around it are dependable. DarazHost hosting includes MySQL and MariaDB with phpMyAdmin, so you can create and design tables visually or by writing SQL directly, whichever fits how you think. Fast SSD storage keeps table operations quick even as your data grows, and automatic backups mean that building and altering your schema is safe, you can experiment knowing you can always restore. You get the database tools to design your data structure with confidence, backed by 24/7 support whenever a constraint or query has you stuck.

How do you change a table after creating it with ALTER TABLE?

Schemas evolve. When you need to add a column, change a type, or add a constraint to an existing table, `ALTER TABLE` is the tool:

“`sql ALTER TABLE products ADD COLUMN weight DECIMAL(6,2); ALTER TABLE products MODIFY COLUMN name VARCHAR(200) NOT NULL; “`

The first line adds a new `weight` column; the second widens `name` and makes it required. `ALTER TABLE` is powerful, but remember the lesson from earlier: changing a type or constraint on a table that already holds thousands of rows can be slow and can fail if existing data violates the new rule. That is precisely why thoughtful design at `CREATE` time saves you pain. Altering an empty table is trivial; altering a populated one is the expensive part.

How do you delete a table with DROP TABLE?

When you truly want a table gone, `DROP TABLE` removes it and *all its data* permanently:

“`sql DROP TABLE IF EXISTS old_logs; “`

Handle this command with respect. There is no undo, the table and every row in it disappear instantly. I add `IF EXISTS` to avoid errors when the table may already be gone, and I always double-check the table name before pressing enter. On a live database, make sure you have a recent backup first. This is one place where a moment of caution prevents a very bad afternoon.

Frequently asked questions

What is the difference between CHAR and VARCHAR? `CHAR(n)` always reserves exactly `n` characters, padding shorter values with spaces, while `VARCHAR(n)` stores only the characters you actually use, up to `n`. Use `CHAR` for fixed-length data like two-letter country codes, and `VARCHAR` for everything variable, like names and emails. `VARCHAR` is the right default for most text.

Can a table have more than one PRIMARY KEY? No, a table can have only one primary key, but that key can span multiple columns (a *composite key*), which together must be unique. If you need other columns to be unique individually, use the `UNIQUE` constraint instead, you can have many of those.

Do I have to define AUTO_INCREMENT on the primary key? No, it is optional. `AUTO_INCREMENT` is convenient for auto-generated numeric IDs, but you can supply your own primary key values, for example a `UNIQUE` product code or a UUID stored as a string, if those fit your data better.

What happens if I forget NOT NULL on a required column? The column will accept empty (`NULL`) values, which means rows can be saved with that field missing. Later you may discover important data is absent and have to clean it up. Adding `NOT NULL` at `CREATE` time prevents the problem entirely rather than fixing it after the fact.

Is CREATE TABLE the same in MySQL, MariaDB, and other databases? The core syntax shown here works in both MySQL and MariaDB, which are closely related. Other systems like PostgreSQL or SQLite are very similar but differ in details (for example, `AUTO_INCREMENT` becomes `SERIAL` or `AUTOINCREMENT`). The concepts, columns, types, and constraints, are universal across all SQL databases.

---

You now know how to define a table that protects your data instead of just storing it. Start with a clear primary key, choose the tightest correct type for every column, and add the constraints that encode your real rules. Do that, and the rest of SQL becomes a pleasure rather than a fight. For the bigger picture of how databases fit together, return to our MySQL/MariaDB: The Complete Guide for Website Owners.