I have the following table and constraint check:
CREATE TABLE IF NOT EXISTS TEST_TABLE (
ID
BIGINT
NOT NULL
AUTO_INCREMENT,
TS
TIMESTAMP
NOT NULL
DEFAULT CURRENT_TIMESTAMP(),
PRIMARY KEY (ID, TS),
TEST_NUMBER
VARCHAR(25)
NOT NULL,
TEST_DESCRIPTION
CLOB
NOT NULL
DEFAULT ''
);
ALTER TABLE TEST_TABLE ADD CONSTRAINT TEST_TABLE_UNIQUE_TEST_NUMBER CHECK (
(
SELECT COUNT(*)
FROM TEST_TABLE AS TMP
WHERE
TMP.ID <> TEST_TABLE.ID AND
TMP.TEST_NUMBER = TEST_TABLE.TEST_NUMBER
) = 0
);
Now if I alter the table:
ALTER TABLE TEST_TABLE ADD COLUMN NEW_COLUMN BIGINT;
I receive the following error and an additional copy of the table exists
(TEST_TABLE_COPY_5_8) with the modification completed.
Column "TEST_TABLE.ID" not found; SQL statement:
ALTER TABLE TEST_TABLE ADD COLUMN NEW_COLUMN BIGINT [42122-192] 42S22/42122
(Help)
org.h2.jdbc.JdbcSQLException: Column "TEST_TABLE.ID" not found; SQL
statement:
ALTER TABLE TEST_TABLE ADD COLUMN NEW_COLUMN BIGINT [42122-192]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:147
)
at org.h2.expression.Comparison.optimize(Comparison.java:180)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:130)
at org.h2.command.dml.Select.prepare(Select.java:855)
at org.h2.engine.Session.optimizeQueryExpression(Session.java:232)
at org.h2.expression.Subquery.optimize(Subquery.java:72)
at org.h2.expression.Comparison.optimize(Comparison.java:178)
at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(
AlterTableAddConstraint.java:191)
at org.h2.command.ddl.AlterTableAddConstraint.update(
AlterTableAddConstraint.java:77)
at org.h2.command.ddl.AlterTableAlterColumn.execute(
AlterTableAlterColumn.java:488)
at org.h2.command.ddl.AlterTableAlterColumn.cloneTableStructure(
AlterTableAlterColumn.java:424)
at org.h2.command.ddl.AlterTableAlterColumn.copyData(
AlterTableAlterColumn.java:254)
at org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn
.java:177)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
at org.h2.server.web.WebApp.getResult(WebApp.java:1388)
at org.h2.server.web.WebApp.query(WebApp.java:1061)
at org.h2.server.web.WebApp$1.next(WebApp.java:1023)
at org.h2.server.web.WebApp$1.next(WebApp.java:1010)
at org.h2.server.web.WebThread.process(WebThread.java:164)
at org.h2.server.web.WebThread.run(WebThread.java:89)
at java.lang.Thread.run(Unknown Source)
Even if REFERENTIAL_INTEGRITY is set to FALSE and/or the table is empty I
still get the error. The way around this is to delete the constraint, make
the alteration, and then recreate the constraint.
My questions are:
1. Should I really be getting this error? It seems that H2 is evaluating
the SQL of the constraint check and it's recognizing the table dependency
which is violated when H2 does behind-the-scenes copy-alter-delete-rename
operations (I don't know the specifics of this).
2. Is there a way to do the constraint check with a query that doesn't have
a hard-coded table name? Example SELECT FROM _SELF? I assume something like
this would allow normal ALTER operations.
3. Is there a better way to accomplish this? Preferably with straight SQL
and not any a Java function.
Thanks!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
