Foreign key constraint tied to same index generated for a unique constraint that uses that index

[Russ Jackson]

re:  h2database 1.4.196

We are noticing that the unique index generated for a unique constraint that includes a foreign keyed column is also being used as the index for that foreign key constraint.  Is this expected or should the foreign key get it's own index?

We noticed this because when dropping the unique constraint the auto generated unique index was not being dropped.

But, if we first drop the foreign key constraint and then the unique constraint the auto generated unique index is dropped.

Thanks.

[Evgenij Ryazanov]

Hello.

Indexes may be shared between constraints.

In the SQL Standard referential constraints may reference only columns with already existing unique constraint. It isn't ensured by released versions of H2, they allow referential constraints without unique constraints and reuse their indexes.

Current H2 doesn't require it too, but creates a unique constraint automatically if it doesn't exists. Attempt to drop the unique constraint is disallowed by default and allowed with CASCADE clause and with such clause the referential constraint is also dropped.

CREATE TABLE T1(ID INT, CONSTRAINT PK1 PRIMARY KEY(ID));
> ok
CREATE TABLE T2(ID INT, F INT,
    CONSTRAINT PK2 PRIMARY KEY(ID),
    CONSTRAINT FK2 FOREIGN KEY(F) REFERENCES T1);
> ok
ALTER TABLE T2 DROP CONSTRAINT PK1;
> Constraint "PUBLIC.PK1" is used by constraint "PUBLIC.FK2"
ALTER TABLE T2 DROP CONSTRAINT PK1 CASCADE;
> ok

[Carlos Eduardo Lopez Olvera]

Foreing KEY grupo index

[Carlos Eduardo Lopez Olvera]

[Carlos Eduardo Lopez Olvera]

[Russ Jackson]

Thanks for the responses.  Let me elaborate.  Here's my table structure:

CREATE TABLE T1 (

  id VARCHAR(50) NOT NULL,

  primary key (id)

);

CREATE TABLE T2 (

  id VARCHAR(50) NOT NULL,

  primary key (id)

);

  

CREATE TABLE T3 (

  id VARCHAR(50) NOT NULL,

  t1_id VARCHAR(50) NOT NULL,

  f1 INTEGER NOT NULL,

  f2 INTEGER NOT NULL,

  t2_id1 VARCHAR(50) NOT NULL,

  t2_id2 VARCHAR(50) NOT NULL,

  create_time TIMESTAMP NOT NULL,

  update_time TIMESTAMP,

  PRIMARY KEY (id),

  CONSTRAINT t3_uk UNIQUE (t1_id, f1),

  CONSTRAINT t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE RESTRICT,

  CONSTRAINT t2_fk1 FOREIGN KEY (t2_id1) REFERENCES t2 (id) ON DELETE RESTRICT,

  CONSTRAINT t2_fk2 FOREIGN KEY (t2_id2) REFERENCES t2 (id) ON DELETE RESTRICT

);

When I run this statement:

alter table t3 drop constraint t3_uk;

the unique index generated for the unique constraint is NOT dropped.

Adding CASCADE to the end of the drop statement results in an error in my case.

If I drop constraint t1_fk first and then t3_uk the unique index is also dropped.

So, sounds like if you define the foreign key constraint after the unique constraint that includes that FK field then you must drop the FK constraint first and then the unique constraint in order for the unique index to also be dropped.

[Evgenij Ryazanov]

By “current H2” I meant current sources of H2 on GitHub.

[Russ Jackson]

Thanks.  We're currently on version 196 from June 2017.  Problems with newer versions of H2 are preventing us from upgrading to the latest.

On Friday, April 24, 2020 at 9:22:04 AM UTC-5, Russ Jackson wrote: