java - Android sqlite邻接模型

Question

我是 Android 开发新手，正在从事一个需要数据库交互的项目当尝试使用 sqlite 中的邻接模型创建表但编译失败并显示此错误消息时

 |   id  |   parentid    |   name    |
-----------------------------------------
|   1    |   null        |   animal   |
|   2    |   null        |vegetable   |
|   3    |   1           |   doggie   |
|   4    |   2           |   carrot   |
|        |               |            |
|        |               |            |

这是我的代码:

private static final String CREATE_TABLE_CATEGORIES="CREATE TABLE "+TABLE_CATEGORIES+"(id INTEGER PRIMARY KEY AUTOINCREMENT,"+category_name+
            " TEXT,parentid INTEGER ,foreign key parentid_fk(parentid) references "+TABLE_CATEGORIES+" (id));";

@Override
    public void onCreate(SQLiteDatabase db){
        //Creation required tables


        db.execSQL(CREATE_TABLE_CATEGORIES);

    }
....
....
....
....
 @Override
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
        // on upgrade drop older tables
        db.execSQL("PRAGMA foreign_keys = ON;");
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_CATEGORIES);
        // create new tables
        onCreate(db);
    }

这是错误:

at dalvik.system.NativeStart.main(Native Method)
     Caused by: android.database.sqlite.SQLiteException: near "parentid_fk": syntax error (code 1): , while compiling: CREATE TABLE Categories(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,parentid INTEGER ,foreign key parentid_fk(parentid) references Categories (id));

Answer 1

要为外键约束命名，必须使用 CONSTRAINT 关键字:

CONSTRAINT parentid_fk FOREIGN KEY (parentid) REFERENCES Categories(id)

或者，不要给它命名:

FOREIGN KEY (parentid) REFERENCES Categories(id)