Getting names of unique constraints via hibernate

[Tadashi Shibata]

I need to fix hibernate so that I can get names of unique constraints from exceptions.
I made a fix and it works fine at least for me.
Before pull request, there are two points I want to know:
- I can not extract constraint name from the message in case of primary key constraint violation even when it is explicitly named, can I?
- I'm going to leave suffix sometimes added by H2 Database. Should I remove it? (Although I don't know the naming rule)
Those two points don't matter for me but it should be good also for others.

BR,
Tadashi

[Evgenij Ryazanov]

Hello!

H2 doesn't return names of constrains in these exceptions (maybe it will in future versions). If you need to find name of constraint, you need to check INFORMATION_SCHEMA.TABLE_CONSTRAINTS table. You need only rows where TABLE_SCHEMA and TABLE_NAME match schema and name of updated table.

* For primary key violations situation is simple, because a table may have only one such constraint, so you need a row with CONSTRAINT_TYPE = 'PRIMARY KEY'.

* For unique index violations you need to read index name from exception and find a row with matched names in INDEX_SCHEMA and INDEX_NAME columns. If there is no such row, it isn't a violation of a unique constraint, but it is a violation of unique index (and you already know name of this index), unique indexes may exist without constraints. Alternatively you can parse column names from exception and search them in INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but such check will be significantly more complicated than check by index name.

Names of both primary key and unique constraints can be read from CONSTRAINT_SCHEMA and CONSTRAINT_NAME columns in matched rows.

Indexes used by constraint may have any names, there is no mandatory suffix. Constraints can and will use any compatible existing index instead of own one if such index already exist, so you cannot determine name of constraint from name of its index without INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.

There is also a special case for names in exceptions. It is possible to create an index with non-ASCII or non-printable characters. Index PUBLIC.Ä (or PUBLIC.U&"\00c4", both names are equivalent in SQL) will be reported as "PUBLIC.U&""\\00c4"" in exception, but you need to find a row with INDEX_NAME = 'Ä' (or INDEX_NAME = U&'\00c4').

[Tadashi Shibata]

Thank you Evgenij,

> H2 doesn't return names of constrains in these exceptions

No it doesn't but the error message contains name of the constraint, in some cases.

Hibernate can try to extract names of the constraints from the error messages, for example:
1) Unique index or primary key violation: "PUBLIC.ACCOUNTS_BY_USERNAME ON PUBLIC.ACCOUNTS(USERNAME) VALUES 1";...
2) Unique index or primary key violation: "PUBLIC.ACCOUNTS_BY_USERNAME_INDEX_A ON PUBLIC.ACCOUNTS(USERNAME)...
3) Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.ACCOUNTS(ID) [1, 'foo']"; SQL statement:...
In case 1, my fix works perfectly and ConstraintViolationException.getConstraintName() returns "ACCOUNTS_BY_USERNAME".
In case 2, H2 Database added suffix "_INDEX_A" to the constraint name. My fix does the same as case 1 and returns "ACCOUNTS_BY_USERNAME_INDEX_A", hoping the message is fixed someday.
My fix doesn't cover case 3 because the constraint name is not in the error message.

Is there any better way about case 2 and 3?

BR,
Tadashi

2022年12月25日日曜日 19:33:30 UTC+9 Evgenij Ryazanov: