Error SQLiteException: foreign key mismatch

2

I was doing a database, but after hours of review I could not reach a resolution to my problem, so I come to you in search of an answer.

I wanted to make a query of a database, whose tables are the following:

    db=this.openOrCreateDatabase("calificaciones.db", MODE_PRIVATE, null);
    db.execSQL("PRAGMA foreign_keys=ON");
    db.execSQL("create table if not exists usuarios (idusuario integer primary key autoincrement, nusuario text, contrasena text, correo text);");
    db.execSQL("create table if not exists alumnos (idalumno integer primary key, apellidos text, nalumno text, especialidad text, grado text, grupo text);");
    db.execSQL("create table if not exists materias (idmateria integer primary key, nmateria text,"+" docente text, horas integer);");
    db.execSQL("create table if not exists calificacion (idcalif integer primary key autoincrement, idalumno integer, idmateria integer, idusuario integer, calificacion integer, parcial integer, foreign key(idalumno) references alumnos(idalumno), foreign key(idmateria) references materias(idmateria), foreign key(idusuario) references alumnos(idusuario));");

But, when you want to enter a record in the "Qualification" table, it gives me the following error message:

android.database.sqlite.SQLiteException: foreign key mismatch - "calificacion" referencing "alumnos" (code 1): , while compiling: insert into calificacion (idalumno, idmateria, idusuario, calificacion, parcial) values (15330050790409,42069,1,10,3);

despite the fact that in the registers of the other tables the primary key matches the data to be inserted in the insert.

I tried to reproduce the error in SQL Fiddle, since in it I could verify tables, and in spite of using exactly the same Querys, in SQL Fiddle it worked, even though in my application, even though I tried to do it did not work. / p>

I'm quite new to Android Studio and it's part of a final project, so if someone knew a solution I'd appreciate it a lot.

EDIT: The function in which the error occurs is the following, in case it is an external error to the database:

    public void onClick(View v) {
    IntIdmateria=Integer.parseInt(ETPC3idmateria.getText().toString().trim());
    IntIdusuario=Integer.parseInt(ETPC3idusuario.getText().toString().trim());
    IntCalificacion=Integer.parseInt(ETPC3calificacion.getText().toString().trim());
    IntParcial=Integer.parseInt(ETPC3parcial.getText().toString().trim());
    LongIdalumno=Long.parseLong(ETPC3idalumno.getText().toString().trim());
    switch (v.getId()) {
        case R.id.BTNPC3insertarcalificacion:{
            cursor=db.rawQuery("select * from alumnos where idalumno="+LongIdalumno+";", null);
            cursor2=db.rawQuery("select * from materias where idmateria="+IntIdmateria+";", null);
            cursor3=db.rawQuery("select * from usuarios where idusuario="+IntIdusuario+";", null);
            if(LongIdalumno==0 || IntIdmateria==0 || IntIdusuario==0 || IntCalificacion==0 || IntParcial==0){
                Toast.makeText(this, "No ha llenado todos los datos",Toast.LENGTH_LONG).show();
            } else if(cursor.getCount()==0){
                Toast.makeText(this, "No se ha encontrado ese Número de Control",Toast.LENGTH_LONG).show();
            } else if(cursor2.getCount()==0){
                Toast.makeText(this, "No se ha encontrado esa identificación de Materia",Toast.LENGTH_LONG).show();
            } else if(cursor3.getCount()==0){
                Toast.makeText(this, "No se ha encontrado esa identificación de Usuario",Toast.LENGTH_LONG).show();
            } else if(LongIdalumno!=0 && IntIdmateria!=0 && IntIdusuario!=0 && IntCalificacion!=0 && IntParcial!=0){
                db.execSQL("insert into calificacion (idalumno, idmateria, idusuario, calificacion, parcial) values ("+LongIdalumno+","+IntIdmateria+","+IntIdusuario+","+IntCalificacion+","+IntParcial+");");
                Toast.makeText(this, "Se ha insertado la calificación correctamente", Toast.LENGTH_LONG).show();
            }
        }
    }
}
    
asked by Erinyes Fisher 12.06.2017 в 10:25
source

2 answers

0

At first it seems that everything is correct, since the structures of the tables seem correct and you are also activating the flag that allows you to have active the foreign keys in according to the documentation .

On the other hand the only reason I see why you are failing is because the idalumno that you put in the insert 15330050790409 is repeated and is not a single value, and this could be possible because according to the structure of your table is not being generated automatically with the autoincrement and it could be that you had entered some duplicate value.

    
answered by 12.06.2017 в 10:51
0

Check how your column idcalif is defined, it is a primary key autoincrementable:

db.execSQL("create table if not exists calificacion (idcalif integer primary key autoincrement, idalumno integer, idmateria integer, idusuario integer, calificacion integer, parcial integer, foreign key(idalumno) references alumnos(idalumno), foreign key(idmateria) references materias(idmateria), foreign key(idusuario) references alumnos(idusuario));");

therefore you should not send it the value of idcalif or try to modify the value of the column, since it is calculated automatically , your query should be like this:

 db.execSQL("insert into calificacion (idmateria, idusuario, calificacion, parcial) values ("+IntIdmateria+","+IntIdusuario+","+IntCalificacion+","+IntParcial+");");
    
answered by 12.06.2017 в 18:28