SQL CREATE, ALTER and DROP TABLE

Design your own tables: choose data types, create tables, change them with ALTER TABLE and remove them safely.

Lesson 10 of 102Intermediate17 min read

Until now you've worked with tables that already existed. In real projects, someone has to design them: decide which tables are needed, which columns each one has and what type of data every column holds. That design work is done with Data Definition Language (DDL), the part of SQL that creates and changes the structure of a database rather than the data inside it.

The three DDL statements you'll use most are CREATE TABLE, which builds a new table, ALTER TABLE, which changes an existing one, and DROP TABLE, which deletes a table completely. Good table design pays off for years: it keeps data consistent, makes queries simple and stops whole categories of bugs before they happen.

In this lesson you'll build the shop's orders table from scratch, choose sensible data types, add a column to an existing table and learn how to remove tables without regretting it.

CREATE TABLE

SQL
CREATE TABLE reviews (
    id          INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    rating      INTEGER NOT NULL,
    comment     TEXT,
    created_at  DATE
);
  • After the table name, list each column in brackets: its name, its data type and any constraints.
  • Separate the columns with commas, with no comma after the last one.
  • Use lowercase snake_case names such as created_at, and make table names plural nouns: reviews, orders.

Add IF NOT EXISTS after CREATE TABLE to skip creation quietly when the table is already there, which makes set-up scripts safe to run twice.

Choosing data types

Kind of dataCommon typesExample values
Whole numbersINTEGER, INT, BIGINT42, -7
Exact decimals (money)DECIMAL(10, 2), NUMERIC24.50
Approximate decimalsREAL, FLOAT, DOUBLE3.14159
Short textVARCHAR(100)'Leeds'
Long textTEXTA product review
True or falseBOOLEANTRUE
Dates and timesDATE, TIMESTAMP'2024-07-01'

Use DECIMAL for money. Approximate types like FLOAT can't store values such as 0.10 exactly, which leads to rounding errors in totals. SQLite is relaxed about types and accepts almost anything, but MySQL and PostgreSQL enforce them strictly, so it's worth choosing carefully from the start.

Auto-numbered primary keys

Most tables need an id that the database fills in automatically:

DatabaseAuto-numbered id
SQLiteid INTEGER PRIMARY KEY
MySQLid INT AUTO_INCREMENT PRIMARY KEY
PostgreSQLid SERIAL PRIMARY KEY or id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

With an auto-numbered key you leave id out of your INSERT statements, as you did in the INSERT lesson, and the database picks the next number.

ALTER TABLE and DROP TABLE

Requirements change, and tables change with them:

SQL
ALTER TABLE customers ADD COLUMN email VARCHAR(255);
ALTER TABLE customers RENAME COLUMN city TO town;
ALTER TABLE customers DROP COLUMN email;
ALTER TABLE customers RENAME TO clients;

Existing rows get NULL (or the column's default) in a newly added column. SQLite supports DROP COLUMN from version 3.35.

DROP TABLE reviews; deletes the table and every row in it, permanently. DROP TABLE IF EXISTS reviews; avoids an error when it's already gone. To empty a table but keep its structure, use DELETE FROM reviews; or, in MySQL and PostgreSQL, the faster TRUNCATE TABLE reviews;.

SQL CREATE TABLE Syntax

DDL statements

CREATE TABLE IF NOT EXISTS table_name (
    id    INTEGER PRIMARY KEY,
    col1  TYPE NOT NULL,
    col2  TYPE
);

ALTER TABLE table_name ADD COLUMN col3 TYPE;
ALTER TABLE table_name RENAME COLUMN col2 TO new_name;
DROP TABLE IF EXISTS table_name;

SQL CREATE TABLE Summary

  • CREATE TABLE name (column TYPE constraints, ...) builds a table.
  • Choose types carefully: INTEGER, DECIMAL for money, VARCHAR/TEXT, BOOLEAN, DATE.
  • Auto-numbered ids are written differently in SQLite, MySQL and PostgreSQL.
  • ALTER TABLE adds, renames or drops columns and renames tables.
  • DROP TABLE deletes a table and all its data; use IF EXISTS and be careful.

SQL CREATE TABLE Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Creating the orders table

SQL
CREATE TABLE IF NOT EXISTS orders (
    id           INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL DEFAULT 1,
    order_date   DATE    NOT NULL
);

INSERT INTO orders (customer_id, product_id, order_date)
VALUES (5, 2, '2024-07-10');

SELECT * FROM orders WHERE customer_id = 5;
Output
id | customer_id | product_id | quantity | order_date
---+-------------+------------+----------+-----------
7  | 5           | 2          | 1        | 2024-07-10
Line-by-line explanation
  1. Line 2An auto-numbered primary key. The new order gets the next id, 7.
  2. Lines 3 to 4NOT NULL means every order must name a customer and a product.
  3. Line 5DEFAULT 1 fills in quantity when the INSERT leaves it out.
  4. Lines 9 to 10Ema's first order, using the default quantity.
Example 2

Adding a column to an existing table

SQL
ALTER TABLE customers ADD COLUMN email VARCHAR(255);

UPDATE customers SET email = 'ana@example.com' WHERE id = 1;

SELECT id, name, email FROM customers WHERE id <= 2;
Output
id | name | email
---+------+----------------
1  | Ana  | ana@example.com
2  | Ben  | NULL
Line-by-line explanation
  1. Line 1Every existing row gets NULL in the new column.
  2. Line 3Fill in values afterwards with UPDATE.
  3. Line 5Ben has no email yet, so his value is still NULL.
Example 3

A new table from a query

SQL
CREATE TABLE kitchen_products AS
SELECT id, name, price
FROM products
WHERE category = 'Kitchen';

SELECT * FROM kitchen_products;
Output
id | name         | price
---+--------------+------
4  | Water bottle | 12.00
5  | Lunch box    | 8.75
Line-by-line explanation
  1. Lines 1 to 4CREATE TABLE ... AS SELECT copies the structure and rows that a query returns.
  2. Line 6It's a real, separate table now. Changes to products won't affect it.

Common SQL CREATE TABLE Mistakes

A trailing comma after the last column

Don't
CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT,
);
Do
CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

