Derby外键约束

Question

这个声明有什么问题，它给了我以下的例外？

    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();

第一个addBatch是在我运行的代码中注释的，因为我以前创建过这个表。事实上，如果我不对第一批进行注释，就会发现一个错误，即表已经存在，并且看到这个问题，我知道这是检查derby中是否存在表的唯一方法。根据这份文件的说法，我的陈述是正确的

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

Answer 1

sql语句中有许多错误！

• 未为类别定义主键。
• 任务中的category和cat_id中的id类型不匹配。
• 正如PM77-1所指出的，这些空格没有得到尊重。

无论如何，工作代码如下：

s.addBatch("CREATE TABLE category ( id  INT NOT NULL GENERATED ALWAYS AS IDENTITY \n" +
        "\t(START WITH 0, INCREMENT BY 1), title  VARCHAR(100), " +
        "\tCONSTRAINT category_pk_id PRIMARY KEY (id))\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," +
        "\t CONSTRAINT task_pk_id PRIMARY KEY (id)," +
        "\t CONSTRAINT fk_cat_id FOREIGN KEY (cat_id)\n" +
        "\t REFERENCES category(id))");

但是，在代码中仍然会有一个错误，即这些操作不是原子操作，如果一个操作发生而下一个操作出错，则第一个操作不会回滚。这不是未解决的问题。