SQLite Forum

Please explain the error on insert
The design requires ordered lists of teachers and their students because teachers and student can be inserted in the list anywhere and the application needs to remember where they were inserted.

The implementation was working fine (students and teachers in the right order) until I turned on referential integrity checking.

I am generating C++ code for accessing the database, a class for each table. I can generate a function for an arbitrary table query with as few as 2 lines of input. I support field types of INTEGER and TEXT only. For INTEGER type, I use a 'int' data member in the class. So implementing teacher_id as a C++ int was a natural. However, 'int's don't do null values very well.

Rethinking my design, the value of 0 can be treated as a null. Since I'm generating the code, it's pretty simple to do this when getting the value for binding to a parameter:

  queryinsert.addBindValue(teacher_prev==0 ? QVariant(QString()) : QVariant(teacher_prev));
The purpose of the teacher/student list is to allow a (dance) teacher to record that a particular student (or the teacher) wants to remember a particular song used for a dance lesson for that student. The application provides quick access to different lists of songs quickly without having to look them up again and again.
As for 1-to-1 teachers and students, if two teachers want to add the same student to their lists, then they just add them. There is no master list.

Also, teachers come and go with regularity so removing a teacher means removing data that only that teacher was interested in. (I may want to rethink cascade...) If a teacher deletes a student, the songs remembered for that student will revert to the catch-all student (Unspecified).