Storing money in FLOAT

Don't
price FLOAT
Do
price DECIMAL(10, 2)

Floating-point numbers can't represent values like 0.10 exactly, so totals drift by tiny amounts.

Dropping a table you meant to empty

Don't
DROP TABLE orders;          -- the table itself is gone
Do
DELETE FROM orders;         -- rows gone, table kept

SQL CREATE TABLE in Real Projects

Schema design at work

Before writing any application code, teams sketch the tables an app needs and how they relate. Web frameworks such as Django, Laravel and Rails generate CREATE and ALTER statements from code and keep them as numbered migration files, so every developer's database, the test server and the live site all have exactly the same structure.

SQL CREATE TABLE FAQ

What is the difference between VARCHAR and TEXT?

VARCHAR(n) sets a maximum length, which acts as a simple rule, such as 100 characters for a name. TEXT holds long text without a declared limit. In PostgreSQL they perform the same; in MySQL, TEXT columns have some indexing limitations. SQLite treats both as TEXT.

How do I rename a column?

Use ALTER TABLE customers RENAME COLUMN joined TO joined_on;, supported by PostgreSQL, MySQL 8 and SQLite from version 3.25. Remember to update every query, view and application that uses the old name, ideally in the same release.

How do I change a column's data type?

PostgreSQL uses ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(10, 2); and MySQL uses ALTER TABLE products MODIFY price DECIMAL(10, 2);. SQLite can't change a column type directly: you create a new table with the right structure, copy the data across and rename it. Back up first, because conversions can fail or lose data.

Should table names be singular or plural?

Either works if you're consistent. This course uses plural names such as customers and orders, which read naturally in queries like FROM orders. Many frameworks also default to plural table names. Avoid mixing styles in one database.

What is a database migration?

A small, versioned script that changes the schema, such as creating a table or adding a column, kept with the application's code. Running migrations in order brings every database, on every developer's machine and on the server, to the same structure. Frameworks such as Laravel, Django and Rails have migration tools built in.

SQL CREATE TABLE Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the statement that creates a table.

 tags (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
Fill in the blanksmedium

Complete the statement that adds a phone column to customers.

 customers  phone VARCHAR(20);
Multiple choicemedium

Which type is best for storing prices?

Predict the outputmedium

After ALTER TABLE customers ADD COLUMN vip BOOLEAN;, what is Dev's vip value?

Multiple choicehard

What's the difference between DELETE FROM t; and DROP TABLE t;?

SQL CREATE TABLE Quiz

5 questions to check what you learned.

  1. What does DDL stand for?

    • Data Download Language
    • Database Design Logic
    • Direct Data Link
    • Data Definition Language

  2. What does IF NOT EXISTS do in CREATE TABLE?

    • Deletes the old table first
    • Skips creation without an error if the table already exists
    • Creates an empty copy
    • Checks the data types

  3. How do you write an auto-numbered id in MySQL?

    • id INT AUTO_INCREMENT PRIMARY KEY
    • id SERIAL
    • id AUTO PRIMARY
    • id INTEGER NUMBERED

  4. What value do existing rows get in a newly added column without a default?

    • 0
    • An empty string
    • NULL
    • The value from the previous row

  5. What does CREATE TABLE copy AS SELECT ... do?

    • Creates a new table containing the rows the query returns
    • Creates a view
    • Renames a table
    • Links two tables together

Related SQL topics

11Constraints & KeysProtect your data with NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY and FOREIGN KEY constraints.08INSERT, UPDATE, DELETEAdd new rows, change existing data and remove rows safely, without losing anything by accident.01IntroductionWhat databases, tables, rows and columns are, and how to write your first SQL query.
Esc
↑↓ to navigateEnter to open