Name for Constraint Unique

[Pylou Ej]

-- Hello I want to add a constraint on a column but when I send the SQL command the BDD adds _INDEX_3 to the end of the name... How do we stop this?

query

ALTER TABLE GS_PAYS ADD CONSTRAINT GS_PAYS_UN1_1 UNIQUE (GS_PAYS_LIB);

result

GS_PAYS_UN1_1_INDEX_3

[Evgenij Ryazanov]

Hello.

H2 preserves the specified name of constraint as is. Your command creates a constraint with the name GS_PAYS_UN1_1. This constraint in H2 needs a compatible index on column GS_PAYS_LIB. If such index wasn't found, H2 creates its automatically and name of such index is autogenerated. In your case in can be GS_PAYS_UN1_1_INDEX_3 or some else.

It looks like you check the result in the wrong place. You can read constraints from INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.

[Pylou Ej]

I need to put a single value constraint on a column. So you advise me to create an index and then assign it the unique constraint?

[Pylou Ej]

Thank you very much.

[Pylou Ej]

CREATE UNIQUE INDEX GS_PAYS_UN1_1 ON GS_PAYS (GS_PAYS_LIB);

[Evgenij Ryazanov]

On Tuesday, 19 January 2021 at 21:34:58 UTC+8 pylo...@gmail.com wrote:

I need to put a single value constraint on a column. So you advise me to create an index and then assign it the unique constraint?

You normally should create only the constraint, unless you need something special from its index. For example, if you always use ORDER BY GS_PAYS_LIB DESC in your queries it would be better to create a unique index with the descending order before creation of the constraint. The constraint will be able to use that index too instead of creation of own one (with default ascending order).

I just informed you that you read the name of internal indes from somewhere; it wasn't the name of your constraint. Constraints and indexes are different things and information about them are reported in different places.

Usage of unique indexes instead of unique constraints is usually a bad idea. Indexes normally should only be used to improve efficiency of your queries; they are not standardized and they are not very portable between different DBMS.