SQLite Forum

Please explain the error on insert
Login
I am actually running SQLite under in a Qt application. I got a failure on an insert statement so I turned to Sqlite3 CLI to figure it out. But I'm stuck.
The design is a linked list of students for each teacher. The link is from the second to the first with a student-prev pointed to a student-id. The first student-prev in each student list is 0.
Here is the CLI console [editted]:

```
sqlite> .version
SQLite 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6
zlib version 1.2.11
gcc-5.2.0
sqlite> .dump [editted to show the studenttable only]
CREATE TABLE studenttable
 (student_id INTEGER NOT NULL
 ,student_displayname TEXT NOT NULL
 ,teacher_id INTEGER NOT NULL REFERENCES teachertable(teacher_id) ON DELETE CASCADE
 ,student_prev INTEGER NOT NULL REFERENCES studenttable(student_id)
 ,student_select INTEGER NOT NULL
 ,UNIQUE(student_displayname,teacher_id)
);
sqlite> select * from sqlite_master where type = 'trigger';
type    name    tbl_name        rootpage        sql
trigger insert_new_student      studenttable    0
  CREATE TRIGGER insert_new_student
    AFTER INSERT ON studenttable
      BEGIN
        UPDATE OR IGNORE studenttable SET student_prev=NEW.student_id
          WHERE teacher_id=NEW.teacher_id
          AND student_prev=NEW.student_prev
          AND student_id!=NEW.student_id;
      END
sqlite> pragma foreign_keys;
foreign_keys
1
sqlite> select * from teachertable;
teacher_id      teacher_displayname     teacher_prev
1		       Doug		    0
2		       Matt		    1
3		       Zhenia		    2
sqlite> select * from studenttable;
student_id      student_displayname     teacher_id      student_prev    student_select
1		Unspecified		     1		       0	       0
2		Unspecified		     2		       0	       0
3		Unspecified		     3		       0	       0
sqlite> INSERT INTO studenttable (student_id,student_displayname,teacher_id,student_prev,student_select)
   ...>   VALUES(4,'Doug',3,3,0);
Error: foreign key mismatch - "studenttable" referencing "studenttable"
```