Android中的SQLite:外键和<表约束>预期的 [英] SQLite in Android: Foreign Keys and <table constraint> expected

问题描述

我已经进行了一次搜索，试图了解这个问题的本质，但是到目前为止还没有任何运气.

I have had a search about to try to understand the nature of this problem, but have not had any luck as of yet.

我正在制作一个RPG风格的待办事项清单以自学Android/Java，并且正在制作两个表格:类别(强度，智能等)和任务:名称，说明，EXP等.

I'm making a RPG-style to-do list to teach myself Android/Java, and I'm making two tables: categories (strength, intelligence etc) and Quests: name, description, EXP etc.

我希望每个任务都有一个类别，但出现表格约束错误.我的代码在下面，并且GitHub链接为

I want each quest to have a category, but I'm getting a table constraints error. My code is below, and the GitHub link is here.

public void onCreate(SQLiteDatabase db){
    setForeignKeyConstraintsEnabled(db);

db.execSQL("CREATE TABLE " + CATEGORY_TABLE_NAME + "(id INTEGER primary key autoincrement NOT NULL, name TEXT, exp INTEGER, level INTEGER )");
db.execSQL("CREATE TABLE " + QUEST_TABLE_NAME + "(id INTEGER primary key autoincrement NOT NULL, name TEXT, description TEXT, expValue INTEGER, category INTEGER NOT NULL, FOREIGN KEY (category) REFERENCES categories (id), date TEXT");

db.execSQL("INSERT INTO " + CATEGORY_TABLE_NAME + "('name', 'exp', 'level') VALUES ('Strength', 0, 0 );");
db.execSQL("INSERT INTO " + CATEGORY_TABLE_NAME + "('name', 'exp', 'level') VALUES ('Stamina', 0, 0 );");
db.execSQL("INSERT INTO " + CATEGORY_TABLE_NAME + "('name', 'exp', 'level') VALUES ('Intelligence', 0, 0 );");
db.execSQL("INSERT INTO " + CATEGORY_TABLE_NAME + "('name', 'exp', 'level') VALUES ('Social', 0, 0 );");
db.execSQL("INSERT INTO " + QUEST_TABLE_NAME + "('name', 'description', 'expValue', 'category', 'date') VALUES ('Gym', 'Weightlifting down The Gym', '300', '1', '25/05/2017');");
}

错误发生在日期文本"处，并且错误显示:

The error is coming at 'date TEXT', and the error says:

<table constraint> expected, got 'date'.

这是什么问题?

推荐答案

您的问题是您将 column_constraint 语法与 table_constraint 语法( ie将后者编码在应该使用前者的位置 ).

Your issue is that you have mixed up column_constraint syntax with table_constraint syntax (i.e coded the latter where the former should be used).

您可以通过使用

db.execSQL("CREATE TABLE " + QUEST_TABLE_NAME + "(id INTEGER primary key autoincrement NOT NULL, name TEXT, description TEXT, expValue INTEGER, category INTEGER NOT NULL REFERENCES categories (id), date TEXT");

db.execSQL("CREATE TABLE " + QUEST_TABLE_NAME + "(id INTEGER primary key autoincrement NOT NULL, name TEXT, description TEXT, expValue INTEGER, category INTEGER NOT NULL REFERENCES categories (id), date TEXT");

下面将解释其他语法.

这是列约束语法以 REFERENCES .... 开头，并且是列定义的一部分(即列名是隐式的)，而table_constraint语法以 FORIEGN KEY(column_name) REFERENCES ...和遵循列定义

That is the column constraint syntax starts with the REFERENCES .... and is part of the column definition (i.e. the column name is implicit), whilst table_constraint syntax starts with FORIEGN KEY(column_name) REFERENCES ... and follows the column definitions

所以您可以选择:-

Column_constraint语法

• 作为列定义的一部分

• As part of a column definition

category INTEGER NOT NULL REFERENCES categories (id)

例如

CREATE TABLE yourtablename (id INTEGER primary key autoincrement NOT NULL, name TEXT, description TEXT, expValue INTEGER, category INTEGER NOT NULL REFERENCES categories (id), date TEXT)

或

Table_constraint语法

• 在定义了列之后，但仍在列定义之内，即仍然在方括号内.

• after the column's have been defined, but still within the column definition i.e. still inside the brackets.

FOREIGN KEY (category) REFERENCES categories (id)

例如

CREATE TABLE yourtablename (id INTEGER primary key autoincrement NOT NULL, name TEXT, description TEXT, expValue INTEGER, category INTEGER NOT NULL, date TEXT, FOREIGN KEY (category) REFERENCES categories (id));

您可能会找到列约束和

You may find column-constraint and table-constraint of use.

日期可以是列名.但是，我建议不要使用任何SQLite关键字(例如date)作为列名.

date can be a column name. However, I'd suggest that it is wise to not use any SQLite keyword, such as date, as a column name.

这篇关于Android中的SQLite:外键和<表约束>预期的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！