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

[Martin Kutschker]

Martin Kutschker schrieb:
> 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.

DB2:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/db2z_sql_truncate.htm

SQLlite:

I couldn't find TRUNCATE for this database. But it seems that a DELETE
without a WHERE clause is optimized to work like a TRUNCATE.

http://www.sqlite.org/lang_delete.html
http://www.sqlite.org/compile.html#omit_truncate_optimization

Masi