SQL DDL Commands: CREATE TABLE, ALTER TABLE, DROP and TRUNCATE
Create a table with CONSTRAINT chk_tags_name CHECK (CHAR_LENGTH(name) >= 2) and run INSERT INTO tags (name) VALUES ('a');. MySQL 8 rejects the row with error 3819, while MySQL 5.7 accepts the very same statement without a single warning: before version 8.0.16 MySQL parsed CHECK and silently ignored it. The structure of a table decides which data the database accepts at all – and that structure is defined with DDL commands.
DDL (Data Definition Language) is the group of SQL commands that create, change and delete database objects: databases, tables, columns, indexes and constraints. The DDL commands are CREATE, ALTER, RENAME, DROP and TRUNCATE. They work with structure, not with individual rows – rows are added, changed and deleted by DML commands (INSERT, UPDATE, DELETE).
DDL Commands in SQL
DDL has five main commands: CREATE creates an object, ALTER changes its structure, RENAME renames it, DROP deletes the object entirely, and TRUNCATE empties a table while keeping its structure. They apply to a database, a table, an index or a view – never to individual rows.
| Command | What it does | Example |
|---|---|---|
CREATE | Creates a database, table, index or view | CREATE TABLE tags (...); |
ALTER | Adds, changes and removes columns, constraints and indexes | ALTER TABLE users ADD COLUMN phone VARCHAR(20); |
RENAME | Renames a table | RENAME TABLE topics TO threads; |
DROP | Deletes an object together with its data | DROP TABLE tags; |
TRUNCATE | Deletes all rows; the table structure stays | TRUNCATE TABLE tags; |
The examples continue the forum practice database from the lesson Relational Databases and DBMS: it already has a users table (id, username, email) with Alice and Bob. Here we add threads and tags to it. The syntax is MySQL 8 with the InnoDB engine; run the commands in order in the mysql client.
DDL in MySQL cannot be rolled back
Every DDL command in MySQL performs an implicit COMMIT: it commits the open transaction and is not part of it. START TRANSACTION; DROP TABLE tags; ROLLBACK; will not bring the table back. The command itself is atomic, though: DROP TABLE tags, no_such_table drops neither table. In PostgreSQL DDL runs inside a transaction and can be rolled back – one of the notable differences between the two databases.
CREATE DATABASE: Creating a Database
A database is created with CREATE DATABASE name; and then selected with USE name;. If a database with that name already exists, the command fails; the CREATE DATABASE IF NOT EXISTS form does nothing in that case and only raises a warning.
-- Option 1: an error if the database already exists
CREATE DATABASE forum;
-- Option 2: safe to run the script again
CREATE DATABASE IF NOT EXISTS forum
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE forum; ERROR 1007 (HY000): Can't create database 'forum'; database exists
Query OK, 1 row affected, 1 warning (0.01 sec)
Database changed The first line of the output is the result of option 1 on a server where forum was already created in the DBMS lesson; the second line is option 2. A real script contains one of the two, not both: IF NOT EXISTS is for scripts you run many times. CHARACTER SET utf8mb4 is already the default in MySQL 8, but stating it explicitly protects you from servers configured differently (more on character sets in MySQL Data Types). In MySQL CREATE SCHEMA is a full synonym of CREATE DATABASE. SHOW DATABASES; lists all databases.
CREATE TABLE: Creating a Table
The CREATE TABLE command creates a table: after the name, list the columns in parentheses separated by commas (name, data type, constraints), and at the end add table-level constraints – the primary key, unique and foreign keys, CHECK. The table is created in the current database selected with USE.
CREATE TABLE [IF NOT EXISTS] table_name (
column_name TYPE [column constraints],
...,
[table constraints]
); Let’s create the forum threads table. Each thread has a title, an author from users and a creation time:
CREATE TABLE topics (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
author_id INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT fk_topics_author FOREIGN KEY (author_id) REFERENCES users (id)
); Query OK, 0 rows affected (0.03 sec) Read id INT NOT NULL AUTO_INCREMENT like this: the column id has the type INT, cannot be empty, and the database assigns the number of every new row itself. Choosing a type (INT, VARCHAR, DATETIME) is covered in MySQL Data Types. The number in INT(5) from older examples limits nothing: it is the deprecated display width, and MySQL 8.0.17+ answers it with warning 1681 (see What the Number in INT(11) Means).
Creation order matters: topics refers to users, so the parent table must exist first. How primary and foreign keys work is explained in Keys and Relationships Between Tables.
CREATE TABLE IF NOT EXISTS Does Not Change an Existing Table
What happens if you run CREATE TABLE IF NOT EXISTS for a table that already exists but with different columns? The users table from the DBMS lesson has no phone column:
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20) NULL,
PRIMARY KEY (id)
);
SHOW WARNINGS; Query OK, 0 rows affected, 1 warning (0.00 sec)
+-------+------+------------------------------+
| Level | Code | Message |
+-------+------+------------------------------+
| Note | 1050 | Table 'users' already exists |
+-------+------+------------------------------+ The command “succeeds”, yet the phone column does not appear: IF NOT EXISTS checks only the name, not the structure. Without IF NOT EXISTS you would get ERROR 1050 (42S01): Table 'users' already exists. The only way to change an existing table is ALTER TABLE.
Copying a Table: LIKE and AS SELECT
You can create a table based on another one. The two forms copy different things:
| Command | What it copies | What it does not copy |
|---|---|---|
CREATE TABLE topics_copy LIKE topics; | Columns and their attributes (NOT NULL, DEFAULT, AUTO_INCREMENT), the primary key and indexes | Data and foreign keys |
CREATE TABLE topics_copy AS SELECT * FROM topics; | Columns with their types and the rows returned by SELECT | Primary key, indexes, foreign keys, the AUTO_INCREMENT attribute |
That is why a backup before a risky ALTER is made in two steps: CREATE TABLE ... LIKE, then INSERT INTO topics_copy SELECT * FROM topics;.
Constraints: NOT NULL, DEFAULT, CHECK, UNIQUE and Keys
A constraint is a rule the database checks on every insert and update of a row: the value is not empty, is unique, satisfies a condition or refers to an existing row. A violation is rejected with an error. Constraints are declared in CREATE TABLE or added later with ALTER TABLE.
| Constraint | What it guarantees | Where it is declared | MySQL 8 error on violation |
|---|---|---|---|
NOT NULL | The column contains no NULL | Column only | ERROR 1048: Column 'title' cannot be null |
DEFAULT value | The value used when INSERT omits the column (strictly speaking, an attribute rather than a check) | Column only | – |
AUTO_INCREMENT | The database assigns the number of a new row (a MySQL attribute) | On a key column, one per table | – |
CHECK (condition) | Values satisfy the condition | Column or table | ERROR 3819: Check constraint '...' is violated |
UNIQUE | Values do not repeat | Column or table | ERROR 1062: Duplicate entry |
PRIMARY KEY | Unique and not NULL, one per table | Column or table (a composite key – table only) | ERROR 1062: Duplicate entry |
FOREIGN KEY | The value exists in the parent table | Table (in MySQL 8.4 and earlier) | ERROR 1452: Cannot add or update a child row |
PRIMARY KEY, UNIQUE and FOREIGN KEY, as well as the ON DELETE CASCADE and SET NULL actions, are explained in detail in Keys and Relationships Between Tables (section ON DELETE and ON UPDATE). What matters here is the syntax: the action goes at the end of the foreign key declaration – FOREIGN KEY (topic_id) REFERENCES topics (id) ON DELETE CASCADE.
Column-Level vs Table-Level Constraints
The same rule can be written in the column definition or as a separate line at the end of CREATE TABLE. The second form lets you name the constraint with CONSTRAINT name – the name appears in error messages, and you use it to drop the constraint later. Let’s create the tags table:
CREATE TABLE tags (
id INT NOT NULL AUTO_INCREMENT, -- column constraints
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id), -- table constraints
CONSTRAINT uq_tags_name UNIQUE (name),
CONSTRAINT chk_tags_name CHECK (CHAR_LENGTH(name) >= 2)
);
INSERT INTO tags (name) VALUES ('a'); ERROR 3819 (HY000): Check constraint 'chk_tags_name' is violated. Writing name VARCHAR(50) NOT NULL UNIQUE would forbid duplicates just as well, but MySQL would invent the index name itself (name). For a foreign key the column form does not work: MySQL 8.4 and earlier parse author_id INT REFERENCES users (id) but ignore it – the table is created without a foreign key and without a warning. Always declare it as a separate FOREIGN KEY (...) REFERENCES ... line.
CHECK is enforced since MySQL 8.0.16
Before 8.0.16 MySQL accepted CHECK in the syntax but neither stored nor enforced it. If a database was migrated from an older version, it has no CHECK constraints even if the scripts declare them – verify with SHOW CREATE TABLE. The condition cannot use subqueries, non-deterministic functions such as NOW() or AUTO_INCREMENT columns. MariaDB enforces CHECK since version 10.2.
How to View a Table Structure: DESCRIBE and SHOW CREATE TABLE
Two commands show the structure of a table: DESCRIBE table lists the columns with their types, NULL, keys and default values, and SHOW CREATE TABLE table prints the full CREATE TABLE statement with all constraint names. SHOW TABLES lists the tables of the current database (an example is in Your First SQL Query in MySQL).
DESCRIBE topics; +------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(200) | NO | | NULL | |
| author_id | int | NO | MUL | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+ PRI marks a primary key column, MUL a column of a non-unique index (MySQL created it for the foreign key). DESCRIBE does not show constraint names, yet you need them for DROP FOREIGN KEY and DROP CHECK – SHOW CREATE TABLE prints them:
SHOW CREATE TABLE tags; CREATE TABLE `tags` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_tags_name` (`name`),
CONSTRAINT `chk_tags_name` CHECK ((char_length(`name`) >= 2))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DESC is the short form of DESCRIBE, and SHOW COLUMNS FROM topics prints the same result.
ALTER TABLE: Changing a Table
The ALTER TABLE command changes the structure of an existing table without losing its data: it adds, modifies, renames and drops columns, constraints and indexes. The syntax is ALTER TABLE name action [, action ...]; – one command can list several actions separated by commas.
| Action | Example | What to keep in mind |
|---|---|---|
| Add a column | ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email; | Without AFTER/FIRST the column goes last; existing rows get the DEFAULT value or NULL |
| Change the type and attributes | ALTER TABLE topics MODIFY title VARCHAR(255) NOT NULL; | The definition is replaced entirely: whatever you omit (NOT NULL, DEFAULT) is lost |
| Rename and change | ALTER TABLE topics CHANGE title subject VARCHAR(200) NOT NULL; | Needs the old name, the new name and the full definition |
| Only rename a column | ALTER TABLE topics RENAME COLUMN title TO subject; | Since MySQL 8.0; does not touch the type or constraints |
| Drop a column | ALTER TABLE users DROP COLUMN phone; | The column data is lost for good |
| Set or remove DEFAULT | ALTER TABLE users ALTER COLUMN phone SET DEFAULT ''; | DROP DEFAULT removes the default value |
| Add a constraint | ALTER TABLE tags ADD CONSTRAINT chk_tags_name CHECK (CHAR_LENGTH(name) >= 2); | If existing rows break the rule, the command fails |
| Drop a constraint | ALTER TABLE tags DROP CONSTRAINT chk_tags_name; | Since MySQL 8.0.19; earlier – DROP CHECK, DROP INDEX, DROP FOREIGN KEY |
| Add or drop an index | ALTER TABLE topics ADD INDEX idx_topics_created (created_at); | To drop: DROP INDEX idx_topics_created |
| Rename the table | ALTER TABLE topics RENAME TO threads; | Same as RENAME TABLE |
Let’s add a phone number to users – the very column that did not appear after CREATE TABLE IF NOT EXISTS:
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;
DESCRIBE users; +----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+ The rows added earlier (Alice, Bob) now have NULL in phone. To make phone numbers unique, use ALTER TABLE ... ADD CONSTRAINT ... UNIQUE – the example and how UNIQUE treats NULL are covered in Unique Key (UNIQUE).
What Will DESCRIBE Print After MODIFY?
The thread title must grow to 255 characters. Try to predict the Null column after this command:
ALTER TABLE topics MODIFY title VARCHAR(255);
DESCRIBE topics; +------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(255) | YES | | NULL | |
| author_id | int | NO | MUL | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+ The length grew, but Null became YES: MODIFY replaces the whole column definition, and the NOT NULL missing from the command silently disappeared. The database will now accept a thread without a title. The correct command repeats every attribute:
ALTER TABLE topics MODIFY title VARCHAR(255) NOT NULL; The opposite case is narrowing a type. If the column already holds a title longer than 50 characters, ALTER TABLE topics MODIFY title VARCHAR(50) NOT NULL; fails in strict mode with ERROR 1265 (01000): Data truncated for column 'title' at row 1, and the table stays unchanged.
Changing a Foreign Key
A foreign key cannot be changed in place: to set a different ON DELETE or ON UPDATE action, drop the key by name and create it again:
ALTER TABLE topics DROP FOREIGN KEY fk_topics_author;
ALTER TABLE topics
ADD CONSTRAINT fk_topics_author FOREIGN KEY (author_id)
REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE; After DROP FOREIGN KEY the index MySQL created for the key stays in the table – drop it separately with DROP INDEX if you no longer need it. Which action to choose for a given relationship is discussed in ON DELETE and ON UPDATE.
RENAME TABLE: Renaming a Table
A table is renamed with RENAME TABLE old_name TO new_name; or the equivalent ALTER TABLE old_name RENAME TO new_name;. Data, indexes and constraints are kept, and InnoDB automatically updates the foreign keys of other tables that referred to the renamed one.
RENAME TABLE topics TO threads;
RENAME TABLE threads TO topics; -- restore the old name for the next examples One RENAME TABLE command can rename several tables, and it does so atomically. This is how you swap a table for a prepared copy without a moment when the table does not exist at all:
RENAME TABLE topics TO topics_old, topics_new TO topics; MySQL has no RENAME DATABASE command: it was removed in version 5.1.23 as dangerous. How to move tables to a database with a new name is answered in the FAQ.
DROP TABLE and DROP DATABASE
DROP TABLE name; deletes a table entirely: rows, structure, indexes and constraints. DROP DATABASE name; deletes a database with all its tables. What was dropped can be restored only from a backup: DROP, like any DDL command in MySQL, cannot be rolled back.
DROP TABLE IF EXISTS topics_old; -- no error if the table does not exist
DROP TABLE IF EXISTS topics_copy, tags_backup; -- several tables at once
DROP DATABASE IF EXISTS test_forum; IF EXISTS turns ERROR 1051 (42S02): Unknown table into a warning – this is how scripts that rebuild a schema from scratch are written. DROP SCHEMA is a synonym of DROP DATABASE.
A table referenced by another table’s foreign key cannot be dropped:
DROP TABLE users; ERROR 3730 (HY000): Cannot drop table 'users' referenced by a foreign key constraint 'fk_topics_author' on table 'topics'. Drop tables in the reverse order of creation: child tables (topics) first, parent tables (users) after. A script that recreates the whole forum database in the right order is in The Forum Practice Database.
TRUNCATE TABLE vs DELETE vs DROP
TRUNCATE TABLE name; deletes all rows of a table and resets the AUTO_INCREMENT counter, but keeps the table itself with its columns, indexes and constraints. In MySQL it is a DDL command: InnoDB does not delete rows one by one but recreates an empty table, so on large tables TRUNCATE is much faster than DELETE and cannot be rolled back.
| Property | DELETE FROM t | TRUNCATE TABLE t | DROP TABLE t |
|---|---|---|---|
| Command group | DML | DDL | DDL |
| What it deletes | Rows: all or by a condition | All rows | The whole table: rows and structure |
WHERE condition | Allowed | Not allowed | Not allowed |
AUTO_INCREMENT counter | Not reset | Reset to 1 | Removed with the table |
ROLLBACK | Possible inside a transaction | Not possible (implicit COMMIT) | Not possible |
ON DELETE triggers | Fire | Do not fire | Do not fire |
| The table is referenced by a foreign key | Works if the key is not violated (or CASCADE fires) | ERROR 1701, even if the child table is empty | ERROR 3730 |
| Speed on millions of rows | Slow: every row is deleted and logged | Fast | Fast |
The DELETE syntax with conditions is covered in the module lesson on changing data with UPDATE and DELETE.
What Will SELECT Print After DELETE and After TRUNCATE?
Add three tags, empty the table with DELETE and insert a new tag. Which id will it get?
INSERT INTO tags (name) VALUES ('leisure'), ('sport'), ('shopping');
DELETE FROM tags;
INSERT INTO tags (name) VALUES ('java');
SELECT id, name FROM tags; +----+------+
| id | name |
+----+------+
| 4 | java |
+----+------+ DELETE removed the rows, but the counter remembers that numbers 1–3 have already been used. Now the same with TRUNCATE:
TRUNCATE TABLE tags;
INSERT INTO tags (name) VALUES ('java');
SELECT id, name FROM tags; Query OK, 0 rows affected (0.02 sec)
+----+------+
| id | name |
+----+------+
| 1 | java |
+----+------+ After TRUNCATE numbering starts over. Note the 0 rows affected: TRUNCATE does not report how many rows it deleted.
TRUNCATE and foreign keys
The example above works only while nothing refers to tags. Once the junction table topic_tags with a foreign key to tags exists, TRUNCATE TABLE tags fails with ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint – even when topic_tags is empty. Empty such a table with DELETE.
Where Schema Changes Go Wrong
The nastiest DDL mistakes are the ones MySQL does not report as errors: a constraint disappears, a column is not added, a check does not work. Below are the frequent cases, what MySQL 8 does in each of them and how to avoid it.
| Situation | What happens | How to do it right |
|---|---|---|
MODIFY without the previous attributes | NOT NULL, DEFAULT, AUTO_INCREMENT silently disappear | Repeat the full definition; compare with SHOW CREATE TABLE |
CREATE TABLE IF NOT EXISTS with new columns | Note 1050, the structure does not change | ALTER TABLE ... ADD COLUMN |
A foreign key on the column: author_id INT REFERENCES users (id) | Ignored without a warning in MySQL 8.4 and earlier | A separate line FOREIGN KEY (author_id) REFERENCES users (id) |
A table named order or group | ERROR 1064: You have an error in your SQL syntax – these are reserved words | A plural name (orders) or backticks `order` |
DROP TABLE on a parent table | ERROR 3730: Cannot drop table ... referenced by a foreign key constraint | Drop child tables first |
TRUNCATE on a referenced table | ERROR 1701: Cannot truncate a table referenced in a foreign key constraint | DELETE FROM or emptying tables in the right order |
INT(5), INT(11) in new scripts | Warning 1681: integer display width is deprecated | Plain INT |
DROP or TRUNCATE inside a transaction, hoping for ROLLBACK | Implicit COMMIT: the data is gone | A backup (CREATE TABLE ... LIKE + INSERT ... SELECT or a dump) before the command |
Frequently Asked Questions
What command groups does SQL have besides DDL?
Usually five are named: DDL (CREATE, ALTER, DROP, TRUNCATE) – structure; DML (INSERT, UPDATE, DELETE) – changing data; DQL (SELECT) – reading, often counted as part of DML; DCL (GRANT, REVOKE) – access rights; TCL (COMMIT, ROLLBACK, SAVEPOINT) – transaction control. A common interview question is which group TRUNCATE belongs to: in MySQL, Oracle and SQL Server it is DDL.
Does ALTER TABLE lock the table on a live server?
It depends on the operation. In MySQL 8 adding a column is instant by default (ALGORITHM=INSTANT, at any position since 8.0.29), and adding an index runs online (INPLACE) without blocking writes. Changing a column type rebuilds the whole table and blocks writes for the entire run. For large production tables, teams use online schema change tools such as pt-online-schema-change or gh-ost.
Why does CHECK let NULL through?
A CHECK constraint rejects a row only when the condition is false. For NULL, a condition such as CHAR_LENGTH(name) >= 2 evaluates to “unknown”, not false, so the row passes. If an empty value is not allowed, add NOT NULL to the column – as in the tags table in the section Constraints.
Who runs CREATE TABLE and ALTER TABLE in a Java project?
In study projects Hibernate often generates the schema from entities (spring.jpa.hibernate.ddl-auto=create or update). Real projects do not do that: update never drops or renames columns and can leave the schema in an unexpected state. DDL commands go into Flyway or Liquibase migration files that live in Git and are applied in order when the application starts, while Hibernate is set to validate.
How do I rename a database in MySQL?
There is no dedicated command. Create a new database and move the tables into it with RENAME TABLE, qualifying the database: RENAME TABLE forum.users TO forum2.users, forum.topics TO forum2.topics;. The tables move without copying data, and foreign keys are kept. Then drop the old, empty database. Views, stored procedures and triggers are not moved this way – recreate them. More on RENAME TABLE in the section RENAME TABLE.
Comments