SQL Tables Explained: CREATE, ALTER, DROP, and Examples

Before you can query anything, a table has to exist to hold it. Creating one means saying three things: what the columns are called, what kind of value each column holds, and which column identifies a row.

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    PRIMARY KEY (one or more columns)
);

Three statements cover a table's whole life:

• CREATE TABLE names the columns and their types, and leaves the table empty.
• ALTER TABLE adds, changes or removes a column while the rows stay where they are.
• DROP TABLE removes the definition and every row in it.

What is a table?

A table is a grid the database stores and names: columns across the top, one row per thing you are keeping track of. Each column is declared with a data type, which fixes what may be written into it. One column, or a set of columns together, is marked as the primary key, the value that tells one row from every other. Here is one, created and filled:

CREATE TABLE students (
    id     INT         NOT NULL,
    name   VARCHAR(20) NOT NULL,
    age    INT         NOT NULL,
    gender VARCHAR(10),
    PRIMARY KEY (id)
);

INSERT INTO students (id, name, age, gender)
VALUES (1, 'Sam',  32, 'Male'),
       (2, 'Bob',  45, 'Male'),
       (3, 'Anne', 23, 'Female');

students is the table name, id, name, age and gender are the columns, and the values 1, Sam, 32 and Male together form one row. Keys have articles of their own: primary key in SQL and what is a foreign key.

CREATE TABLE, column by column

The statement is a name followed by a parenthesized list, one line per column, plus any constraint that covers more than a single column. Three parts of the students statement are worth reading closely:

• VARCHAR(20) is a variable-length string, and the 20 is the longest value it accepts, not the space the column always uses.
• NOT NULL refuses a row that leaves the column empty; gender carries no such constraint, so it may be left empty.
• PRIMARY KEY (id) makes id unique and non-empty at once, and a table has at most one of them.

The types themselves are where the engines part company:

SQL Server counting bytes is the row that catches people out: VARCHAR(20) there holds fewer than twenty characters as soon as one of them is multibyte, and the char and varchar page calls reading the number as characters a common misconception. For one engine's full list worked through an example, see CREATE TABLE in PostgreSQL.

DROP TABLE, and the statement to use instead

DROP TABLE deletes the definition and every row in one step:

DROP TABLE students;

There is no confirmation, and the rows are not recoverable from inside SQL afterwards, so the name in that statement deserves a second look before you run it. When the intention is to empty a table rather than remove it, the statement you want is a DELETE with no WHERE clause:

DELETE FROM students;

The table, its columns and its primary key survive that, and it can be inserted into again immediately.

ALTER TABLE on a table that already has rows

ALTER TABLE changes the structure of a table that exists, without rewriting the rows it holds. Three clauses do the column work, each applied to students below:

• ADD adds a column, which starts out empty on every row already there.
• MODIFY, or ALTER COLUMN, changes the definition of a column that exists.
• DROP COLUMN removes a column and everything stored in it.

Constraints are added and dropped with the same statement.

Add a column with ALTER TABLE ADD

A new column is a name and a type, exactly as in CREATE TABLE:

ALTER TABLE students ADD address VARCHAR(100);

The three rows that were already there have nothing to put in the new column, so they hold NULL. Give the column a DEFAULT if you want a value there instead. A new NOT NULL column needs one, because the existing rows must hold something legal the moment the column appears.

MySQL and PostgreSQL accept the word COLUMN after ADD and treat it as noise; SQL Server has no such keyword there. Leaving it out is the spelling that runs on all three.

Change a column with MODIFY, ALTER COLUMN or CHANGE

Changing an existing column is the one operation the three engines spell differently:

Making name shorter and letting it stand empty looks like this:

-- MySQL
ALTER TABLE students MODIFY name VARCHAR(15) NULL;

-- PostgreSQL
ALTER TABLE students ALTER COLUMN name TYPE VARCHAR(15);
ALTER TABLE students ALTER COLUMN name DROP NOT NULL;

-- SQL Server
ALTER TABLE students ALTER COLUMN name VARCHAR(15) NULL;

Whatever you leave out of a whole definition is dropped, so write NOT NULL into the new one where you want to keep it. MySQL also has CHANGE, which renames the column in the same statement and so takes the old name and the new one. The reference pages are the MySQL 8.4 ALTER TABLE, PostgreSQL 18 ALTER TABLE and SQL Server ALTER TABLE statements.

Shortening a column only succeeds while every value stored in it still fits. On MySQL that depends on one setting: the MySQL 8.4 ALTER TABLE page warns that shortening a string column may truncate values, and tells you to enable strict SQL mode to stop the statement from succeeding where the conversion would lose data. Strict SQL mode is part of the default SQL mode in MySQL 8.4, so a server nobody has reconfigured refuses the change rather than truncating. Sam, Bob and Anne are well under fifteen characters, so nothing is in the way here; on a real table, check the longest value first.

Remove a column with ALTER TABLE DROP

Dropping a column takes the column and everything stored in it, and leaves the rows themselves alone:

ALTER TABLE students DROP COLUMN address;

students is back to four columns and still holds its three rows. The word COLUMN is optional in MySQL and PostgreSQL and required in SQL Server, so writing it is the habit that travels.

A view or a foreign key that reads the column changes the outcome. Put the column back and build a view on it:

ALTER TABLE students ADD address VARCHAR(100);

CREATE VIEW student_addresses AS
SELECT id, address
FROM students;

ALTER TABLE students DROP COLUMN address;

PostgreSQL rejects that last statement while student_addresses exists. Its ALTER TABLE page says you need CASCADE where anything outside the table depends on the column, a foreign key reference or a view among them:

ALTER TABLE students DROP COLUMN address CASCADE;

That version drops the view together with the column, which is a heavier change than the statement looks. Check what refers to a column before you remove it, on any engine.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Create and alter the same table in DbSchema

Typing CREATE TABLE is quick for one table and slow for thirty, and a diagram is easier to read back than a folder of scripts. DbSchema connects to the database, reverse-engineers what is there, and draws the tables and their foreign keys on a canvas. Right-click the canvas and choose "New Table" to add one; double-click a table header to open the Table Dialog, where columns, indexes and foreign keys are edited in a form rather than in DDL. A foreign key is drawn by dragging one column onto the column it points at.

Which of those edits reaches the database depends on the mode you are in. Connected, DbSchema executes each schema change against the live database as you make it; connecting, reverse-engineering, the diagram and the SQL editor are all in the free Community Edition. Disconnected, the changes collect in the design model file and you apply them later from "Schema → Synchronize Model with Database", which shows the generated DDL before it runs. Saving that model file and the synchronization dialog are Pro, and together they put a review step between the diagram you edited and the database.

Install DbSchema from https://dbschema.com/download.html, open a connection to a database you already have, and draw students on the canvas instead of typing the CREATE TABLE at the top of this page. That much is Community Edition work, and the model file and the synchronization dialog are the Pro part.