Practical 3: Dropping, Truncating and Renaming

Chapter Thirty-Seven

Syllabus topic Module 2, Practical 3: "Dropping/Truncating/Renaming Tables"

Pages 136 to 139 of 206

Aim

To drop, truncate and rename tables, and to distinguish between them.

The three, in one line each

DROP TABLE removes the table itself. Structure and rows, both gone.

TRUNCATE TABLE empties the table. Every row goes; the table, its columns and its constraints stay.

RENAME TABLE gives the table a different name. Nothing else changes.

And the fourth that belongs in the comparison:

DELETE FROM t with no WHERE also empties the table, and it is not the same as TRUNCATE. The differences are what this practical is really about.

Working on copies

Everything below is done on copies, so the library survives the chapter.

CREATE TABLE book_copy    AS SELECT * FROM book;
CREATE TABLE member_copy  AS SELECT * FROM member;
CREATE TABLE spare_table (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(20));
INSERT INTO spare_table (note) VALUES ('first'), ('second'), ('third');

CREATE TABLE ... AS SELECT copies the columns and the rows. It does not copy the primary key, the constraints or the indexes, which is worth knowing: a copy made this way is a table of the same data with none of the same rules.

TRUNCATE

SELECT COUNT(*) AS before_truncate FROM book_copy;

+-----------------+
| before_truncate |
+-----------------+
|               8 |
+-----------------+

TRUNCATE TABLE book_copy;

SELECT COUNT(*) AS after_truncate FROM book_copy;

+----------------+
| after_truncate |
+----------------+
|              0 |
+----------------+

Eight rows to none, and the table is still there:

DESCRIBE book_copy;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| book_id   | int          | NO   |     | NULL    |       |
| title     | varchar(20)  | NO   |     | NULL    |       |
| subject   | varchar(12)  | NO   |     | NULL    |       |
| price     | decimal(7,2) | YES  |     | NULL    |       |
| added_on  | date         | YES  |     | NULL    |       |
| author_id | int          | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

Every column still in place, which is the whole difference from DROP.

What TRUNCATE does to AUTO_INCREMENT

DELETE FROM spare_table;
INSERT INTO spare_table (note) VALUES ('after delete');

SELECT id, note FROM spare_table;

+----+--------------+
| id | note         |
+----+--------------+
|  4 | after delete |
+----+--------------+

Three rows were deleted and the next id was 4, because DELETE does not touch the counter. Now the other way:

TRUNCATE TABLE spare_table;
INSERT INTO spare_table (note) VALUES ('after truncate');

SELECT id, note FROM spare_table;

+----+----------------+
| id | note           |
+----+----------------+
|  1 | after truncate |
+----+----------------+

Back to 1. TRUNCATE resets the AUTO_INCREMENT counter and DELETE does not. That single difference is the one most often asked, and it is now demonstrated rather than claimed.

TRUNCATE and foreign keys

TRUNCATE TABLE member;

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`librarydb`.`loan`, CONSTRAINT `loan_ibfk_2`)

Practical 3: Dropping, Truncating and Renaming

Error 1701. Rows in loan point at member, so the server refuses to empty it, and it refuses even if no row would actually be orphaned, because TRUNCATE does not examine rows one at a time. DELETE FROM member would check each row and refuse only the ones that are referenced.

That is the second real difference: DELETE is row by row; TRUNCATE is wholesale. It is also why TRUNCATE is much faster on a large table.

DELETE against TRUNCATE

	`DELETE FROM t`	`TRUNCATE TABLE t`
Family	DML	DDL
Removes	rows, one at a time	all rows, wholesale
Can take a `WHERE`	yes	no
Resets `AUTO_INCREMENT`	no	yes
Can be rolled back	yes, inside a transaction	no: DDL commits
Fires triggers	yes	no
Speed on a large table	slow	fast
With a foreign key pointing at it	refuses only the referenced rows	refuses outright

The row that costs marks is rollback. TRUNCATE is DDL, so it commits, and there is no undoing it. DELETE inside a transaction can be undone right up to the COMMIT, which is [Practical 10: COMMIT and ROLLBACK].

RENAME

RENAME TABLE book_copy TO book_archive;

SELECT table_name AS tables_here
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;

+--------------+
| tables_here  |
+--------------+
| author       |
| book         |
| book_archive |
| loan         |
| member       |
| member_copy  |
| spare_table  |
+--------------+

book_copy is gone from the list and book_archive has appeared, with the same rows and the same structure. Renaming moves nothing: the table is not copied, so it is as fast on a large table as on a small one.

