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:
1 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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:
1 | 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:
1 | 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.