CREATE TABLE in SQL

Before you can store a single row, you have to describe the shape of your data — what columns exist, what type each holds, and what rules keep the values sane. CREATE TABLE is the statement that does this, and the care you put into it decides whether your database quietly protects you or lets garbage in. This tutorial builds a real schema from scratch and explains every choice.

We will create the employees and orders tables that appear throughout the SQL learning hub, so the examples connect to everything else you study.

The basic syntax

A CREATE TABLE statement names the table and lists its columns inside parentheses. Each column gets a name, a data type, and optional constraints:

CREATE TABLE employees (
    emp_id      INT,
    name        VARCHAR(100),
    department  VARCHAR(50),
    salary      DECIMAL(10, 2),
    hire_date   DATE
);

Read it top to bottom: this creates a table called employees with five columns. emp_id holds whole numbers, name and department hold variable-length text up to their limits, salary holds a fixed-precision number, and hire_date holds a calendar date. Run it once and the empty table is ready for rows, which you would then query with the SELECT statement.

Choosing the right data type

The data type controls what a column can store and how much space it uses. Picking well matters for both correctness and performance.

Use INT for whole numbers like ids and counts, and BIGINT when values may exceed about two billion. Use VARCHAR(n) for text of variable length up to n characters — names, emails, statuses — and CHAR(n) only for fixed-length codes like a two-letter country code. Use DECIMAL(p, s) for exact numbers such as money, where p is total digits and s is digits after the decimal point. Use DATE, TIMESTAMP, or DATETIME for temporal values, and BOOLEAN for true/false flags.

Common mistake: Storing money in FLOAT or DOUBLE. Those types use binary floating point, so a value like 0.10 cannot be represented exactly and errors accumulate across sums. Always use DECIMAL for currency — DECIMAL(10, 2) stores values up to 99,999,999.99 with no rounding surprises.

Adding constraints that protect your data

A type says what kind of value fits. A constraint says what values are allowed. Constraints are where a table stops being a passive container and starts defending itself against bad data.

Here is the employees table again, now with real rules:

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50)  NOT NULL DEFAULT 'Unassigned',
    salary      DECIMAL(10, 2) CHECK (salary >= 0),
    email       VARCHAR(150) UNIQUE,
    hire_date   DATE NOT NULL
);

Each constraint earns its place. PRIMARY KEY makes emp_id unique and non-null, giving every employee one guaranteed identifier. NOT NULL forces a value, so you can never insert an employee with no name or no hire date. DEFAULT 'Unassigned' fills department automatically when you do not supply one. CHECK (salary >= 0) rejects negative salaries outright. UNIQUE on email guarantees no two employees share an address — and the database enforces it even under concurrent inserts.

Pro tip: Push rules into the schema, not just the application. If a negative salary is impossible, a CHECK constraint makes it impossible for every program that ever touches the table, including scripts and manual fixes. Application-only validation always eventually gets bypassed.

Connecting tables with foreign keys

Real data is related. An order belongs to an employee, and the database can enforce that link with a foreign key. This is the backbone of relational design and of database normalization.

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    emp_id      INT NOT NULL,
    order_date  DATE NOT NULL DEFAULT (CURRENT_DATE),
    amount      DECIMAL(10, 2) NOT NULL CHECK (amount > 0),
    status      VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

The FOREIGN KEY line ties orders.emp_id to employees.emp_id. Now the database will refuse to insert an order for an employee id that does not exist, and by default it will refuse to delete an employee who still has orders. That single rule prevents orphaned records — orders pointing at nobody — which is one of the most common data-integrity bugs in beginner projects.

You can control what happens on deletion with referential actions like ON DELETE CASCADE (delete the orders too) or ON DELETE SET NULL (blank the reference). Choose deliberately based on your business rules.

Auto-generating keys

You rarely want to assign ids by hand. Databases can generate them for you, though the keyword differs by system. In PostgreSQL and modern SQL standard syntax:

CREATE TABLE departments (
    dept_id     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_name   VARCHAR(50) NOT NULL UNIQUE
);

In MySQL you would write dept_id INT AUTO_INCREMENT PRIMARY KEY instead. This difference between database engines is exactly the kind of detail explored in the MySQL vs PostgreSQL comparison, and it is worth knowing which dialect your project uses.

Modifying and removing tables

Requirements change, and you rarely recreate a table from scratch. ALTER TABLE adjusts an existing one:

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'General';

To remove a table entirely — structure and all its data — use DROP TABLE employees. Use it carefully; it is not reversible outside a transaction. To empty a table but keep its structure, TRUNCATE TABLE orders is faster than deleting every row.

Interview note: Expect "What is the difference between DROP, TRUNCATE, and DELETE?" DROP removes the whole table, TRUNCATE removes all rows but keeps the table, and DELETE removes selected rows and can be rolled back inside a transaction. Being crisp on this trio is an easy way to score early points.

Composite and named constraints

Sometimes a rule spans more than one column, or you want to name a constraint so error messages are readable. You can declare constraints at the table level, after the column list, instead of inline:

CREATE TABLE order_items (
    order_id    INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL DEFAULT 1,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id),
    CONSTRAINT chk_qty CHECK (quantity > 0),
    CONSTRAINT fk_order FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
);

The PRIMARY KEY (order_id, product_id) here is a composite key — the combination must be unique, so a product can appear once per order but across many orders. Naming constraints (pk_order_items, chk_qty) means that when an insert violates one, the database tells you exactly which rule failed, which saves real debugging time in a large schema. This link-table pattern is the standard way to model a many-to-many relationship, a design you will meet again when you study how relational databases store data.

Order of columns and constraints matters for readability

Databases do not care about column order for correctness, but humans do. A consistent convention — primary key first, then required business columns, then optional ones, then timestamps, and table-level constraints at the bottom — makes schemas readable across a whole team. When you or a teammate opens a table definition six months later, that predictability is worth more than it sounds. Treat your CREATE TABLE statements as documentation of the data model, because that is exactly what they become.

A complete, correct schema

Put together, a well-designed CREATE TABLE gives you strong types, keys that identify rows, foreign keys that keep tables consistent, and constraints that reject impossible values. That structure is what lets everything above it — joins, aggregations, and transactions — work reliably.

Design the table with the same care you would design a class in code: decide what each column means, what type fits, and what rules must always hold. Data outlives applications, so a schema you get right today keeps paying off for years. When you build backend services in the Java Full Stack with AI program, these definitions become the entities your whole application is built on, which is why learning to write them well is one of the best early investments you can make in SQL.