为什么我无法在android SQLite上创建此表? [英] why i fail to create this table on android SQLite?

问题描述

我在应用程序上创建了4个表格. 下面是最后一个表的创建-

这是我在解析字符串之后得到的查询

如果表不存在则创建表TABLE_1(ID整数主键自动递增不为空，PK_Person文本不为空，外键(PK_Person)参考表_2，COLUMN_INDEX_AS_TEXT文本不为空，外键(COLUMS_INDEX___)；

当我运行这种创建方法时-我得到一个例外，即COLUMN_INDEX_AS_TEXT附近的值为

错误代码:1(SQLITE_ERROR) 引起原因:SQL(query)错误或数据库丢失. (在"COLUMN_INDEX_AS_TEXT"附近:语法错误(代码1):，而在编译时:如果不存在则创建表.....(查询)

我检查了该表之前创建的所有表是否都存在.

  try
    {
        SQLiteDatabase db = getWritableDatabase();



String query = "CREATE TABLE IF NOT EXISTS " + TABLE_1 + "("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person )" + " REFERENCES " + TABLE_2 + ", "
                + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL, FOREIGN KEY( " +  COLUMN_INDEX_AS_TEXT + " ) REFERENCES " + TABLE_3 + " );";




db.execSQL(query);
    }
    catch (Exception e)
    {
        Log.e(TAG, e.getMessage());
    }

解决方案

在sqlite理解的SQL中，表约束(例如FOREIGN KEY)遵循列规范.您不能将它们混合在一起.将FOREIGN KEY规范移动到CREATE TABLE的末尾，以使COLUMN_INDEX_AS_TEXT列规范位于它们之前.

I created 4 table on my app. Below is the creation of the last table -

this is the query that i get after the parse of the string

CREATE TABLE IF NOT EXISTS TABLE_1 (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person ) REFERENCES TABLE_2 , COLUMN_INDEX_AS_TEXT TEXT NOT NULL, FOREIGN KEY( COLUMN_INDEX_AS_TEXT ) REFERENCES TABLE_3 );

When i run this method of creation - i get an exception that the value near the COLUMN_INDEX_AS_TEXT is

Error Code : 1 (SQLITE_ERROR) Caused By : SQL(query) error or missing database. (near "COLUMN_INDEX_AS_TEXT ": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS ..... ( the query )

I check and all the table that i create before this table are exist.

  try
    {
        SQLiteDatabase db = getWritableDatabase();



String query = "CREATE TABLE IF NOT EXISTS " + TABLE_1 + "("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person )" + " REFERENCES " + TABLE_2 + ", "
                + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL, FOREIGN KEY( " +  COLUMN_INDEX_AS_TEXT + " ) REFERENCES " + TABLE_3 + " );";




db.execSQL(query);
    }
    catch (Exception e)
    {
        Log.e(TAG, e.getMessage());
    }

解决方案

In SQL as understood by sqlite, table constrainsts such as FOREIGN KEY follow column specifiations. You cannot intermix them. Move the FOREIGN KEY specs at the end of your CREATE TABLE so that the COLUMN_INDEX_AS_TEXT column spec is before them.

这篇关于为什么我无法在android SQLite上创建此表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！