SQLite in Android: Foreign Keys and <table constraint> expected

Question

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

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.

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'.

What's the problem here?

try put it (date Text) before Foreign key i'm not sure but i think you need to put all colums before foreign key — Crammeur
– Crammeur, Commented Nov 10, 2017 at 21:29
@crammeur If I move date before the FOREIGN KEY bit, I get the following error: ')', DEFERRABLE, MATCH, NOT, ON or comma expected, unexpected end of file — James Stewart
– James Stewart, Commented Nov 10, 2017 at 22:01
I got it working with your suggestions! I used: db.execSQL("CREATE TABLE " + QUEST_TABLE_NAME + "(id INTEGER primary key autoincrement NOT NULL, name TEXT, description TEXT, expValue INTEGER, date TEXT, category INTEGER NOT NULL, FOREIGN KEY (category) REFERENCES '+CATEGORY_TABLE_NAME+' (id))"); — James Stewart
– James Stewart, Commented Nov 10, 2017 at 22:08

MikeT · Accepted Answer · 2017-11-11 03:26:29Z

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).

You could fix the issue by using

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

As is explained below as is the alternative syntax.

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

So you could have either :-

Column_constraint syntax

As part of a column definition
category INTEGER NOT NULL REFERENCES categories (id)

e.g.

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)

or

Table_constraint syntax

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)

e.g

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.

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.

Collectives™ on Stack Overflow

SQLite in Android: Foreign Keys and <table constraint> expected

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related