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

A table stores data in rows and columns, and each column holds one type of value. For example, a students table holds one row per student, with a name and an age. The database can refuse a value that doesn't fit its column.

CREATE TABLE makes a table, ALTER TABLE changes it, and DROP TABLE removes it. The three statements have this shape:

CREATE TABLE table_name (
  column_name data_type constraint,
  ...
);

ALTER TABLE table_name action;

DROP TABLE table_name;

• table_name names the table.
• column_name data_type declares one column and the kind of value it holds, such as int or varchar(20).
• constraint is an optional rule for the column, such as NOT NULL or PRIMARY KEY.
• action is the change that ALTER TABLE makes, such as adding, changing or dropping a column.

Create a sample table of three students:

CREATE TABLE students (
  id   int PRIMARY KEY,
  name varchar(20) NOT NULL,
  age  int
);

INSERT INTO students VALUES
  (1, 'Sam',  32),
  (2, 'Bob',  45),
  (3, 'Anne', 23);

Run the examples in order. Each statement changes students, and the next statement starts from the changed table.

Every statement below ran on three database engines: PostgreSQL 18.6, MySQL 9.1 and SQLite 3.50. All three gave the same result unless an engine is named. The SQL Server statements follow Microsoft's documentation only.

The table has three columns, each with a name and a data type, and one row per student:

Practice this in Lesson 1 of the SQL tutorial: you work with the tables, rows and columns of a shop database in your browser, with exercises.

Data types

A column's data type decides which values the column accepts. For example, name is varchar(20) and accepts text of at most 20 characters. Try to insert a longer name:

INSERT INTO students (id, name, age)
VALUES (4, 'Alexandra Konstantinou', 19);

PostgreSQL refuses the row:

ERROR:  value too long for type character varying(20)

Alexandra Konstantinou has 22 characters. MySQL refuses the row too, with "Data too long for column 'name' at row 1".

SQLite stores the row, because SQLite ignores the length in varchar(20) (Datatypes In SQLite). If you follow along on SQLite, remove the row with DELETE FROM students WHERE id = 4.

The type names also differ between engines:

SQL Server's varchar(20) holds fewer than 20 characters when some characters take more than one byte (char and varchar).

PRIMARY KEY

A primary key is the column whose value identifies each row. For example, every student has a different id. The database refuses a row with an id that already exists, or with no id at all.

INSERT INTO students (id, name, age)
VALUES (1, 'Kim', 20);

PostgreSQL refuses the duplicate id:

ERROR:  duplicate key value violates unique constraint "students_pkey"
DETAIL:  Key (id)=(1) already exists.

Sam already has id 1, so Kim's row isn't stored. MySQL answers "Duplicate entry '1' for key 'students.PRIMARY'". SQLite answers "UNIQUE constraint failed: students.id".

A table has at most one primary key. Primary key in SQL explains how to choose the primary key.

On SQLite, an int PRIMARY KEY column accepts NULL. Declare the column NOT NULL as well, and SQLite then refuses NULL (CREATE TABLE).

Other constraints add their own rules to a column:

• NOT NULL refuses NULL, the marker for a missing value. name is NOT NULL.
• UNIQUE refuses a value that is already in the column.
• CHECK (age >= 0) refuses a row whose age is below 0.
• DEFAULT 'none' fills in 'none' when an INSERT leaves the column out.
• FOREIGN KEY links a column to the key of another table. What is a foreign key explains foreign keys.

CREATE TABLE AS

CREATE TABLE AS creates a table from the rows and columns that a SELECT returns. For example, you can copy the students over 30 into a new table, adults.

CREATE TABLE adults AS
SELECT id, name FROM students WHERE age > 30;

SELECT * FROM adults;

Sam and Bob are over 30, so adults holds their two rows, with the two columns that the SELECT named. Anne is 23 and stays out.

The copy has no primary key. On all three engines, adults accepts a second row with id 1 without an error.

SQL Server has no CREATE TABLE AS. On SQL Server, make the same copy with SELECT id, name INTO adults FROM students WHERE age > 30. SELECT INTO explains this statement.

ALTER TABLE ADD

ALTER TABLE changes a table that already holds rows. ADD puts a new column at the end of the table. For example, you can add an email column to students.

ALTER TABLE students ADD email varchar(50);

SELECT * FROM students;

The three rows keep their values, and email holds NULL in every row. An INSERT or an UPDATE can fill in the email later.

PostgreSQL, MySQL and SQLite also accept ADD COLUMN email. SQL Server accepts only ADD email.

MODIFY and ALTER COLUMN

MODIFY and ALTER COLUMN change a column that already exists. For example, you can change name from varchar(20) to varchar(30). Each engine spells the change differently:

