[TYPO3-dev] How standard compliant is "truncate"?

[Martin Kutschker]

Martin Kutschker schrieb:
> Martin Kutschker schrieb:
>> Peter Kuehn [wmdb] schrieb:
>>> Hi yall,
>>>
>>> some questions to those, who know more about databases than i do:
>>>
>>> How standard compliant is "truncate table"?
>> Taken from the docs for Oracle 11g Rel. 1
>>
>> "You cannot truncate the parent table of an enabled foreign key
>> constraint. You must disable the constraint before truncating the table.
>> An exception is that you can truncate the table if the integrity
>> constraint is self-referential."
>>
>> And Oracle's implementation allows only ONE table to be truncated at once.
> 
> Here's the docs for SQL Server 2008:
> 
> http://msdn.microsoft.com/en-us/library/ms177570.aspx
> 
> Same here "You cannot use TRUNCATE TABLE on tables that:
>     * Are referenced by a FOREIGN KEY constraint. (You can truncate a
> table that has a foreign key that references itself.)"
> 
> There are (also for Oracle) also other constraints.
> 
> Note also this (the same goes for Mysql):
> 
> "If the table contains an identity column, the counter for that column
> is reset to the seed value defined for the column. If no seed was
> defined, the default value 1 is used. To retain the identity counter,
> use DELETE instead."

http://dev.mysql.com/doc/refman/5.0/en/truncate.html
http://www.postgresql.org/docs/8.1/static/sql-truncate.html

Seems that PostgreSQL is the only DBRMS that allows multiple tables to
be truncated at once.

Masi