Dropping unique constraint for column in H2

Question

I try to drop unique constraint for 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 with no 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 this constraint should be correctly removed?

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)

seems to return a correct output.

Solution 1

In the SQL language, identifier names can't 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 get the identifier name, and run the statement

ALTER TABLE PUBLIC_PARTNER DROP CONSTRAINT <xxx>

Solution 2

You could use a user defined function to execute a dynamically created statement. First to create the 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 the user defined function that runs a statement.

Solution 3

If you are using H2 with Spring Boot in PosgreSQL Mode the query has to include the schema public and the tables are likely in lower case mode. (see application.yml below)

Check the letter case in the information schema table and use the upper and lower case as seen in table information_schema.constraints.

Verbose Query Set

SET @constraint_name = QUOTE_IDENT(
              SELECT DISTINCT constraint_name
              FROM information_schema.constraints
              WHERE table_schema = 'public'
              AND table_name = 'public_partner'
              AND constraint_type = 'UNIQUE'
              AND column_list = 'info');

SET @command = 'ALTER TABLE public.public_partner DROP CONSTRAINT public.' || @constraint_name;

SELECT @command;

EXECUTE IMMEDIATE @command;

Explanation:

• SELECT DISTINCT constraint_name [...]

  Select the Columns constraint_name with the UNIQUE constrain from the schema info

• QUOTE_IDENT([...])

  I don't know why this is needed, it will quote the resulting string

• SET @constraint_name = [...];

  Store in Variable @constraint_name

• SET @command = [...];

  Compose whole command by concatenation of strings and store in variable @command

• SELECT @command;

  Show the composed Query on Screen, just for debugging

• EXECUTE IMMEDIATE @command;

  Execute @command

Typical H2 Configuration in PostgreSQL Mode in the Spring Boot application.yml (extract)

spring:
  # [...]
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
  # [...]
  datasource:
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false
    username: sa
    password: sa
  # [...]

Comments

[Alec]

Thanks, Thomas! I expected you personally to answer to my question :) The problem is that your solution is obvious for me, and i will accept your answer anyway as useful. The bad thing is i need it to automate my delta script without manual intervention :(((

[MartinGrotzke]

This would be the user defined function: CREATE ALIAS IF NOT EXISTS EXECUTE AS $$ void executeSql(Connection conn, String sql) throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

[MartinGrotzke]

With this I got "Constraint <xxx> not found". It worked for me with "constraint_name" instead of "unique_index_name", so that the select was select distinct constraint_name from in formation_schema.constraints where table_name='PUBLIC_PARTNER' and column_list='INFO' One may also additionally restrict the constraint type by adding ` AND constraint_type = 'UNIQUE'` My H2 version was 1.3.166 btw.

[Thomas Mueller]

@MartinGrotzke Thanks! I have updated my answer (there was also a typo in "information_schema").

[gzak]

This is just what I need, and I'm so close... You glossed over how to get the identifier name, and I'm having a very hard time figuring it out (I'm using H2).

[smajlo]

using this solution but it's failing with Feature not supported: "VARCHAR +"; SQL statement:. My Code: call execute('ALTER TABLE DAILY_AGGREGATES DROP CONSTRAINT '+ (select distinct constraint_name from information_schema.constraints where table_name='DAILY_AGGREGATES' and constraint_type='PRIMARY KEY'));

[Thomas Mueller]

In SQL, the + is not used for concatenating string. Instead, use ||. As in: call execute('ALTER TABLE DAILY_AGGREGATES DROP CONSTRAINT ' || (select distinct constraint_name from information_schema.constraints where table_name='DAILY_AGGREGATES' and constraint_type='PRIMARY KEY'));