[
https://issues.apache.org/jira/browse/DERBY-268?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12905937#action_12905937
]
Rick Hillegas commented on DERBY-268:
-------------------------------------
As Knut notes, the TRUNCATE TABLE command is defined in the 2008 SQL Standard,
part 2, section 14.10 <truncate table statement>. The standard syntax is:
TRUNCATE TABLE tableName [ identityBehavior ]
identityBehavior ::=
CONTINUE IDENTITY
| RESTART IDENTITY
The default behavior is CONTINUE IDENTITY, which means that the table's
identity column (if it has one) continues its sequence where it left off.
RESTART IDENTITY means that the identity column resets to start again at its
original initial value.
Derby appears to implement a compatible subset of this standard syntax if you
are running in debug mode:
TRUNCATE TABLE tableName
Derby preserves the standard CONTINUE IDENTITY default behavior.
The following comment in AlterTableNode explains why TRUNCATE TABLE has been
disabled in non-debug mode:
//truncate table is not supported in this release
//semantics are not yet clearly defined by SQL Council yet
//truncate will be allowed only in DEBUG builds for testing
purposes.
I believe that was a statement about the 2003 rev of the Standard--it is no
longer true. I believe that we can expose this useful command in non-debug,
production mode now.
This seems to me to be a good issue for a newcomer. It involves the following:
1) Remove the disabling logic from the initializer of AlterTableNode.
2) Write regression tests to verify that TRUNCATE behaves correctly. In
particular, verify that CONTINUE IDENTITY semantics are enforced.
3) Document this command in the Reference Guide.
A follow-on effort might be to implement the optional CONTINUE IDENTITY and
RESTART IDENTITY clauses. Fortunately, the tricky bit of RESTART IDENTITY has
already been implemented. The tricky bit is the following implied statement
which is executed after truncating the table:
ALTER TABLE tableName ALTER COLUMN RESTART WITH initialValue
> Add Support for truncate table
> ------------------------------
>
> Key: DERBY-268
> URL: https://issues.apache.org/jira/browse/DERBY-268
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Lance Andersen
> Priority: Minor
>
> Adding support for truncate table command will aid to portability
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
