[Solved] LibreOffice Base: Index not found error

[KaziiTheAvali]

I am having trouble trying to remove the unique form a chart for my database class. i need 2 things to be the same and its throwing this error whenever i try to make it unique.

[Mr.Dandy]

The database seems to be corrupted.
Which format is used: HSQL native or external, Firebird, another?

[KaziiTheAvali]

unshure the default

[KaziiTheAvali]

it was a new HSQLDB Embedded database. it does that for any database like that i try to make

[Mr.Dandy]

Are you sure that all database packages has been installed?

[UnklDonald418]

Embedded HSQL doesn't support SHEMA queries, so trouble shooting your Index issue will be difficult.
My suggestion would be to create a new table without any added Indexes then copy your data into the new table.
A Unique constraint can be added to a table using SQL commands at Tools>SQL
The basic form is

Code: Select all

ALTER TABLE "SomeTableName" ADD CONSTRAINT "ConstraintTitle" UNIQUE("ColumnName");

If there is an existing violation of the Unique constraint an error message will tell you so, otherwise HSQL will enforce that constraint on all future entries into the table.
The "ConstraintTitle" is optional, if you skip that HSQL will create one.
The advantage of named constraints is that later you can easily remove "ConstraintTitle"

Code: Select all

ALTER TABLE "SomeTableName" DROP CONSTRAINT "ConstraintTitle";

[KaziiTheAvali]

Are you sure that all database packages has been installed?

witch database packages needs to be installed

[UnklDonald418]

I doubt there is a problem with your installation but I need correct my earlier statement about SCHEMA queries and Embedded databases. The following query should list all the constraints

Code: Select all

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS

For your "unique" issue, look for tables in the list with UNIQUE type constraints and note the TABLE_NAME and CONSTRAINT_NAME. Constraints can be removed at TOOLS>SQL with a statement of the format

Code: Select all

ALTER TABLE "TABLE_NAME" DROP CONSTRAINT "CONSTRAINT_NAME";

The Index GUI might be helpful when using other types of database connections but Embedded HSQL builds indexes as needed, so you shouldn't need it there.
Of course once you remove the UNIQUE constraint and you add a record with duplicate data, you can't go back without first removing the duplication.