Can't drop and index because of an unknown constraint

[niels]

I try the following

DROP INDEX UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG;

and get

 [Error Code: 90085, SQL State: 90085]  Index "UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG" gehört zu einer Bedingung

Index "UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG" belongs to a constraint; SQL statement:

DROP INDEX UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG [90085-149]

I followed the documentation and try

 SELECT * FROM INFORMATION_SCHEMA.CONSTRAINTS

 WHERE UNIQUE_INDEX_NAME = 'UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG';

But the result is empty.

Calling

ALTER TABLE ZEUGNIS_FORMULAR  DROP CONSTRAINT  UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG;

returns

Constraint "UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG" not found; SQL statement:

ALTER TABLE ZEUGNIS_FORMULAR  DROP CONSTRAINT  UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG [90057-149]

ALTER TABLE ZEUGNIS_FORMULAR  DROP CONSTRAINT  UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG;

To make it reproducible I tried just to create the one table and the constraints. As a result I figured out that it seems to be a problem with some foreign keys, because if I don't create them all works fine. 

Can someone give me a hint how to find the reason.  I can't understand why I can't drop the INDEX.

Regards 

Niels

[niels]

Here the create sqls

CREATE TABLE ZEUGNIS_FORMULAR (

ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, 

VERSION BIGINT NOT NULL, 

AUSGABE_DATUM TIMESTAMP NOT NULL, 

BESCHREIBUNG VARCHAR(50) NOT NULL, 

KLASSE_ID BIGINT NOT NULL, 

LEITSPRUCH VARCHAR(300), 

LEITSPRUCH2 VARCHAR(300), 

NACHTEILS_AUSGLEICHS_DATUM TIMESTAMP NOT NULL, 

QUELLE_LEITSPRUCH VARCHAR(30), 

QUELLE_LEITSPRUCH2 VARCHAR(30), 

SCHULHALBJAHR_ID BIGINT NOT NULL, 

TEMPLATE_FILE_NAME VARCHAR(255) NOT NULL, 

CONSTRAINT PK_ZEUGNIS_FORMULAR PRIMARY KEY (ID));

and

CREATE UNIQUE INDEX UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG ON ZEUGNIS_FORMULAR(KLASSE_ID, SCHULHALBJAHR_ID, BESCHREIBUNG);

I can provide the whole database or the complete SQL-scripts if it helps.

Niels

[niels]

I attached the db which only contains the necessary tables. It works
if I drop the foreign keys. But I used it with liquibase and don't
like it to have there always special statement for h2. Furthermore I
don't understand why the foreign keys are the problem.
I drop the fk and recreate them after that I can drop the index. Looks
like a bug to me.

I added the SQLs

This is what liquibase said it will do:
ALTER TABLE PUBLIC.ZEUGNIS_FORMULAR ADD CONSTRAINT FK62F9A9906105B64D
FOREIGN KEY (SCHULHALBJAHR_ID) REFERENCES PUBLIC.SCHULHALBJAHR (ID) ON
UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE PUBLIC.ZEUGNIS_FORMULAR ADD CONSTRAINT FK62F9A99040A05D6E
FOREIGN KEY (KLASSE_ID) REFERENCES PUBLIC.KLASSE (ID) ON UPDATE
RESTRICT ON DELETE RESTRICT;

CREATE UNIQUE INDEX UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG
ON ZEUGNIS_FORMULAR(KLASSE_ID, SCHULHALBJAHR_ID, BESCHREIBUNG);

This is what I found in the systems tables:
ALTER TABLE PUBLIC.ZEUGNIS_FORMULAR ADD CONSTRAINT
PUBLIC.FK62F9A9906105B64D FOREIGN KEY(SCHULHALBJAHR_ID) INDEX
PUBLIC.FK62F9A9906105B64D_INDEX_1 REFERENCES PUBLIC.SCHULHALBJAHR(ID)
NOCHECK
ALTER TABLE PUBLIC.ZEUGNIS_FORMULAR ADD CONSTRAINT
PUBLIC.FK62F9A99040A05D6E FOREIGN KEY(KLASSE_ID) REFERENCES
PUBLIC.KLASSE(ID) NOCHECK

CREATE UNIQUE INDEX
PUBLIC.UK_ZEUGNIS_FORMULAR_HALBJAHR_KLASSE_BESCHREIBUNG ON
PUBLIC.ZEUGNIS_FORMULAR(KLASSE_ID, SCHULHALBJAHR_ID, BESCHREIBUNG)

Hope that someone can give me some hints what I must provide to get help.

Niels


2012/10/18 niels <openso...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/Ab730i_V76gJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Noel Grandin]

On 2012-10-19 20:40, niels wrote:
> I attached the db which only contains the necessary tables. It works
> if I drop the foreign keys. But I used it with liquibase and don't
> like it to have there always special statement for h2. Furthermore I
> don't understand why the foreign keys are the problem.
> I drop the fk and recreate them after that I can drop the index. Looks
> like a bug to me.
>

I fixed a bug in H2 around foreign keys and indexes around a week ago,
which will probably fix your problem.

[Thomas Mueller]

Hi,

FYI, the latest build is available for testing at: http://www.h2database.com/automated/h2-latest.jar

See also http://h2database.com/html/download.html

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[niels]

Hi thanks for the answers. Unfortunately it doesn't help. 

Here is a script which shows the bug

java -cp h2-1.3.169.jar org.h2.tools.RunScript -url jdbc:h2:test  -user sa -script bug.sql

I hope it's small enough. It only handles 3 tables. Perhaps I could remove some columns but the tables are small.

I would really appreciate if you can give me some feedback, how this issue will be handled and if you have enough information.

Niels

Am Dienstag, 23. Oktober 2012 20:41:25 UTC+2 schrieb Thomas Mueller:

Hi,

FYI, the latest build is available for testing at: http://www.h2database.com/automated/h2-latest.jar

See also http://h2database.com/html/download.html

Regards,

Thomas

On Tue, Oct 23, 2012 at 3:51 PM, Noel Grandin <noelg...@gmail.com> wrote:

On 2012-10-19 20:40, niels wrote:

I attached the db which only contains the necessary tables. It works
if I drop the foreign keys. But I used it with liquibase and don't
like it to have there always special statement for h2. Furthermore I
don't understand why the foreign keys are the problem.
I drop the fk and recreate them after that I can drop the index. Looks
like a bug to me.

I fixed a bug in H2 around foreign keys and indexes around a week ago, which will probably fix your problem.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.