Db2 RENAME TABLE

Summary: in this tutorial, you will learn how to use the Db2 RENAME TABLE statement to change the name of a table to the new one.

Introduction to Db2 RENAME TABLE statement

To change the name of an existing table to a new one, you use the RENAME TABLE statement.

Here is the syntax of the RENAME TABLE statement:

RENAME TABLE table_name TO new_table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table to which you want to rename after the RENAME TABLE keywords.
• Then, specify the new name of the table after the TO keyword.

Db2 requires that the table, which you want to rename, must meet the following requirements:

• The table must not have any reference in existing objects such as view, triggers, functions, or constraints.
• The table must not have any generated column, other than the identity column, or be a parent or dependent table.

Notice that this behavior is different from other database systems such as Oracle and SQL Server, which mark the dependent objects of the table invalid.

Db2 RENAME TABLE statement example

First, create a new table named favorites:

CREATE TABLE favorites (
	book_id INT NOT NULL,
	user_id INT NOT NULL,
	PRIMARY KEY (book_id,user_id)
);
Code language: SQL (Structured Query Language) (sql)

Then, use the RENAME TABLE statement to change the favorites table to favorite_books table:

RENAME TABLE favorites TO favorite_books;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the DB2 RENAME TABLE statement to change the name of a table to the new one.