CODEWITHSUNDEEP
javaandroidsqlite
moe_nyc
moe_nyc

Reputation: 307

Using References in SQLite

I am trying to create two tables (Users,Messages). The users table which consists of a primary key(RoomID) which I will reference to in my Messages tables. This is the query I am attempting to run.

String CREATE_TABLE_MESSAGES = "CREATE TABLE " + Messages.TABLE  + "( "
                + "FOREIGN KEY(" + Messages.KEY_ROOMID  + ") REFERENCES "+ChatRooms.TABLE+"("+ChatRooms.KEY_ROOMID+"), "
                + Messages.MESSAGE+ " TEXT, "
                + Messages.SIDE+ " BOOLEAN, "
                + Messages.TYPE + " TEXT )";

However, I keep running into this error

09-28 18:45:14.803  14760-14789/lltest.mohsin.com.chattest E/SQLiteLog﹕ (1) near "FOREIGN": syntax error
09-28 18:45:14.807  14760-14789/lltest.mohsin.com.chattest W/System.err﹕ android.database.sqlite.SQLiteException: near "FOREIGN": syntax error (code 1): , while compiling: CREATE TABLE Messages( FOREIGN KEY(RoomID) REFERENCES ChatRooms(RoomID), Message TEXT, Side BOOLEAN, Type TEXT )
09-28 18:45:14.807  14760-14789/lltest.mohsin.com.chattest W/System.err﹕ at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
09-28 18:45:14.807  14760-14789/lltest.mohsin.com.chattest W/System.err﹕ at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
09-28 18:45:14.807  14760-14789/lltest.mohsin.com.chattest W/System.err﹕ at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
09-28 18:45:14.811  14760-14789/lltest.mohsin.com.chattest W/System.err﹕ at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)

What am I doing wrong?

Upvotes: 0

Views: 423

Answers (1)

Andreas
Andreas

Reputation: 159165

Your FOREIGN KEY definition should follow the column definitions.

See example in SQLite Manual: https://www.sqlite.org/foreignkeys.html

Once you do that, it will fail again, because the foreign key column (RoomID) is undefined.

Upvotes: 1

Related Questions