creating db table with foreign key ANDROID

Question

Im trying to created a db that (at the moment) has 4 tables {USERS,TOPICS,GROUPS,CREATED_TOPICS}. I keep getting this error when i try and run it...

syntax error (code 1): , while compiling: create table created_topics_table (CREATED_TOPIC_ID INTEGER PRIMARY KEY AUTOINCREMENT, TOPIC_ID INTEGER,USER_ID INTEGER, FOREIGN KEY(TOPIC_ID) REFERENCES topics_table(_ID),FOREIGN KEY(USER_ID) REFERENCES users_table(_ID).

heres the code...

public class databaseHelper extends SQLiteOpenHelper
{
//DATABASE NAME
public static final String DATABASE_NAME = "users.db";
//USERS TABLE
public static final String TABLE_NAME_USERS = "users_table";
public static final String COL_USER_ID = "_ID";
public static final String COL_USER_NAME = "USER_NAME";
public static final String COL_USER_EMAIL = "USER_EMAIL";
public static final String COL_USER_PASSWORD = "USER_PASSWORD";
//TOPICS TABLE
public static final String TABLE_NAME_TOPICS = "topics_table";
public static final String COL_TOPIC_ID = "_ID";
public static final String COL_TOPIC_NAME = "TOPIC_NAME";


//GROUPS TABLE
public static final String TABLE_NAME_GROUPS = "groups_table";
public static final String COL_GROUP_ID = "GROUP_ID";
public static final String COL_GROUP_NAME = "GROUP_NAME";

public static final String TABLE_NAME_CREATED_TOPICS =
"created_topics_table";
public static final String COL_CREATED_TOPIC_ID = "CREATED_TOPIC_ID";
public static final String COL_FK_TOPIC_TOPIC_ID = "TOPIC_ID";
public static final String COL_FK_TOPIC_USER_ID = "USER_ID";

public void onCreate(SQLiteDatabase db) {
db.execSQL("create table " + TABLE_NAME_USERS + " (_ID INTEGER PRIMARY KEY   
AUTOINCREMENT,USER_NAME TEXT,USER_EMAIL TEXT,USER_PASSWORD TEXT)");
db.execSQL("create table " + TABLE_NAME_TOPICS + " (_ID INTEGER PRIMARY KEY     
AUTOINCREMENT,TOPIC_NAME TEXT)");
db.execSQL("create table " + TABLE_NAME_GROUPS + " (GROUPS_ID INTEGER PRIMARY    
KEY AUTOINCREMENT,GROUP_NAME TEXT)");
db.execSQL("create table " + TABLE_NAME_CREATED_TOPICS + " (CREATED_TOPIC_ID 
INTEGER PRIMARY KEY AUTOINCREMENT, TOPIC_ID INTEGER,USER_ID INTEGER, FOREIGN
KEY(TOPIC_ID) REFERENCES " +TABLE_NAME_TOPICS + "(_ID)," +                                                                                                                                                            
"FOREIGN KEY(USER_ID) REFERENCES " +TABLE_NAME_USERS + "(_ID)");
}

which DB are you using? shouldn't AUTOINCREMENT be AUTO_INCREMENT? — Deendayal Garg
AUTOINCREMENT works, no need for the underscore, and im just using the bulit in sqlite db — luke

Pooya Pooya · Accepted Answer · 2016-05-02T05:15:13

You missed closing parentheses at the end:

create table created_topics_table (CREATED_TOPIC_ID INTEGER PRIMARY KEY AUTOINCREMENT, TOPIC_ID INTEGER,USER_ID INTEGER, FOREIGN KEY(TOPIC_ID) REFERENCES topics_table(_ID),FOREIGN KEY (USER_ID) REFERENCES users_table(_ID))

One other note on sqlite foreign keys. They are off by default. You need to use "PRAGMA foreign_keys=ON" every time you open database connection

EDIT

Change:

db.execSQL("create table " + TABLE_NAME_CREATED_TOPICS + " (CREATED_TOPIC_ID 
INTEGER PRIMARY KEY AUTOINCREMENT, TOPIC_ID INTEGER,USER_ID INTEGER, FOREIGN
KEY (TOPIC_ID) REFERENCES " +TABLE_NAME_TOPICS + " (_ID)," +                                                                    
"FOREIGN KEY (USER_ID) REFERENCES " +TABLE_NAME_USERS + " (_ID))");

creating db table with foreign key ANDROID

2 Answers