DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 DROP TABLE

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;
Code language: SQL (Structured Query Language) (sql)

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) );
Code language: SQL (Structured Query Language) (sql)

1) Drop a table example

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

DROP TABLE t1;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 ALTER TABLE DROP COLUMN
Next Db2 TRUNCATE TABLE

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.