Db2 DROP TABLE

Summary: in this tutorial, you’ll learn how to use the Db2 DROP TABLE statement to delete an existing table from a database.

Introduction to Db2 DROP TABLE statement

Sometimes, you may want to delete one or more unused tables from a database. To do this, you use the DROP TABLE statement as follows:

DROP TABLE [schema_name.]table_name;

In this syntax:

• First, specify the name of the schema to which the table belongs. The schema is optional. If you skip it, the statement will delete the specified table in the current schema.
• Second, specify the name of the table that you want to drop.

When you use the DROP TABLE statement to delete a table, Db2 performs the following actions:

• Delete all data in the table permanently.
• Delete all columns of the dropped table and the indexes associated with these columns.
• Mark all views that reference to the dropped table as inoperative.
• Also, mark all triggers that depend on the dropped table as inoperative.
• Revoke all privileges on the table and dependent views.

Db2 DROP TABLE examples

Let’s create three new tables to demonstrate the DROP TABLE statement.

CREATE TABLE t1(
	id INT NOT NULL PRIMARY KEY
);

CREATE TABLE t2(
	id INT NOT NULL PRIMARY KEY
);

CREATE TABLE t3(
	id INT NOT NULL PRIMARY KEY,
	fk INT NOT NULL,
	FOREIGN KEY fk_t2(fk) REFERENCES t2(id)
);

1) Drop a table example

The following statement uses the DROP TABLE statement to drop the t1 table:

DROP TABLE t1;

This example is straight forward. The table t1 was just dropped successfully.

In practice, a table may have foreign key references like the t2 table.

2) Drop a table with foreign key constraint example

The following example uses the DROP TABLE statement to drop the t2 table:

DROP TABLE t2;

The t2 table was dropped. In addition, the foreign key fk_t2 was marked as inoperative.

In this tutorial, you have learned how to use the Db2 DROP TABLE statement to remove one or more tables from a database.