ALTER TABLE DROP PRIMARY KEY does not seem to work.

[Alex]

Hello Thomas,

I am trying to drop the primary key on a table.
I get the error below. The actual index name is "PRIMARY_KEY_6".
It looks like it's trying to delete the index "PRIMARY_KEY_"

Alex


ALTER TABLE TARGET_IDS DROP PRIMARY KEY;
Index PRIMARY_KEY_ not found; SQL statement:
ALTER TABLE TARGET_IDS DROP PRIMARY KEY [42112-104] 42S12/42112 (Help)
org.h2.jdbc.JdbcSQLException: Index PRIMARY_KEY_ not found; SQL
statement:
ALTER TABLE TARGET_IDS DROP PRIMARY KEY [42112-104]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.table.Table.getPrimaryKey(Table.java:558)
at org.h2.command.Parser.parseAlterTable(Parser.java:4299)
at org.h2.command.Parser.parseAlter(Parser.java:3816)
at org.h2.command.Parser.parsePrepared(Parser.java:305)
at org.h2.command.Parser.parse(Parser.java:288)
at org.h2.command.Parser.parse(Parser.java:260)
at org.h2.command.Parser.prepareCommand(Parser.java:232)
at org.h2.engine.Session.prepareLocal(Session.java:344)
at org.h2.engine.Session.prepareCommand(Session.java:305)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1043)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebThread.getResult(WebThread.java:1723)
at org.h2.server.web.WebThread.query(WebThread.java:1286)
at org.h2.server.web.WebThread.process(WebThread.java:444)
at org.h2.server.web.WebThread.processRequest(WebThread.java:186)
at org.h2.server.web.WebThread.process(WebThread.java:241)
at org.h2.server.web.WebThread.run(WebThread.java:196)

[Thomas Mueller]

Hi,

How does your database look like? I can't reproduce the problem using

create table test(id int primary key);
ALTER TABLE test DROP PRIMARY KEY;

You can get the CREATE statements by running

SCRIPT NODATA TO 'test.sql';

Regards,
Thomas

[Alex]

Hi,

It may be a slightly different issue. Here is the script:
==================================================================================
CREATE CACHED TABLE PUBLIC.TARGET_IDS(
TARGET_ID INT NOT NULL
);

ALTER TABLE PUBLIC.TARGET_IDS ADD CONSTRAINT PUBLIC.CONSTRAINT_3
PRIMARY KEY(TARGET_ID);
==================================================================================

I need to drop the index. I no longer care about the not null
constraint.
I tried running : ALTER TABLE TARGET_IDS MODIFY TARGET_ID int; to drop
the constraint, it does not seem to be supported.

Actually I just updated to the latest nightly build (106) using the
"svn update" and the ALTER TABLE DROP PRIMARY KEY did work for me.
My previous H2 version where it did not work was 104.

What is the recommended way to get rid of the primary key constraint
in my case ?

Alex




On Jan 9, 12:27 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> How does your database look like? I can't reproduce the problem using
>
> create table test(id int primary key);
> ALTER TABLE test DROP PRIMARY KEY;
>
> You can get the CREATE statements by running
>
> SCRIPT NODATA TO 'test.sql';
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

> Actually I just updated to the latest nightly build (106) using the
> "svn update" and the ALTER TABLE DROP PRIMARY KEY did work for me.
> My previous H2 version where it did not work was 104.
>
> What is the recommended way to get rid of the primary key constraint
> in my case ?

If you don't want to upgrade, you could use SCRIPT TO '...', edit the
script manually, then create a new database, and run RUNSCRIPT FROM
'...' to recreate the database.

Regards,
Thomas