Databases
StackOverflow2013
Postsdbo
POUnable to create second table in sqlite
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnable to create second table in sqlite
    primarykey
    data
    text
    <p>After trying all the possible solutions from blogs, forums, web pages, I've come up with this question.</p> <p>Initially I created a database with a table "registrationTable", and I was able to do all the CRUD operations. Then I tried to add a second table "purposeTable", which is not getting created due to some reason.</p> <p>I have tried doing the following things:</p> <p>changed the database version</p> <p>changed the create statement for the second table</p> <p>included "PRAGMA foreign_keys = ON;" as the second table contains a foreign key</p> <p>swapped the datatype of "cdate" field from text to date and vice-versa</p> <p>but still the table is uncreated.</p> <p>The code of my DBAdapter class is as below:</p> <pre><code> //Table 1 private static final String TAG = "DBAdapter"; private static final String DATABASE_TABLE = "registrationTable"; private static final String DATABASE_NAME = "project1database"; private static final int DATABASE_VERSION = 1; private static final String DATABASE_CREATE = "create table registrationTable ( phone integer primary key not null, name text not null, " + "age integer not null, area text not null, sex text not null);"; //Table 2 private static final String PURPOSE_TABLE = "purposeTable"; private static final String PURPOSE_CREATE = "create table purposeTable ( phone integer not null, foreign key (phone) references registrationTable(phone), " + "cdate text not null, primary key (phone, date), text1 text not null, text2 text not null);"; private final Context context; private SQLiteDatabase db; private DatabaseHelper DBHelper; public DBAdapter(Context ctx){ context = ctx; } //public DBAdapter read()throws SQLException //public DBAdapter write()throws SQLException //public void close() //public long insertDetails(String name, int age, String area, int phone, String sex) public long insertPurpose(String date, String text1, String text2){ ContentValues initialValues1 = new ContentValues(); initialValues1.put(CDATE, date); initialValues1.put(TEXT1, text1); initialValues1.put(TEXT2, text2); return db.insert(PURPOSE_TABLE, null, initialValues1); } private static class DatabaseHelper extends SQLiteOpenHelper{ public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { try{ db.execSQL(DATABASE_CREATE); db.execSQL(PURPOSE_CREATE); db.execSQL("PRAGMA foreign_keys = ON;"); }catch(SQLException e){ e.printStackTrace(); } } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading from version " + oldVersion + " to " + newVersion + ". All data will be deleted."); db.execSQL("DROP TABLE IF EXISTS registrationTable"); db.execSQL("DROP TABLE IF EXISTS purposeTable"); onCreate(db); } } </code></pre> <p>And am calling the method insertPurpose() from another class with the below code:</p> <pre><code>dbAdapter2.write(); dbAdapter2.insertPurpose(cDate, text1, text2); dbAdapter2.close(); </code></pre> <p>And the logcat log is as below:</p> <pre><code>11-17 01:29:17.023: I/SqliteDatabaseCpp(15095): sqlite returned: error code = 1, msg = no such table: purposeTable, db=/data/data/com.android.project1/databases/project1database 11-17 01:29:17.143: E/SQLiteDatabase(15095): Error inserting text1=a text2=b cdate=17-11-2011 11-17 01:29:17.143: E/SQLiteDatabase(15095): android.database.sqlite.SQLiteException: no such table: purposeTable: , while compiling: INSERT INTO purposeTable(text1,text2,cdate) VALUES (?,?,?) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteCompiledSql.&lt;init&gt;(SQLiteCompiledSql.java:64) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:260) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:112) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1745) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1618) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at com.android.project1.DBAdapter.insertPurpose(DBAdapter.java:113) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at com.android.project1.Purpose3Activity$1.onClick(Purpose3Activity.java:51) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.view.View.performClick(View.java:3460) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.view.View$PerformClick.run(View.java:13955) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.os.Handler.handleCallback(Handler.java:605) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.os.Handler.dispatchMessage(Handler.java:92) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.os.Looper.loop(Looper.java:137) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at android.app.ActivityThread.main(ActivityThread.java:4340) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at java.lang.reflect.Method.invokeNative(Native Method) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at java.lang.reflect.Method.invoke(Method.java:511) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551) 11-17 01:29:17.143: E/SQLiteDatabase(15095): at dalvik.system.NativeStart.main(Native Method) </code></pre> <p>Thanks in advance, if somebody can tell me where am I going wrong.</p> <p>Second table is created, but data is not inserted, new log:</p> <pre><code>11-17 11:42:11.281: E/SQLiteDatabase(10172): Error inserting text1=e text2=d cdate=17-11-2011 11-17 11:42:11.281: E/SQLiteDatabase(10172): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1745) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1618) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at com.android.project1.DBAdapter.insertPurpose(DBAdapter.java:113) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at com.android.project1.Purpose3Activity$1.onClick(Purpose3Activity.java:51) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.view.View.performClick(View.java:3460) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.view.View$PerformClick.run(View.java:13955) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.os.Handler.handleCallback(Handler.java:605) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.os.Handler.dispatchMessage(Handler.java:92) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.os.Looper.loop(Looper.java:137) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at android.app.ActivityThread.main(ActivityThread.java:4340) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at java.lang.reflect.Method.invokeNative(Native Method) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at java.lang.reflect.Method.invoke(Method.java:511) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551) 11-17 11:42:11.281: E/SQLiteDatabase(10172): at dalvik.system.NativeStart.main(Native Method) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USUser210282
    1. USUser210282
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload