I want to insert a register into a table, with the following structure:
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, at the moment of inserting a register into the table "Calificacion", 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);
It happens even if the registers of the other tables match with the data that I tried to insert into "Calificaciones". I tried to reproduce the same error on SQL Fiddle, using exactly the same Query's, but in that platform somehow it didn't cause any error.
What is wrong with my syntax?
Jon Adams :
From the docs:\n\n\n If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys.\n \n ...\n \n Foreign key DML errors are may be reported if:\n \n \n The parent table does not exist, or \n The parent key columns named in the foreign key constraint do not exist, or \n The parent key columns\n named in the foreign key constraint are not the primary key of the\n parent table and are not subject to a unique constraint using\n collating sequence specified in the CREATE TABLE, or \n The child table\n references the primary key of the parent without specifying the\n primary key columns and the number of primary key columns in the\n parent do not match the number of child key columns.\n \n\n\nHowever, glancing at your code, it looks like most of them are followed. Are you sure there aren't errors during creation, for example, the parent table not being able to be created in the first place? (Because you are getting the error later during insert, I'm wondering if you're missing errors earlier in the process.)\n\nAnd, are you sure these are the tables you are creating? Because you have a if not exists clause in there; maybe older versions of the tables with different schemas are there and your current above (and I think) correct code is not what is actually running right now? Use a tool to view or extra the raw SQL Lite database from your device/emulator to see if the schema there matches the commands you are currently supplying it above.",
2017-06-12T14:22:56