Two tables can be renamed in one statement, which is how a table is swapped for a new version without the old name ever being missing:

RENAME TABLE live TO old, staging TO live;

ALTER TABLE old_name RENAME TO new_name; does the same thing for one table, and is the form you will see more often.

A rename does not follow through into other objects. A view or a stored program that names the old table keeps naming it and breaks. So does a program of yours.

DROP

DROP TABLE book_archive;

SELECT table_name AS tables_here
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;

+-------------+
| tables_here |
+-------------+
| author      |
| book        |
| loan        |
| member      |
| member_copy |
| spare_table |
+-------------+

Gone from the list entirely: the rows, the columns, the constraints, the indexes.

SELECT * FROM book_archive;

ERROR 1146 (42S02): Table 'librarydb.book_archive' doesn't exist

Error 1146. There is no such table any more.

A table that another table's foreign key points at cannot be dropped either:

Practical 3: Dropping, Truncating and Renaming

DROP TABLE member;

ERROR 3730 (HY000): Cannot drop table 'member' referenced by a foreign key constraint 'loan_ibfk_2' on table 'loan'.

Error 3730, and the message names the constraint that is in the way. Drop the child table first, or drop the constraint.

DROP TABLE IF EXISTS t; does not complain when there is nothing to drop, which is what a script that may be run twice should use.

The four compared

	`DELETE`	`TRUNCATE`	`DROP TABLE`	`RENAME TABLE`
Rows	removed, selectively	all removed	removed	kept
Table structure	kept	kept	removed	kept
Table name	kept	kept	gone	changed
Family	DML	DDL	DDL	DDL
Rollback	yes	no	no	no
`AUTO_INCREMENT`	unchanged	reset	not applicable	unchanged

Learn that table. It is the answer to "differentiate between DELETE, TRUNCATE and DROP", which is asked in this practical's viva more reliably than anything else in Module 2.

What beginners get wrong

Thinking TRUNCATE can be rolled back. It is DDL and it commits.

Expecting DROP to keep the structure. That is TRUNCATE.

Putting a WHERE on TRUNCATE. It takes none. Use DELETE.

Expecting DELETE to reset AUTO_INCREMENT. It does not.

Dropping a parent table before its children. Refused, error 3730.

Renaming a table and forgetting the views and programs that name it. They break, and not until the next time they run.

Using DROP DATABASE when a table was meant. There is no confirmation.

Quick revision

DROP TABLE t; removes structure and rows. DROP TABLE IF EXISTS t; does not complain.
TRUNCATE TABLE t; empties it, resets AUTO_INCREMENT, cannot be rolled back, takes no WHERE, and is refused outright if a foreign key points at the table.
RENAME TABLE a TO b; or ALTER TABLE a RENAME TO b; changes only the name.
DELETE FROM t; is DML: row by row, rollback-able, fires triggers, leaves the counter alone.
CREATE TABLE c AS SELECT * FROM t; copies columns and rows but not keys, constraints or indexes.
Error 1146 is no such table; 1701 is truncate refused by a foreign key; 3730 is drop refused by one.

What goes in your journal

Aim, and then the four statements each with a SELECT COUNT(*) and a SHOW TABLES before and after, so that what survived each one is visible. Then the comparison table, which is what the practical is for.

Include the AUTO_INCREMENT demonstration: delete the rows, insert one, note the id; truncate, insert one, note the id. Two ids, 4 and 1, and they are the proof of the difference.

Test yourself

1. What is the difference between DELETE and TRUNCATE? DELETE is DML, removes rows one at a time, can take a WHERE, can be rolled back, fires triggers and leaves AUTO_INCREMENT alone. TRUNCATE is DDL, empties the table wholesale, takes no WHERE, cannot be rolled back, fires no triggers and resets the counter.

Practical 3: Dropping, Truncating and Renaming

2. What does DROP TABLE leave behind? Nothing. The rows, the columns, the constraints and the indexes all go.

3. Why was TRUNCATE TABLE member refused? Because a foreign key in loan references member. TRUNCATE is refused outright whenever such a reference exists, without examining the rows.

4. After deleting all three rows and inserting one, what id does it get? And after truncating instead? 4 after the delete, because the counter is untouched. 1 after the truncate, because TRUNCATE resets it.

5. Does RENAME TABLE copy the data? No. Only the name changes, so it is as quick on a large table as on a small one.

6. What does CREATE TABLE copy AS SELECT * FROM t; not copy? The primary key, the other constraints and the indexes. It copies the columns and the rows only.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Major Practical 1

Report or request