Removing a unique constraint for a column in H2

Question

I am trying to remove a unique constraint for a column in h2 previously created as info varchar(255) unique .

I tried:

 sql> alter table public_partner drop constraint (select distinct unique_index_name from in formation_schema.constraints where table_name='PUBLIC_PARTNER' and column_list='INFO'); 

But without success (as follows):

 Syntax error in SQL statement "ALTER TABLE PUBLIC_PARTNER DROP CONSTRAINT ([*]SELECT DISTI NCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME='PUBLIC_PARTNER ' AND COLUMN_LIST='INFO') "; expected "identifier"; SQL statement: alter table public_partner drop constraint (select distinct unique_index_name from informa tion_schema.constraints where table_name='PUBLIC_PARTNER' and column_list='INFO') [42001-1 60] 

How should this restriction be removed correctly?

By the way:

 sql> (select unique_index_name from information_schema.constraints where table_name='PUBLI C_PARTNER' and column_list='INFO'); UNIQUE_INDEX_NAME CONSTRAINT_F574_INDEX_9 (1 row, 0 ms) 

It seems that

returns the correct result.

Answer 1

In SQL, identifier names cannot be expressions. You need to run two statements:

 select distinct constraint_name from information_schema.constraints where table_name='PUBLIC_PARTNER' and column_list='INFO' 

and then enter the id name and run the statement

 ALTER TABLE PUBLIC_PARTNER DROP CONSTRAINT <xxx> 

Answer 2

You can use a user-defined function to execute a dynamically created statement. First create an execute alias (only once):

 CREATE ALIAS IF NOT EXISTS EXECUTE AS $$ void executeSql(Connection conn, String sql) throws SQLException { conn.createStatement().executeUpdate(sql); } $$; 

Then, to call this method:

 call execute('ALTER TABLE PUBLIC_PARTNER DROP CONSTRAINT ' || (select distinct unique_index_name from in formation_schema.constraints where table_name='PUBLIC_PARTNER' and column_list='INFO')); 

... where execute is a user-defined function that executes the statement.