Truncate Table syntax
From InterBase
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 neitherIMMEDIATE
norDEFERRED
are specified.IMMEDIATE
andDEFERRED
are unreserved keywords.RESTRICT
is implicit if neitherRESTRICT
norCASCADE
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 withCOMMIT
orROLLBACK
.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 theON 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
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.