CODEWITHSUNDEEP
androidsqlite
Hoo
Hoo

Reputation: 1840

Error in creating table in SQLite

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)

Upvotes: 1

Views: 45

Answers (2)

frogatto
frogatto

Reputation: 29285

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

enter image description here

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.

Upvotes: 1

e4c5
e4c5

Reputation: 53734

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))

Upvotes: 2

Related Questions