3

I have the following constraint in CREATE statement:

UNIQUE (`field_name`)

These commands to remove constraint work in MySQL but not in H2:

DROP INDEX `field_name` ON `table_name`;    
ALTER TABLE `table_name` DROP INDEX `field_name`;

I need a command which would work both in MySQL and H2 (MySQL is used in real environment and H2 in Unit tests)

Alexander
  • 452
  • 6
  • 15

2 Answers2

2

Found the following workaround: removing column removes the constraint, so:

ALTER TABLE `table_name` ADD COLUMN `tmp_code` VARCHAR(50) NOT NULL DEFAULT 'TMP';

UPDATE `table_name`
SET `tmp_code` = `field_name`;

ALTER TABLE `table_name` DROP COLUMN `field_name`;

ALTER TABLE `table_name` ADD COLUMN `field_name` VARCHAR(50) NOT NULL;

UPDATE `table_name`
SET `field_name` = `tmp_code`;

ALTER TABLE `table_name` DROP COLUMN `tmp_code`;
Alexander
  • 452
  • 6
  • 15
0

Do SHOW CREATE TABLE to see the name of the UNIQUE KEY. Then you can proceed with the DROP or ALTER. It will probably say

UNIQUE KEY `col` (`col`),

The first col is the key name.

If you need to maintain the INDEX but get rid of the UNIQUEness constraint, then drop the key, then add a non-unique key.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • For others who tried to execute the query on a different connection (e.g. IntelliJ console), this won't work as H2 In-Memory databases create unique db on each connection. See [this](https://stackoverflow.com/a/28950817/14148807) for more info. – Achmad Afriza Nov 14 '21 at 13:38
  • @Flavalacious - So it is an H2 problem? And perhaps Alexander has the right answer? – Rick James Nov 14 '21 at 16:32
  • It would seem so. I did some digging through native H2 [commands](https://www.h2database.com/html/commands.html#drop_index) and it looks like it's inherently different, as suggested by the [documentation](http://h2database.com/html/features.html#compatibility). Error messages do also point out the same error area (note the `[*]` on the message). I would love to elaborate more later on in another answer if it helps, as thorough checking would be a bit tedious with H2 databases. – Achmad Afriza Nov 17 '21 at 18:45