1

I created the MyStudents.db SQLite database with followings commands in sqlite3 CLI:

CREATE TABLE workpiecelist (
idnum integer CONSTRAINT pk_workpiecelist PRIMARY KEY AUTOINCREMENT ,
grade integer NOT NULL ,
quart integer NOT NULL ,
workpiecenamehu text NOT NULL COLLATE nocase ,
workpiecenamesr text NOT NULL COLLATE nocase
);
CREATE TABLE student (
id integer CONSTRAINT pk_student PRIMARY KEY AUTOINCREMENT ,
idnum integer UNIQUE NOT NULL ,
studentsname text NOT NULL COLLATE nocase ,
lang text NOT NULL COLLATE nocase ,
grade integer NOT NULL ,
class text NOT NULL COLLATE nocase ,
headteacher text NOT NULL COLLATE nocase
);
CREATE TABLE uniqueworkpc (id integer CONSTRAINT pk_uniqueworkpc PRIMARY KEY AUTOINCREMENT,
student_who_owns_workpc text NOT NULL REFERENCES student (idnum)
ON DELETE CASCADE ON UPDATE CASCADE ,
numofworkpc_fromthelist integer NOT NULL REFERENCES workpiecelist (idnum)
ON DELETE CASCADE ON UPDATE CASCADE , mark integer , reason text);

Then I inserted two records into workpiecelist table with:

INSERT INTO workpiecelist VALUES(1,5,1,'workpeaceNameHu1','workpeaceNameSr1');
INSERT INTO workpiecelist VALUES(2,5,1,'workpeaceNameHu2','workpeaceNameSr2');

and ten records into student table:

INSERT INTO student VALUES(1,29660,'FirstName1 LastName1','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(2,12037,'FirstName2 LastName2','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(3,32284,'FirstName3 LastName3','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(4,20125,'FirstName4 LastName4','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(5,20624,'FirstName5 LastName5','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(6,14302,'FirstName6 LastName6','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(7,28590,'FirstName7 LastName7','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(8,13040,'FirstName8 LastName8','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(9,12514,'FirstName9 LastName9','Hu',5,'b','HeadTeachers Name');
INSERT INTO student VALUES(10,23316,'FirstName10 LastName10','Hu',5,'b','HeadTeachers Name');

Now I want to populate some columns in the uniqueworkpc table with:

sqlite> INSERT INTO uniqueworkpc(student_who_owns_workpc, 
   ...> numofworkpc_fromthelist) SELECT studentsname, 
   ...> workpiecelist.idnum FROM student CROSS JOIN workpiecelist WHERE student.grade is 5 
   ...> AND workpiecelist.grade is 5 AND student.lang is "Hu";

and get error message: Error: FOREIGN KEY constraint failed

but, when I execute the python script with same command, I get the inserted records in the uniqueworkpc table:

#!/usr/bin/python3.4
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

conn = lite.connect('MyStudents.db')

with conn:

    cur = conn.cursor()

    cur.execute('INSERT INTO uniqueworkpc(student_who_owns_workpc, \
    numofworkpc_fromthelist) SELECT studentsname, \
    workpiecelist.idnum FROM student CROSS JOIN workpiecelist WHERE student.grade is 5 \
    AND workpiecelist.grade is 5 AND student.lang is "Hu" ')

Why get I error message in the sqlite3 CLI but not with the python script even though the commands are the same?

Best, from Pal

Pal Csanyi
  • 101
  • 1
  • 9
  • I can't believe it let you create that foreign key in the first place. Foreign keys have to have the same datatype, and `student.idnum` is not `TEXT`. You also probably can't use a `TEXT` field as a foreign key in the first place, since the foreign key needs to be indexed. – Barmar Dec 08 '17 at 20:56
  • Why don't you use the student ID in the `uniqueworks` table instead of the student name? It's usually best to have foreign keys point to primary keys. – Barmar Dec 08 '17 at 20:58
  • Thank you for the help! I did as you adviced. I used not text field as a foreign key but primary key, which is a number. It works now. – Pal Csanyi Dec 09 '17 at 10:37

0 Answers0