Please explain the error on insert
(1) By doug (doug9forester) on 2020-04-08 04:27:28 [link] [source]
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"
(2) By Keith Medcalf (kmedcalf) on 2020-04-08 05:01:33 in reply to 1 [link] [source]
There is no student 0. Nor is there a teacher 0.
(3) By doug (doug9forester) on 2020-04-08 06:07:33 in reply to 2 [link] [source]
The items that refer to teacher 0 and student 0 are already in their respective tables. I put the there with pragma foreign_keys=0.
The insert that is failing has an unused student_id (4) and refers to an existing student_id (3):
INSERT INTO studenttable (student_id,student_displayname,teacher_id,student_prev,student_select) VALUES(4,'Doug',3,3,0);
What you are suggesting is that some mechanism is looking at existing rows to see if they are violating referential integrity (which they obviously do). And the checking occurs when I try to insert a new row.
If so, what is that mechanism?
Is there a table design which gets around the mechanism and still provides referential integrity for new inserts?
(4) By Gunter Hick (gunter_hick) on 2020-04-08 09:38:43 in reply to 1 [link] [source]
Why does your design require an ordered list of teachers and their students? This strikes me as a remnant of storing data in linked lists in memory. Your trigger program is also failing to select any row, there is no row matching teacher_id = 3 and student_prev = 3. Perhaps you meant teacher_id=NEW.teacher_id and student_id = NEW.student_prev? This will add new students to the head of the list. Note that your design also limits students to having exactly one teacher.
(5) By Gunter Hick (gunter_hick) on 2020-04-08 09:50:18 in reply to 1 [link] [source]
Why does your design declare student_prev to be NOT NULL if you clearly want to enter "nil pointers" in your data? It Forces you to use 0 as a marker for the end of the list and to violate referential integrity. Also, do you really want to kick out all students attached to a teacher when you remove a teacher (ON DELETE CASCADE)? This seems a Little drastic. Maybe you would rather reassign all attached students (ON DELETE RESTRICT) first? Or maybe make them unassigned (ON DELETE SET NULL), which would probably mess up your linked list without some further code.
(6) By Keith Medcalf (kmedcalf) on 2020-04-08 10:35:54 in reply to 3 [link] [source]
In a relational database system (of which SQLite3 is one implementation) the parent:child relationship is a 1:N relationship. Parents must be unique in their domain. You can have more than one child per parent. A child in a relationship must have exactly exactly one parent or must be a bastard (the parent is unknown within the domain, hence NULL).
From this you can gleen the following rules:
The set of columns which comprise the "parent key" must be constrained unique -- this is done with the UNIQUE constraint or a UNIQUE index. Furthermore, these values cannot be null (hence the parent key is a proper candidate key).
The set of children which belong to a parent must be able to be located. This means that you usually want to have an index that begins with these "child key" columns unless you want to be complaining why referential enforcement is slow.
Children with no parents have the child key elements set to NULL (hence ON DELETE SET NULL). If a child key cannot be null then the child must have a parent.
Your particular problem is that you have violated the first requirement (a unique index on the parent keys) and this is what the error message is telling you.
Your database does not have integrity because it violates the last rule.
(7) By doug (doug9forester) on 2020-04-08 22:43:01 in reply to 4 [source]
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).