CODEWITHSUNDEEP
androiddatabasesqliteforeign-keyssyntax-error
JMD123
JMD123

Reputation: 47

Android SQLite Foreign Key Syntax Error

I'm just starting to play around with SQLite databases in Android apps. I'm trying to make a 'team' table, and a 'player' table, and I want the 'player' table to have a foreign key which references the 'team' table's auto-increment _ID field (I think this _ID field comes from implementing BaseColumns in my contract class). I thought I had the syntax correct but I get the following error (even after uninstalling and reinstalling my app)

06-08 20:26:22.763: E/Database(29843): Failure 1 (near "playerName": syntax error) on 0x22a110 when preparing 'CREATE TABLE player (_id INTEGER PRIMARY KEY,team_ID INTEGER, FOREIGN KEY(team_ID) REFERENCES team(_id), playerName TEXT,playerAge INTEGER,playerPosition TEXT,playerHeight REAL,playerWeight REAL,playerPhoto TEXT,playerNumber INTEGER)'.

The error above shows the SQL statement that ends up trying to go through. This is the code for making the SQL statement (sorry it's unnecessarily wordy, I was following a tutorial):

private static final String SQL_CREATE_ENTRIES = "CREATE TABLE "
        + TeamTable.TABLE_NAME + " (" + TeamTable._ID
        + " INTEGER PRIMARY KEY," + TeamTable.COLUMN_NAME_TEAM_NAME
        + TEXT_TYPE + COMMA_SEP + TeamTable.COLUMN_NAME_TEAM_WINS
        + TEXT_TYPE + COMMA_SEP + TeamTable.COLUMN_NAME_TEAM_LOSSES
        + TEXT_TYPE + COMMA_SEP + TeamTable.COLUMN_NAME_TEAM_LOGO
        + TEXT_TYPE + " )";

private static final String SQL_CREATE_ENTRIES_2 = "CREATE TABLE "
        + PlayerTable.TABLE_NAME + " (" + PlayerTable._ID
        + " INTEGER PRIMARY KEY," + "team_ID INTEGER, "
        + "FOREIGN KEY(team_ID) REFERENCES team(_id), "
        + PlayerTable.COLUMN_NAME_PLAYER_NAME + TEXT_TYPE + COMMA_SEP
        + PlayerTable.COLUMN_NAME_PLAYER_AGE + " INTEGER" + COMMA_SEP
        + PlayerTable.COLUMN_NAME_PLAYER_POSITION + TEXT_TYPE + COMMA_SEP
        + PlayerTable.COLUMN_NAME_PLAYER_HEIGHT + " REAL" + COMMA_SEP
        + PlayerTable.COLUMN_NAME_PLAYER_WEIGHT + " REAL" + COMMA_SEP
        + PlayerTable.COLUMN_NAME_PLAYER_PHOTO + TEXT_TYPE + COMMA_SEP
        + PlayerTable.COLUMN_NAME_PLAYER_NUMBER + " INTEGER" + ")";

Then I use execSQL(SQL_CREATE_ENTRIES) and execSQL(SQL_CREATE_ENTRIES_2, and the error appears. I have tried creating only the 'team' table and it seems to work okay. I'm even able to read some data from the 'team' table and display the value in a TextView. But when the 'player' table is introduced things stop working. I am probably missing something very obvious but I can't seem to find it.

Any help is greatly appreciated!

Upvotes: 3

Views: 3011

Answers (1)

AlexBcn
AlexBcn

Reputation: 2460

Declare first all the columns and put the FOREIGN KEY(team_ID) REFERENCES team(_id) after the last column.

So the final query is like

CREATE TABLE player
(_id INTEGER PRIMARY KEY,
team_ID INTEGER, 
playerName TEXT,
playerAge INTEGER,
playerPosition TEXT,
playerHeight REAL,
playerWeight REAL,
playerPhoto TEXT,
playerNumber INTEGER,
FOREIGN KEY(team_ID) REFERENCES team(_id));

Upvotes: 5

Related Questions