How to create foreign key in a table?

Question

I have tried to create foreign key in table but it's throwing error

        @Override
        public void onCreate(SQLiteDatabase db) {    
        db.execSQL(" CREATE TABLE " + TABLE_NAME + "( "+ KEY_TEXT + " TEXT NOT NULL, " +
                            KEY_DATE + " TEXT NOT NULL PRIMARY KEY UNIQUE, " +
                            KEY_LOCATION + " TEXT , " +KEY_SMILEY+ " TEXT, " 
                            + KEY_IMAGE + " BLOB , " + KEY_AUDIO + " BLOB, " 
                            + KEY_VIDEO + " BLOB, " + KEY_ID+" INTEGER "+");"
                            );
        db.execSQL(" CREATE TABLE " + IMAGES_TABLE + "( "+ KEY_DATE + " TEXT NOT NULL FOREIGN       KEY(date) REFERENCES Diary(date) , 
"+ KEY_IMAGE + " BLOB , " + KEY_ID+ " INTEGER  PRIMARY KEY "+");");
    }

Logcat error

04-02 11:04:41.699: E/SQLiteLog(24544): (1) near "FOREIGN": syntax error
04-02 11:04:41.699: D/AndroidRuntime(24544): Shutting down VM
04-02 11:04:41.699: W/dalvikvm(24544): threadid=1: thread exiting with uncaught exception (group=0x41cb82a0)
04-02 11:04:41.709: E/AndroidRuntime(24544): FATAL EXCEPTION: main
04-02 11:04:41.709: E/AndroidRuntime(24544): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.abs.mydiary/com.abs.mydiary.Diary}: android.database.sqlite.SQLiteException: near "FOREIGN": syntax error (code 1): , while compiling: CREATE TABLE images( date FOREIGN KEY(date) REFERENCES Diary(date) , image BLOB , _id INTEGER  PRIMARY KEY );

check my answer.. you cant use date keyword as column name... — Dehan Wjiesekara, Apr 02 '14 at 06:56

Dehan Wjiesekara · Accepted Answer · 2014-04-02T06:50:43.557

2

try this , I have modified your code, you must re assign Key_date variable value as "date1" or "my_date"..etc because "date" is a key word, you cant use keywords of sql as column names.

db.execSQL(" CREATE TABLE " + IMAGES_TABLE + "
( "+ KEY_DATE + " TEXT NOT NULL  ,
  "+ KEY_IMAGE + " BLOB , 
  "+ KEY_ID+ " INTEGER  PRIMARY KEY "+"
)FOREIGN  KEY("+ KEY_DATE + ") REFERENCES Diary("+ KEY_DATE + ");");

edited Apr 02 '14 at 06:50

answered Apr 02 '14 at 05:54

Dehan Wjiesekara

3,152
3
32
46

Isn't `Date` is a column data type ? – user2060383 Apr 02 '14 at 05:58
@user2060383 hi... I have modified my answer... "date" should be the value of KEY_DATE variable.. I guess, – Dehan Wjiesekara Apr 02 '14 at 06:03

score 0 · Answer 2 · answered Apr 02 '14 at 05:47

Try in this way:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

    CREATE TABLE track(
      trackid     INTEGER, 
      trackname   TEXT, 
      trackartist INTEGER,
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
    );

score 0 · Answer 3 · answered Apr 02 '14 at 05:59

0

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

https://www.sqlite.org/foreignkeys.html

answered Apr 02 '14 at 05:59

boiledwater

10,372
4
37
38

How to create foreign key in a table?

3 Answers3