expected identifier error

[aditya]

Hi,

I am using H2 database version 1.1.18 (I cannot upgrade to latest
version).

I am trying to drop an unnamed constraint on one of the tables using
following query
ALTER TABLE T_USER DROP CONSTRAINT (
SELECT constraint_name FROM information_schema.constraints WHERE
table_name = 'T_USER' AND column_list = 'NAME'
);

But I get error saying:

Syntax error in SQL statement ALTER TABLE T_USER DROP CONSTRAINT ([*]
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE
TABLE_NAME = 'T_USER' AND COLUMN_LIST = 'NAME'
) ; expected identifier; SQL statement:
alter table T_USER drop constraint (
SELECT constraint_name FROM information_schema.constraints WHERE
table_name = 'T_USER' AND column_list = 'NAME'
) [42001-75] 42001/42001 (Help)
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement ALTER
TABLE T_USER DROP CONSTRAINT ([*]
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE
TABLE_NAME = 'T_USER' AND COLUMN_LIST = 'NAME'
) ; expected identifier; SQL statement:
alter table T_USER drop constraint (
SELECT constraint_name FROM information_schema.constraints WHERE
table_name = 'T_USER' AND column_list = 'NAME'
) [42001-75]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSyntaxError(Message.java:139)
at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:
2346)
at org.h2.command.Parser.parseAlterTable(Parser.java:4142)
at org.h2.command.Parser.parseAlter(Parser.java:3681)
at org.h2.command.Parser.parsePrepared(Parser.java:307)
at org.h2.command.Parser.parse(Parser.java:290)
at org.h2.command.Parser.parse(Parser.java:262)
at org.h2.command.Parser.prepareCommand(Parser.java:234)
at org.h2.engine.Session.prepareLocal(Session.java:285)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:210)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:126)
at java.lang.Thread.run(Thread.java:619)

at org.h2.engine.SessionRemote.done(SessionRemote.java:408)
at org.h2.command.CommandRemote.prepare(CommandRemote.java:68)
at org.h2.command.CommandRemote.<init>(CommandRemote.java:47)
at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:
341)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1042)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebThread.getResult(WebThread.java:1707)
at org.h2.server.web.WebThread.query(WebThread.java:1270)
at org.h2.server.web.WebThread.process(WebThread.java:437)
at org.h2.server.web.WebThread.processRequest(WebThread.java:184)
at org.h2.server.web.WebThread.process(WebThread.java:234)
at org.h2.server.web.WebThread.run(WebThread.java:194)

The table exists and constraint name also exist. Not sure why i am
getting this error. Could you please help?

[Thomas Mueller]

Hi,

> I am using H2 database version 1.1.18 (I cannot upgrade to latest
> version).

Usually I would say "why not?" but here it wouldn't help.

> I am trying to drop an unnamed constraint on one of the tables using
> following query
> ALTER TABLE T_USER DROP CONSTRAINT (
>  SELECT constraint_name FROM information_schema.constraints WHERE
> table_name = 'T_USER' AND column_list = 'NAME'
>  );

Identifiers can't be formulas, the same as in other programming languages.

Regards,
Thomas

[Madusanka Premaratne]

This does not work in mysql.