德比外键约束 [英] Derby foreign key constraint

问题描述

  s.addBatch（CREATE TABLE category（id SMALLINT NOT NULL GENERATED ALWAYS作为标识\\\
+ 
\ t（START WITH 0，INCREMENT BY 1），title VARCHAR（100））\ n）; 
 s.addBatch（CREATE TABLE task（id SMALLINT NOT NULL GENERATED ALWAYS AS ASENENTITY \\\
+ 
\t（START WITH 0，INCREMENT BY 1），title VARCHAR（100）， cat_id INT，visible BOOLEAN，+ 
弃用BOOLEAN+ 
CONSTRAINT fk_cat_id FOREIGN KEY（cat_id）\\\
+ 
\ tREFERENCES category（id）））; 
 s.executeBatch（）; 
  

第一个addBatch在我的运行代码中被注释掉了，因为我之前创建了这个表。事实上，如果我不评论第一批，我得到一个错误，说该表已经存在，并看到这个问题我知道这是唯一的方法来检查表中是否存在德比。我发现，根据这个文档

  java.sql.BatchUpdateException：语法错误：在第2行第119列遇到fk_cat_id。
 at org.apache.derby.impl.jdbc.Util.newBatchUpdateException（Unknown Source）
 org.apache.derby.impl.jdbc.EmbedStatement.executeLargeBatch（Unknown Source）
 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.DelegatingMeth odAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43）
 at java.lang.reflect.Method.invoke（Method.java:601）
 at com.intellij.rt.execution.application.AppMain.main（ AppMain.java:120）
导致：java.sql.SQLSyntaxErrorException：语法错误：在第2行第119列遇到fk_cat_id。
在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）
 org。 apache.derby.impl.jdbc.ConnectionChild.handleException（Unknown Source）
在org.apache.derby.impl.jdbc.EmbedStatement.execute（Unknown Source）
在org.apache.derby.impl。 jdbc.EmbedStatement.executeBatch元素（未知源）
 ... 11更多
导致：java.sql.SQLException：语法错误：遇到fk_cat_id在第2行第119列。
在org.apache。 derby.impl.jdbc.SQLExceptionFactory.getSQLException（Unknown Source）
 at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA（Unknown Source）
 ... 19 more 
导致：错误42X01：语法错误：在第2行，第119列遇到fk_cat_id。
 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 
  

解决方案

重新在sql语句中有很多错误！
$ b

• 没有为类别定义主键。
  
• <$ c任务中的$ c> category 和 cat_id 不匹配。
  
• 作为







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

$ $ p $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ b\ 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 ASENENTITY \\\
+
\t（START WITH 0，INCREMENT BY 1），title VARCHAR（100）， cat_id INT，可见BOOLEAN，+
弃用BOOLEAN，+
\ t CONSTRAINT task_pk_id PRIMARY KEY（id），+
\ t CONSTRAINT fk_cat_id FOREIGN KEY（cat_id ）\\\
+
\ t REFERENCES category（id）））;


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

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

解决方案

There are many errors in the sql statement!

• No primary key is defined for the category.
• The type of id in category and cat_id in task don't match.
• As PM77-1 has noted the spaces aren't respected.

Anyway the working code is the following:

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

But there will still be an error in the code that these operations aren't atomic and if one happens and the next goes wrong the first isn't rolled back. It is not unsolved.

这篇关于德比外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！