Unique constaraint not working properly in unit test script

29 Views Asked by At

I have springboot app with H2 database for unit tests.

I have table:

create table system
(
id serial not null constraint system_pk primary key,
code varchar(15) not null,
prefix char(3) not null
);

create unique index if not exists system_code_prefix_uindex on system(code, prefix);

For unit tests I have script with inserts:

insert into system(id, code, prefix)
values(100, 'ABC', 'AAA'),
(200, 'DEF', 'AAA'),
(300, 'XYZ', 'AAA'),
(400, 'ABC', 'XXX');

Script fails with error: Unique index or primary key violation:"PUBLIC.CONSTRAINT_INDEX_65 ON PUBLIC.SYSTEM(CODE NULLS FIRST) VALUES (/* 100 */ 'ABC' )

The same script works if executed directly on database.

I tried adding to script before inserts:

alter table public.system drop constraint if exists CONSTRAINT_65;
drop index if exists CONSTRAINT_INDEX_65;
create unique index if not exists system_code_prefix_uindex on system(code, prefix);

and it works, but does not look good.

0

There are 0 best solutions below