Truncate Table syntax

Go Up to Truncate Table

TRUNCATE TABLE <table_name> [IMMEDIATE|DEFERRED] [RESTRICT|CASCADE]

When using the truncate Table command, please consider these points:

• IMMEDIATE is implicit if neither IMMEDIATE nor DEFERRED are specified. IMMEDIATE and DEFERRED are unreserved keywords.
• RESTRICT is implicit if neither RESTRICT nor CASCADE are specified.

For example:

 TRUNCATE TABLE <table_name>

is the same as:

TRUNCATE TABLE <table_name> IMMEDIATE RESTRICT

• Use the IMMEDIATE qualifier to execute the Truncate Table command immediately and to empty the content of the table.
• Use the DEFERRED qualifier to execute the Truncate Table command when the transaction terminates with COMMIT or ROLLBACK. COMMIT guarantees all the transactional work before emptying the target table. ROLLBACK cancels the Truncate Table Command.
• When you specify the RESTRICT qualifier the Truncate Table command only succeeds if no foreign key constrains reference the target table. The Truncate Table command only executes if the table has self-referencing foreign key constrains.
• When you specify the CASCADE qualifier, declare all the foreign key constrains of referencing tables with the ON DELETE CASCADE action, or the foreign key constrains not declared must reference currently "empty" tables. This condition applies recursively to referencing tables, if any table violates this condition the Truncate Table command fails with a foreign key violation error.

Note:
In this context "empty" means the table has no data storage allocated to it. A table with no rows still has storage allocated to it. This can happen when all rows have been deleted with one or more DELETE statements, but concurrent transactions still have earlier versions of the row in their snapshots, or the rows and their earlier versions are not in any transactions' snapshots but have not yet been garbage collected.

To immediately make those foreign key dependent tables empty, Truncate Table can be run against such tables if logic dictates. A Truncate Table statement is allowed to be called from InterBase triggers and stored procedures assuming they have been granted the TRUNCATE privilege.

Advance To:

• Truncate Table privilege