Error in creating table in SQLite

Question

What's wrong with my SQL code? When I run, the app crashed. I've been searching it for quite a long time but still cannot figure out the problem.

     db.execSQL("create table "+TABLE_WORKDETAILS +"(ID INTEGER PRIMARY KEY , Project TEXT, WorkDescription TEXT, Per Text, TimeIn DATETIME,
 TimeOut DATETIME,TotalHours DATETIME, Twf_id INTEGER, FOREIGN KEY(Twf_id) REFERENCES "+TABLE_WORKFORCE+"(ID1),TableInfo_id INTEGER, FOREIGN KEY(TableInfo_id) REFERENCES "+TABLE_INFO+"(ID))");

Error LogCat

 Process: com.example.project.project, PID: 2055
    android.database.sqlite.SQLiteException: near "TableInfo_id": syntax error (code 1): , while compiling: create table WorkDetails(ID INTEGER PRIMARY KEY , Project TEXT, WorkDescription TEXT, Per Text, TimeIn DATETIME, TimeOut DATETIME,TotalHours DATETIME, Twf_id INTEGER, FOREIGN KEY(Twf_id) REFERENCES WorkForce(ID1),TableInfo_id INTEGER, FOREIGN KEY(TableInfo_id) REFERENCES Information(ID))
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)

e4c5 · Accepted Answer · 2015-10-11 07:05:36Z

2

In Sqlite table definitions all the column definitions must be placed before the key definitions where as in your query they are jumbled together. Try this:

CREATE table WorkDetails(ID INTEGER PRIMARY KEY , 
  Project TEXT, WorkDescription TEXT, Per Text,

  TimeIn DATETIME, TimeOut DATETIME,TotalHours DATETIME,TableInfo_id INTEGER, 
  Twf_id INTEGER, FOREIGN KEY(Twf_id) REFERENCES WorkForce(ID1),
  FOREIGN KEY(TableInfo_id) REFERENCES Information(ID))

answered Oct 11, 2015 at 7:05

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

frogatto · Accepted Answer · 2015-10-11 07:24:14Z

1

There's a cool diagram in SQLite website which visually illustrate how a create table statement must be.

So, as you can see, table-constraints must be after the column-defs. You could now hence put all your foreign key and primary key definitions in bottom your statement.

edited Oct 11, 2015 at 7:24

answered Oct 11, 2015 at 7:19

frogatto

29.4k13 gold badges90 silver badges137 bronze badges

Collectives™ on Stack Overflow

Error in creating table in SQLite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related