Search code examples
javahsqldb

HSQLDB - Referencing INFORMATION_SCHEMA in foreign key

I tried to create a foreign key on an existing table.
The FK should reference a column on a table contained into INFORMATION:SCHEMA.

However this doesn't seem to be possible.
Is that right?

UPDATE
This is what I execute (as the super user):
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_FK FOREIGN KEY (A, B, C) REFERENCES CATALOG.INFORMATION_SCHEMA.TABLES (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)

This is the error I get:
[42501][-5501] user lacks privilege or object not found: INFORMATION_SCHEMA.TABLES java.lang.RuntimeException: org.hsqldb.HsqlException: user lacks privilege or object not found: INFORMATION_SCHEMA.TABLES

Not very helpful since the user I'm using as all the privileges possible and the object definitely exists.

Solution

  • The INFORMATION_SCHEMA.TABLES is a VIEW and as such has no PRIMARY KEY constraint. Therefore it is not possible to create a FOREIGN KEY to reference it.