Foreign key referencing INFORMATION_SCHEMA.TABLES(TABLENAME) causes createIndex() to throw Exception

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Create a table with a column that is defined as a foreign key
referencing INFORMATION_SCHEMA.TABLES(TABLENAME).
2. Observe that H2 will attempt to create an index on
INFORMATION_SCHEMA.TABLES.  This fails.

What is the expected output? What do you see instead?
I was hoping I could define a column whose values must be the names of
existing tables.

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2 v1.128.

Do you know a workaround?
No.

How important/urgent is the problem for you?
Low priority.

In your view, is this a defect or a feature request?
Defect.

Please provide any additional information below.
Here is the stack trace:
org.h2.jdbc.JdbcSQLException: Feature not supported: "META"; SQL statement:
CREATE TABLE foo.bar ( id INTEGER PRIMARY KEY, name VARCHAR(20) NOT NULL,
FOREIGN KEY (name) REFERENCES INFORMATION_SCHEMA.TABLES(TABLE_NAME) )
[50100-128]
    at org.h2.message.Message.getSQLException(Message.java:110)
    at org.h2.message.Message.getSQLException(Message.java:121)
    at org.h2.message.Message.getSQLException(Message.java:74)
    at org.h2.message.Message.getUnsupportedException(Message.java:166)
    at org.h2.table.MetaTable.addIndex(MetaTable.java:549)
    at
org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.j
ava:275)
    at
org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.jav
a:226)
    at
org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:8
9)
    at org.h2.command.ddl.CreateTable.update(CreateTable.java:162)
    at org.h2.command.CommandContainer.update(CommandContainer.java:71)
    at org.h2.command.Command.executeUpdate(Command.java:199)
    at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:124)
    at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:109)

Original issue reported on code.google.com by ljnelson on 3 Feb 2010 at 7:55

[GoogleCodeExporter]

Sorry, that should be TABLE_NAME, not TABLENAME.

Original comment by ljnelson on 3 Feb 2010 at 8:07

[GoogleCodeExporter]

Currently it is not possible to create indexes on meta data tables, because 
those
tables are not persisted. Because of that, triggers and foreign key constraints 
are
not supported on those tables.

I will add a feature request, however it is unlikely that I can implement this
feature in the near future.

Original comment by thomas.t...@gmail.com on 6 Feb 2010 at 11:55

[GoogleCodeExporter]

Sure; obviously this is not a big deal.  The workaround is to use a check 
constraint,
which I've done.  Thanks for a great product.

Original comment by ljnelson on 6 Feb 2010 at 4:09

[GoogleCodeExporter]

This issue is now in the roadmap.

Original comment by thomas.t...@gmail.com on 20 Feb 2010 at 9:45

• Changed state: InRoadmap