在H2中删除列的唯一约束条件

Question

我试图在h2数据库中删除一个先前创建的带有info varchar(255) unique约束的列的唯一约束。

我尝试过：

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');

但是没有成功（如下）：

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]

这个限制应该如何正确地删除？

顺便说一下：

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)

看起来返回了正确的输出。

- Alec

Answer 1

在SQL语言中，标识符名称不能是表达式。您需要运行两个语句：

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

然后获取标识符名称，并执行该语句

ALTER TABLE PUBLIC_PARTNER DROP CONSTRAINT <xxx>

- Thomas Mueller

---

谢谢，Thomas！我期望你亲自回答我的问题 :) 问题是你的解决方案对我来说很明显，我无论如何都会接受你的答案作为有用的。不好的是，我需要将其自动化以避免手动干预 :((( - Alec

我得到了“未找到约束<xxx>”的错误。对我而言，使用“constraint_name”而不是“unique_index_name”可以解决这个问题，因此选择语句为select distinct constraint_name from information_schema.constraints where table_name='PUBLIC_PARTNER' and column_list='INFO'。您还可以通过添加AND constraint_type = 'UNIQUE'来进一步限制约束类型。顺便说一下，我的H2版本是1.3.166。 - MartinGrotzke

@MartinGrotzke 谢谢！我已经更新了我的回答（"information_schema" 也有一个打字错误）。 - Thomas Mueller

这正是我所需要的，而且我已经接近成功了... 你没有详细说明如何获取标识符名称，我很难弄清楚（我正在使用H2）。 - gzak

Answer 2

您可以使用用户定义的函数来执行动态创建的语句。首先要创建execute别名（仅一次）：

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

然后调用这个方法：

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'));

...其中execute是运行语句的用户定义函数。

- Thomas Mueller

---

这将是用户定义的函数：`CREATE ALIAS IF NOT EXISTS EXECUTE AS $$ void executeSql(Connection conn, String sql) throws SQLException { conn.createStatement().executeUpdate(sql); } $$;` - MartinGrotzke

使用这个解决方案，但是出现了“不支持的特性：“VARCHAR +”; SQL语句：”的错误。我的代码：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')); - smajlo

在SQL中，+不用于连接字符串。相反，使用 ||。例如：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

Answer 3

如果您在Spring Boot中使用H2并且处于PosgreSQL模式下，则查询必须包括模式public，表通常处于小写模式。（请参见下面的application.yml）

检查信息模式表中的字母大小写，并根据information_schema.constraints表中所看到的大小写使用大小写。

详细查询集。

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;

解释：

• SELECT DISTINCT constraint_name [...]

  从模式信息中选择具有唯一约束的列constraint_name

• QUOTE_IDENT([...])

  我不知道为什么需要这个，它将引用结果字符串

• SET @constraint_name = [...];

  将其存储在变量@constraint_name中

• SET @command = [...];

  通过字符串连接组成整个命令，并将其存储在变量@command中

• SELECT @command;

  显示组成的查询，仅用于调试

• EXECUTE IMMEDIATE @command;

  执行@command

Spring Boot application.yml中PostgreSQL Mode的典型H2配置（摘录）

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
  # [...]

- notes-jj