What's the problem with this statement that gives me the following exception?
s.addBatch("CREATE TABLE category ( id SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY \n" +
"\t(START WITH 0, INCREMENT BY 1), title VARCHAR(100))\n" );
s.addBatch("CREATE TABLE task (id SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY \n" +
"\t(START WITH 0, INCREMENT BY 1), title VARCHAR(100), cat_id INT, visible BOOLEAN, " +
"deprecated BOOLEAN" +
"CONSTRAINT fk_cat_id FOREIGN KEY (cat_id)\n" +
"\tREFERENCES category(id))");
s.executeBatch();
the first addBatch is commented in my running code because I'd created the table before. In fact if I don't comment the first batch I get an error saying that the table already exists and seeing this question I know it's the only way to check if a table exists in derby. I see that my statement is correct according to this documentation
java.sql.BatchUpdateException: Syntax error: Encountered "fk_cat_id" at line 2, column 119.
at org.apache.derby.impl.jdbc.Util.newBatchUpdateException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeLargeBatch(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeBatch(Unknown Source)
at model.DBConnection.createTables(DBConnection.java:48)
at model.DBConnection.<init>(DBConnection.java:33)
at model.DBConnection.<clinit>(DBConnection.java:10)
at test.DBTest.main(DBTest.java:11)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "fk_cat_id" at line 2, column 119.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeBatchElement(Unknown Source)
... 11 more
Caused by: java.sql.SQLException: Syntax error: Encountered "fk_cat_id" at line 2, column 119.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 19 more
Caused by: ERROR 42X01: Syntax error: Encountered "fk_cat_id" at line 2, column 119.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 13 more