Android Studio中SQLite第二张表添加数据无响应问题求助

Hey Bruno, let's break down what's causing your SQLite insert failure—there are a couple of critical issues hiding in your code that are stopping the second table from accepting data.

The Core Problem: Invalid Table Definition #

Looking at your createTable2 SQL in DatabaseHelper.java, you've made a syntax mistake that breaks the table structure entirely:

String createTable2 = "CREATE TABLE " + TABLE_NAME2 + " ( " + COL3 + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COL4 + " TEXT," + COL5 + " INTEGER," + COL1 + " INTEGER PRIMARY KEY," + COL6 + " TEXT, " + " FOREIGN KEY (" + COL1 + ") REFERENCES " + TABLE_NAME + "(" + COL1 + "));";

You've marked two columns as PRIMARY KEY (COL3 and COL1), but SQLite only allows one primary key per table (unless you explicitly define a composite primary key, which isn't your goal here). On top of that, COL1 is meant to be a foreign key linking to your first table—making it a primary key at the same time is invalid and will prevent the table from working as expected.

Secondary Issue: Data Type Mismatch #

You defined COL5 as INTEGER in the table, but in addData2(), you're passing a String (newEntry2) to this column. SQLite is forgiving with types, but this mismatch can still cause insert failures or unexpected behavior.

Fixes to Get It Working #

Let's correct these issues step by step:

1. Fix the Table Creation SQL
   Remove the duplicate PRIMARY KEY constraint from COL1 in createTable2:

   @Override 
   public void onCreate(SQLiteDatabase db) { 
       String createTable = "CREATE TABLE " + TABLE_NAME + " ( " + COL1 + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COL2 + " TEXT);"; 
       // Corrected table 2 definition
       String createTable2 = "CREATE TABLE " + TABLE_NAME2 + " ( " 
           + COL3 + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
           + COL4 + " TEXT," 
           + COL5 + " INTEGER," // Keep as INTEGER if you need numeric values
           + COL1 + " INTEGER, " // Removed PRIMARY KEY here
           + COL6 + " TEXT, " 
           + " FOREIGN KEY (" + COL1 + ") REFERENCES " + TABLE_NAME + "(" + COL1 + "));"; 
       db.execSQL(createTable); 
       db.execSQL(createTable2); 
   }
   

2. Align Data Types in addData2()
   If COL5 is supposed to store integers, update the method to accept an Integer instead of a String:

   public boolean addData2(String newEntry1, Integer newEntry2, String horarios, Integer id_ppl) { 
       SQLiteDatabase db = this.getWritableDatabase(); 
       ContentValues contentValues = new ContentValues(); 
       contentValues.put(COL4, newEntry1); 
       contentValues.put(COL5, newEntry2); // Now matches INTEGER column type
       contentValues.put(COL1, id_ppl); 
       contentValues.put(COL6, horarios); 
       
       long result = db.insert(TABLE_NAME2, null, contentValues); 
       // Add logging to debug future issues
       if (result == -1) {
           Log.e("DB_ERROR", "Failed to insert into " + TABLE_NAME2);
           return false; 
       } else { 
           return true; 
       } 
   }
   

3. Update the Call in MainActivity
   Convert the input from editText3 to an integer before passing it to AddData2() (and add a check to avoid NumberFormatException):

   buttonNewPill.setOnClickListener(new View.OnClickListener() { 
       @Override 
       public void onClick(View v) { 
           String newEntry1 = editText2.getText().toString(); 
           String newEntry2Str = editText3.getText().toString();
           String textSp1 = spinner2.getSelectedItem().toString(); 
           String textSp2 = spinner3.getSelectedItem().toString(); 
           String horario = textSp1 + ":" + textSp2; 
           Integer id_ppl = 1; 
   
           // Validate inputs properly
           if (!newEntry1.isEmpty() && !newEntry2Str.isEmpty()){
               try {
                   Integer newEntry2 = Integer.parseInt(newEntry2Str);
                   AddData2(newEntry1, newEntry2, horario, id_ppl); 
                   editText2.setText(""); 
                   editText3.setText(""); 
               } catch (NumberFormatException e) {
                   toastMessage("Please enter a valid number for the second field!");
               }
           } else { 
               toastMessage("Isn't your field empty?"); 
           } 
       } 
   });
   

Pro Tip for Future Debugging #

Always check Android Studio's Logcat when SQLite operations fail. When db.insert() returns -1, SQLite logs a detailed error message (search for "SQLite" or "Database" tags) that will tell you exactly what went wrong—this is way more helpful than a generic toast!

内容的提问来源于stack exchange，提问作者BrunoJ