Error al insertar android.database.sqlite.sqliteconstraintexception error código 19 restricción fallida
Sé que hay mucho de hilos sobre este problema, pero ninguno de mi propio problema. Tengo un spinner conectado en la base de datos que muestra la clave externa de la tabla específica. Y aparece la inserción de error.
02-18 11:44:34.500: E/SQLiteDatabase(20811): Error inserting ConsumerName=android.database.sqlite.SQLiteCursor@4144fa58 kWh=801.0 _id=65324 Date=2013 -2-18 Previous=98 Current=899
- No se puede bajar la base de datos de la versión `n` a` n-1` en Samsung
- ¿Cuál es el modo de subprocesamiento predeterminado de SQLite en Android?
- Android SQLiteException: Error al cambiar la configuración regional de db a 'en_US'
- ¿Cuándo debo llamar a close () en SQLiteOpenHelper utilizado por ContentProvider
- Conexión de SQLite se filtró aunque todo se cerró
02-18 11:44:34.500: E/SQLiteDatabase(20811): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
public class ElectricMeterReader extends Activity { private long rowID; private EditText meterNoEt; private EditText currentEt; private EditText previousEt; private EditText kWhEt; private TextView dateTv; private Spinner spinner; private int mSpinnerSpeciesId; private ElectricMeterReader mContext; @Override protected void onCreate(Bundle savedInstanceState) { // TODO Auto-generated method stub super.onCreate(savedInstanceState); setContentView(R.layout.electric_meter_reader); Bundle extras = getIntent().getExtras(); // rowID = extras.getLong(ConsumerList.ROW_ID); dateTv = (TextView) findViewById(R.id.dateEmr); meterNoEt = (EditText) findViewById(R.id.meterNumberEmr); currentEt = (EditText) findViewById(R.id.currentReadingEmr); previousEt = (EditText) findViewById(R.id.passReadingEmr); kWhEt = (EditText) findViewById(R.id.kwhEmr); spinner = (Spinner) findViewById(R.id.spinner); // Loading spinner data from database SQLiteDatabase db = new DatabaseOpenHelper(this).getWritableDatabase(); // Spinner Drop down elements // List<String> lables = db.getAllLabels(); Cursor c = db.rawQuery( "SELECT AccountID AS _id, ConsumerName FROM Consumers", null); // Creating adapter for spinner startManagingCursor(c); String[] from = new String[] { "_id" }; int[] to = new int[] { android.R.id.text1 }; SimpleCursorAdapter dataAdapter = new SimpleCursorAdapter(this, android.R.layout.simple_spinner_item, c, from, to); // Drop down layout style - list view with radio button dataAdapter .setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item); // attaching data adapter to spinner spinner.setAdapter(dataAdapter); // set spinner listener to display the selected item id mContext = this; spinner.setOnItemSelectedListener(new OnItemSelectedListener() { public void onItemSelected(AdapterView<?> parent, View view, int position, long id) { // On selecting a spinner item //String label = parent.getItemAtPosition(position).toString(); Cursor c = (Cursor) parent.getItemAtPosition(position); mSpinnerSpeciesId = c.getInt(c .getColumnIndexOrThrow("ConsumerName")); // Showing selected spinner item /* Toast.makeText(parent.getContext(), "You selected: " + label, Toast.LENGTH_LONG).show();*/ } public void onNothingSelected(AdapterView<?> parent) { // TODO Auto-generated method stub } }); final Calendar ca = Calendar.getInstance(); int yy = ca.get(Calendar.YEAR); int mm = ca.get(Calendar.MONTH); int dd = ca.get(Calendar.DAY_OF_MONTH); dateTv.setText(new StringBuilder() // Month is 0 based, just add 1 .append(yy).append(" ").append("-").append(mm + 1).append("-") .append(dd)); if (extras != null) { rowID = extras.getLong("_id"); // meterNoEt.setText(extras.getString("MeterNumber")); // set current date into textview // Spinner click listener currentEt.setText(extras.getString("Current")); previousEt.setText(extras.getString("Previous")); kWhEt.setText(extras.getString("kWh")); // dateTv.setText(extras.getString("Date")); } Button calButton = (Button) findViewById(R.id.calculateDef); calButton.setOnClickListener(new OnClickListener() { public void onClick(View v) { // TODO Auto-generated method stub float result = Float.parseFloat(currentEt.getText().toString()) - Float.parseFloat(previousEt.getText().toString()); kWhEt.setText(Float.toString(result)); } }); Button saveButton = (Button) findViewById(R.id.addConsumption); saveButton.setOnClickListener(new OnClickListener() { public void onClick(View v) { if (kWhEt.getText().length() != 0) { AsyncTask<Object, Object, Object> saveContactTask = new AsyncTask<Object, Object, Object>() { @Override protected Object doInBackground(Object... params) { saveContact(); return null; } @Override protected void onPostExecute(Object result) { /* * Intent addContact = new Intent( * ElectricMeterReader.this, * ConsumerReadList.class); * startActivity(addContact); */ finish(); } }; saveContactTask.execute((Object[]) null); } else { AlertDialog.Builder alert = new AlertDialog.Builder( ElectricMeterReader.this); alert.setTitle(R.string.errorTitle); alert.setMessage(R.string.errorMessage); alert.setPositiveButton(R.string.errorButton, null); alert.show(); } } }); } public boolean onCreateOptionsMenu(Menu menu) { super.onCreateOptionsMenu(menu); MenuInflater inflater = getMenuInflater(); inflater.inflate(R.menu.consumer_menu, menu); return true; } @Override public boolean onOptionsItemSelected(MenuItem item) { switch (item.getItemId()) { case R.id.addConsumerItem: Intent addContact = new Intent(ElectricMeterReader.this, AddEditConsumer.class); startActivity(addContact); return true; case R.id.consumerInfo: Intent electricMeterReader = new Intent(ElectricMeterReader.this, ViewConsumer.class); startActivity(electricMeterReader); return true; default: return super.onOptionsItemSelected(item); } } private void saveContact() { DatabaseConnector dbConnector = new DatabaseConnector(this); if (getIntent().getExtras() == null) { dbConnector.insertConsumption(meterNoEt.getText().toString(), currentEt.getText().toString(), previousEt.getText() .toString(), kWhEt.getText().toString(), dateTv .getText().toString(), spinner.getItemAtPosition(mSpinnerSpeciesId).toString() ); } else { dbConnector.updateConsumption(rowID, currentEt.getText().toString(), previousEt.getText() .toString(), kWhEt.getText().toString(), dateTv .getText().toString(), spinner.getItemAtPosition(mSpinnerSpeciesId).toString()); } }
}
Insertar método codificado en mi databaseconnector
public void insertConsumption(String meter_number, String current, String previous, String kWh, String date, String consumer_name) { ContentValues newCons = new ContentValues(); newCons.put("_id", meter_number); newCons.put("Current", current); newCons.put("Previous", previous); newCons.put("kWh", kWh); newCons.put("Date", date); newCons.put("ConsumerName", consumer_name); open(); database.insert("Consumptions", null, newCons); close(); }
Mi base de datos Openhelper
public class DatabaseOpenHelper extends SQLiteOpenHelper { static final String dbName = "ElectricMeterDB"; static final String tableLabels = "labels"; static final String consumptionsTable = "Consumptions"; static final String colMeterNumber = "_id"; static final String colCurrent = "Current"; static final String colPrevious = "Previous"; static final String colkWh = "kWh"; static final String colDate = "Date"; static final String colConsumer = "ConsumerName"; static final String consumersTable = "Consumers"; static final String colAccountID = "AccountID"; static final String colName = "ConsumerName"; static final String colAddress = "Address"; public static final String viewReads = "ViewReading"; public DatabaseOpenHelper(Context context) { super(context, dbName, null, 2); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + consumersTable + " (" + colAccountID + " INTEGER PRIMARY KEY NOT NULL," + colName + " TEXT NOT NULL, " + colAddress + " TEXT NOT NULL);"); db.execSQL("CREATE TABLE " + consumptionsTable + " (" + colMeterNumber + " INTEGER PRIMARY KEY NOT NULL," + colCurrent + " INTEGER, " + colPrevious + " INTEGER, " + colkWh + " INTEGER, " + colDate + " TEXT, " + colConsumer + " INTEGER NOT NULL, FOREIGN KEY (" + colConsumer + ") REFERENCES " + consumersTable + " (" + colAccountID + "));"); db.execSQL("CREATE TRIGGER fk_cmpcons_consid" + " BEFORE INSERT " + " ON " + consumptionsTable + " FOR EACH ROW BEGIN" + " SELECT CASE WHEN ((SELECT " + colAccountID + " FROM " + consumersTable + " WHERE " + colAccountID + " =new." + colConsumer + ") IS NULL)" + "THEN RAISE (ABORT, 'Foreign Key Violation') END;" + "END;"); db.execSQL("CREATE VIEW " + viewReads + " AS SELECT " + consumptionsTable + " . " + colMeterNumber + " AS _id, " + consumptionsTable + " . " + colCurrent + " ," + consumptionsTable + " . " + colPrevious + " ," + consumptionsTable + " . " + colkWh + ", " + consumptionsTable + " . " + colDate + "," + consumersTable + "." + colConsumer + " FROM " + consumptionsTable + " JOIN " + consumersTable + " ON " + consumptionsTable + " . " + colConsumer + " = " + consumersTable + "." + colAccountID); // Inserts pre-defined consumers } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS" + consumptionsTable); db.execSQL("DROP TABLE IF EXISTS" + consumersTable); db.execSQL("DROP TRIGGER IF EXISTS consumers_id_trigger"); db.execSQL("DROP TRIGGER IF EXISTS consumers_id_trigger22"); db.execSQL("DROP TRIGGER IF EXISTS fk_cmpcons_consid"); db.execSQL("DROP VIEW IF EXISTS" + viewReads); onCreate(db); } public void insertLabel(String label) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(colName, label); // Inserting Row db.insert(tableLabels, null, values); db.close(); // Closing database connection } /** * Getting all labels returns list of labels * */ public List<String> getAllLabels() { List<String> labels = new ArrayList<String>(); // Select All Query String selectQuery = "SELECT * FROM " + consumersTable; SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { labels.add(cursor.getString(1)); } while (cursor.moveToNext()); } // closing connection cursor.close(); db.close(); // returning lables return labels; } }
Estoy atascado con este problema. Por favor, ayúdame. Gracias por la respuesta futura.
Logd
02-18 13:49:59.870: D/saveContact(30881): _id: 36698521 ConsumerName: android.database.sqlite.SQLiteCursor@4144f700
Mi arrayadapter;
public void arrayAdapterList() { final String[] from = new String[] { DatabaseOpenHelper.colAccountID }; // int[] to = new int[] { android.R.id.text1 }; ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item, from); adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item); spinner = (Spinner) findViewById(R.id.spinner); // attaching data adapter to spinner spinner.setAdapter(adapter); spinner.setOnItemSelectedListener(new OnItemSelectedListener() { public void onItemSelected(AdapterView<?> parent, View view, int position, long id) { // On selecting a spinner item // String col= c.getString(c.getColumnIndex("_id")); int item = spinner.getSelectedItemPosition(); Toast.makeText(getBaseContext(), "You Selected: " + from[item], Toast.LENGTH_SHORT).show(); } public void onNothingSelected(AdapterView<?> parent) { // TODO Auto-generated method stub } }); }
- SQLiteDatabase.openDatabase vs SQLiteOpenHelper.getReadableDatabase
- cómo crear base de datos una sola vez, a continuación, leer y escribir varias veces desde él, SQLite, OpenHelper, Android
- Cómo funciona SQLiteOpenHelper Context
- SQLiteOpenHelper múltiples bases de datos en memoria
- Android no actualiza la base de datos SQLite
- SQLiteOpenHelper - ¿Cómo se crea la base de datos?
- SQLiteOpenHelper sincronización
- Robolectric: ejecutar múltiples pruebas falla
Parece que algunos valores que está insertando pueden ser NULL para campos NOT NULL (ConsumerName en su caso) o PK (_id en su caso) ya existe. El código de error 19 significa que una restricción de tabla (NOT NULL, UNIQUE, etc.) fue violada durante la operación (INSERT, etc.). Aquí están la lista de códigos de error de SQLITE
Android.database.sqlite.SQLiteCursor@4144fa58 es un valor no deseado asignado a ConsumerName. Así que por favor haga alguna solución para obtener su FK adecuado para ConsumerName desde el cursor.
Hay algo mal con usted SimpleCursorAdapter. Así que por favor mirar en él. Si usted está recibiendo los valores correctos en spinner, entonces por favor, obtener el valor seleccionado de spineer como este spinner.getSelectedItem().toString();
En lugar de spinner.getItemAtPosition(mSpinnerSpeciesId).toString()
.
Espero que esto te ayude.
Por lo general, la restricción falló que se hizo algo como pasar un valor nulo a una columna que se declara como no nulo cuando se crea la tabla.
Por lo tanto, no quite nulo y lugar nulo al crear la tabla.
Conseguí el mismo error y lo solucioné fijando la identificación NULL en código. Puesto que ID es PK SqlLite autoincrementa su valor, no necesita ser configurado.
public void insertConsumption(String meter_number, String current, String previous, String kWh, String date, String consumer_name) { ContentValues newCons = new ContentValues(); //newCons.put("_id", meter_number); newCons.put("Current", current); newCons.put("Previous", previous); newCons.put("kWh", kWh); newCons.put("Date", date); newCons.put("ConsumerName", consumer_name); open(); database.insert("Consumptions", null, newCons); close(); }
Declaró la columna "colAccountID" como PRIMARY KEY, por lo que el SQLite db arrojar error mientras intenta insertar el mismo id una y otra vez.
- Reconocimiento de voz sin cuadros de diálogo de Google
- Cómo rellenar el espacio entre dos vistas dentro de un RelativeLayout