Bug cannot truncate table? H2 1.0 (2007-04-29)

[AnselmD]

Hello Thomas,

i got an error when executing the follow statements H2 1.0
(2007-04-29):
I think this is a bug.

DROP TABLE test IF EXISTS;
CREATE TABLE test(
PRIMARY KEY (id),
id varchar(20)
);

DROP TABLE test2 if EXISTS;
CREATE TABLE test2(
id VARCHAR(20),
FOREIGN KEY(id) REFERENCES test
);

INSERT INTO test(id)
values('xxx');

truncate table test;

Cannot truncate TEST [90106-46]
org.h2.jdbc.JdbcSQLException: Cannot truncate TEST [90106-46]
at org.h2.message.Message.getSQLException(Message.java:65)
at org.h2.message.Message.getSQLException(Message.java:47)
at org.h2.command.ddl.TruncateTable.update(TruncateTable.java:31)
at org.h2.command.CommandContainer.update(CommandContainer.java:
64)
at org.h2.command.Command.executeUpdate(Command.java:120)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:155)
at org.h2.server.web.AppThread.getResult(AppThread.java:941)
at org.h2.server.web.AppThread.query(AppThread.java:611)
at org.h2.server.web.AppThread.process(AppThread.java:71)
at org.h2.server.web.WebServerThread.run(WebServerThread.java:
127)

Anselm

[Thomas Kellerer]

Quote from the manual at http://h2database.com/html/grammar.html#sql52

"Only regular data tables without foreign key constraints can be
truncated. "

[AnselmD]

ok, dann sehe ich zwei Möglichkeiten
1)
- foreign key droppen
- truncate table
- foreign key neu erstellen

oder
- table droppen
- table neu erstellen

Ein "alter table disable constraint" wie unter Oracle ist ja wohl kein
SQL-Standard.

Habe ich noch andere Möglichkeiten?

On 11 Mai, 00:12, Thomas Kellerer <CAJWEDXOJ...@spammotel.com> wrote:
> Quote from the manual athttp://h2database.com/html/grammar.html#sql52

[AnselmD]

Sorry, the same in english:

i think i have two possibilities:
1)
- drop the foreign key condition
- truncate table
- create the foreign key condition anew

2) or
- drop table
- create table anew

"alter table disable constraint" is not a SQL-Standard, isn´t it?

Is there any other possibility?

Best Regards,
Anselm

[AnselmD]

...and i can do a remove from table...
I think i do not need more inspiration ;-)

Best Regards,
Anselm

[AnselmD]

this i not my day.... i mean delete from table