[H2 V.2] FOREIGN KEY CONSTRAINT appears to be treated as if it were a PRIMARY KEY CONSTRAINT

[mew-tsukiyomuneko]

Description

It seems that the H2 V.2 Driver of DBeaver treats FOREIGN KEY CONSTRAINTs the same way as PRIMARY KEY CONSTRAINTs.

H2 V.2 or later versions, the value of the CONSTRAINT_TYPE column in INFORMATION_SCHEMA.TABLE_CONSTRAINTS is set to "FOREIGN KEY" for foreign key constraints, not to "REFERENTIAL".
(And it seems that this specification change has not yet been reflected correctly in the documentation for H2 V.2.)

It seems that this specification change is affecting methods, for example, such as prepareUniqueConstraintsLoadStatement() and getUniqueConstraintType() in org/jkiss/dbeaver/ext/h2/model/H2MetaModel.java.

DBeaver Version

Community Edition 24.0.4

Operating System

Windows 10 Pro 22H2 build 19045.4412

Database and driver

H2 Embedded V.2 (2.2.224)

Steps to reproduce

1. Create tables.

CREATE TABLE A (
	A_ID IDENTITY
);

CREATE TABLE B (
	B_ID IDENTITY,
	KEY_OF_A BIGINT NOT NULL,
	CONSTRAINT FK_B FOREIGN KEY (KEY_OF_A) REFERENCES A(A_ID)
);

2. Show DDL of Table B from Context menu -> "Generate SQL" -> "DDL".
3. Result. FOREIGN KEY Constraint 'FK_B' appears to be treated as if it were a PRIMARY KEY CONSTRAINT.

-- PUBLIC.B definition

-- Drop table

-- DROP TABLE PUBLIC.B;

CREATE TABLE PUBLIC.B (
	B_ID BIGINT NOT NULL AUTO_INCREMENT,
	KEY_OF_A BIGINT NOT NULL,
	CONSTRAINT CONSTRAINT_42 PRIMARY KEY (B_ID),
	CONSTRAINT FK_B PRIMARY KEY (KEY_OF_A)
);
CREATE INDEX FK_B_INDEX_4 ON PUBLIC.B (KEY_OF_A);
CREATE UNIQUE INDEX PRIMARY_KEY_42 ON PUBLIC.B (B_ID);


-- PUBLIC.B foreign keys

ALTER TABLE PUBLIC.B ADD CONSTRAINT FK_B FOREIGN KEY (KEY_OF_A) REFERENCES PUBLIC.A(A_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

Additional context

No response

[E1izabeth]

Thank you for bug report and investigation

[LonwoLonwo]

Foreign key recognition fixed. The fix will be available in release 24.1.0