-- PostgreSQL
ALTER TABLE students ALTER COLUMN name TYPE varchar(30);

-- MySQL
ALTER TABLE students MODIFY name varchar(30) NOT NULL;

-- SQL Server
ALTER TABLE students ALTER COLUMN name varchar(30) NOT NULL;

Insert Alexandra's row again:

INSERT INTO students (id, name, age)
VALUES (4, 'Alexandra Konstantinou', 19);

SELECT * FROM students;

Alexandra's name fits into varchar(30), so her row is stored.

PostgreSQL's TYPE changes only the type, so name stays NOT NULL.

On MySQL and SQL Server, write NOT NULL again. Both engines replace the whole definition of the column, so without NOT NULL, name accepts NULL.

SQLite can't change a column's type. Instead, copy the rows into a new table, as SQLite's ALTER TABLE page describes.

DROP COLUMN

DROP COLUMN removes a column and every value stored in it. For example, you can remove the email column from students.

ALTER TABLE students DROP COLUMN email;

SELECT * FROM students;

students is back to three columns and keeps its four rows. The emails are gone for good.

In DROP COLUMN, write the word COLUMN on every engine. SQL Server requires it, and the other engines accept it.

TRUNCATE TABLE

TRUNCATE TABLE deletes every row of a table. For example, you can empty adults and then count its rows.

TRUNCATE TABLE adults;

SELECT count(*) AS row_count FROM adults;

The count runs without an error, because the adults table and its columns remain.

On SQLite, run DELETE FROM adults instead. SQLite has no TRUNCATE. A DELETE with no WHERE clause empties the table on every engine.

DROP TABLE

DROP TABLE removes a table with its columns and every row in it. For example, you can drop adults once you no longer need it.

DROP TABLE adults;

SELECT * FROM adults;

The SELECT fails, because adults no longer exists:

ERROR:  relation "adults" does not exist

MySQL and SQLite refuse the SELECT too.

DROP TABLE asks for no confirmation. MySQL makes the drop permanent at once, and a ROLLBACK can't undo it.

The three ways to remove rows differ in what they keep:

In a script that runs more than once, write IF EXISTS. DROP TABLE IF EXISTS adults does nothing when adults is already gone, and raises no error. CREATE TABLE IF NOT EXISTS students does nothing when students already exists.

SQL Server has had DROP TABLE IF EXISTS since SQL Server 2016. SQL Server's CREATE TABLE has no IF NOT EXISTS.

What goes wrong

A NOT NULL column on a table with rows

A new NOT NULL column needs a value in every existing row. Add email again, this time as NOT NULL:

ALTER TABLE students ADD email varchar(50) NOT NULL;

PostgreSQL refuses it:

ERROR:  column "email" of relation "students" contains null values

SQLite refuses the statement too, with "Cannot add a NOT NULL column with default value NULL". MySQL 9.1 adds the column and fills each existing row with an empty string.

Give the column a DEFAULT, as in ADD email varchar(50) NOT NULL DEFAULT 'none'. Then all three engines fill the existing rows with 'none'. On MySQL, the first statement already added email, so remove that column first with ALTER TABLE students DROP COLUMN email.

A view that reads the column

A view is a saved SELECT that you query like a table. Create a view that reads age, then drop the age column:

CREATE VIEW student_ages AS
SELECT id, age FROM students;

ALTER TABLE students DROP COLUMN age;

PostgreSQL refuses to drop the column:

ERROR:  cannot drop column age of table students because other objects depend on it
DETAIL:  view student_ages depends on column age of table students
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

SQLite refuses the drop too. MySQL drops the column, and every later SELECT from student_ages fails with error 1356, because the view reads a column that is gone.

On PostgreSQL, DROP COLUMN age CASCADE drops the view together with the column, as PostgreSQL's ALTER TABLE page describes. Before you drop a column, check which views read it.

Create and change tables in DbSchema

DbSchema connects to the database and draws the database's tables on a diagram. Choose Table → Create Table in the toolbar, type the table name, and add each column with its data type in the Columns tab. Double-click a table header to open the Edit Table dialog, where you add a column in the Columns tab (Tables, Columns and Indexes).

Press SQL at the bottom of the dialog to read the table's CREATE TABLE statement before you press OK. While DbSchema is connected, OK runs the change in the database at once.

Offline, the change stays in the design model, DbSchema's own copy of the schema. Schema synchronization applies the change to the database later, after you review the SQL.

Download DbSchema at https://dbschema.com/download.html, connect it to your database, and create students with Table → Create Table instead of typing its statement. Connecting, the diagram, creating and editing tables, and the SQL Editor are in the free Community Edition. Saving the model to a file and schema synchronization need DbSchema Pro.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download