Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
H2 Database
+ new post
About this group
Join this group
Can't drop and index because of an unknown constraint
Options
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
   6 messages - Collapse all  -  Translate all to Translated (View all originals)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
niels  
View profile   Translate to Translated (View Original)
 More options Oct 18 2012, 2:09 pm
From: niels <opensourc...@gmail.com>
Date: Thu, 18 Oct 2012 11:09:16 -0700 (PDT)
Local: Thurs, Oct 18 2012 2:09 pm
Subject: Can't drop and index because of an unknown constraint
Print | Individual message | Show original | Report this message | Find messages by this author

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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
niels  
View profile   Translate to Translated (View Original)
 More options Oct 18 2012, 2:33 pm
From: niels <opensourc...@gmail.com>
Date: Thu, 18 Oct 2012 11:33:12 -0700 (PDT)
Local: Thurs, Oct 18 2012 2:33 pm
Subject: Re: Can't drop and index because of an unknown constraint
Print | Individual message | Show original | Report this message | Find messages by this author

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

Am Donnerstag, 18. Oktober 2012 20:09:16 UTC+2 schrieb niels:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
niels  
View profile  
 More options Oct 19 2012, 2:40 pm
From: niels <opensourc...@gmail.com>
Date: Fri, 19 Oct 2012 20:40:56 +0200
Local: Fri, Oct 19 2012 2:40 pm
Subject: Re: Can't drop and index because of an unknown constraint
Print | Individual message | Show original | Report this message | Find messages by this author

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 <opensourc...@gmail.com>:

  test.db.h2.db
54K Download

  test.db.trace.db
12K Download

 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Noel Grandin  
View profile  
 More options Oct 23 2012, 9:52 am
From: Noel Grandin <noelgran...@gmail.com>
Date: Tue, 23 Oct 2012 15:51:51 +0200
Local: Tues, Oct 23 2012 9:51 am
Subject: Re: Can't drop and index because of an unknown constraint
Print | Individual message | Show original | Report this message | Find messages by this author

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 must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Thomas Mueller  
View profile  
 More options Oct 23 2012, 2:41 pm
From: Thomas Mueller <thomas.tom.muel...@gmail.com>
Date: Tue, 23 Oct 2012 20:41:17 +0200
Local: Tues, Oct 23 2012 2:41 pm
Subject: Re: Can't drop and index because of an unknown constraint
Print | Individual message | Show original | Report this message | Find messages by this author

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 must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
niels  
View profile  
 More options Oct 24 2012, 2:52 am
From: niels <opensourc...@gmail.com>
Date: Tue, 23 Oct 2012 23:52:28 -0700 (PDT)
Local: Wed, Oct 24 2012 2:52 am
Subject: Re: Can't drop and index because of an unknown constraint
Print | Individual message | Show original | Report this message | Find messages by this author

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:

  bug.sql
9K Download

 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google