java - 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 在我的运行代码中被注释，因为我之前创建了该表。事实上，如果我不评论第一批，我会收到一条错误消息，指出该表已存在并看到 this question我知道这是检查 Derby 中是否存在 table 的唯一方法。根据 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

Answer 1

sql语句有很多错误!

• 没有为该类别定义主键。
• category 中的 ID 类型与任务中的 cat_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))");

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