Firebird 不支援 TRUNCATE TABLE 指令


Few notes about level of support of TRUNCATE statement in Firebird :

a) as we have no IDENTITY we can ignore and all related rules
(Syntax rule 4, Genereal rule 8)

Firebird中沒有IDENTITY,所以不用找了 (笑)

b) Syntax rule 2 means TRUNCATE TABLE is not applicable to VIEW’s


c) Syntax rule 3 means truncated table must not participate in FOREIGN KEY constraints as
master table.
ORACLE relaxed this rule and allow self-referenced constraints. I think we can follow it.

d) as we have no subrows we can ignore General rule 5

e) it will be good to implement TRUNCATE privilege to satisfy Access rules but for the first time
we can allow to TRUNCATE table only for SYSDBA and OWNER.


ORACLE used DROP TABLE privilege to control usage of TRUNCATE TABLE statement, while MSSQL used ALTER TABLE privilege.

From the implementation point of view i have some concerns. All the code which released
table’s and indexes pages without removing relation itself is already present. It may require
some small changes but i see no problem with it.

The main concern is about rollback’s (or undo). If we will perform TRUNCATE TABLE as usual
DML statement, i.e. not defer it to transaction commit time, then we must implement support
for undo-log. Also we must decide if we will preserve truncated contents for older still active
concurrent transactions. And specify when this content will be erased finally.

Another approach is to require exclusive lock on table when TRUNCATE is issued (to prevent
any concurrent access to table’s data) and to defer TRUNCATE execution to the commit time. It
allows us to not preserve table contents for the indefinite time and to avoid complex manipulations with undo-log.

Note, ORACLE can’t rollback TRUNCATE statement. MSSQL 2005 don’t log truncation of big
objects and defer physical deallocation until commit time. All pages remains locked until commit
so no concurrent access to truncated data is allowed, AFAIU. IIRC, MSSQL before 2005 also can’t
rollback TRUNCATE statement.



Firebird說明仍能有條件地使用 TRUNCATE 指令






一山不容二虎之 EDBClient - Key violation 記事

關聯式資料庫裡有個很基本的特性:PRIMARY KEY 條件約束。 一個資料表只能有一個 PRIMARY KEY 條件約束,並且任何加入 PRIMARY KEY 條件約束的資料行都不可接受 Null 值。因為 PRIMARY KEY 條件約束保證唯一的資料,它們通常...