19 = foreign key constraint failed

[Fim]

But which foreign key?
Somtimes you have more than one foreign key.

/Fim W

[Fim]

#COMPILE EXE "W:\TEST6.EXE"
#COMPILER PBCC
#INCLUDE "C:\POWERBASIC\SQLITENING\INC\SQLITENING.INC"

FUNCTION PBMAIN () AS LONG
    SLSETPROCESSMODS "E0"
    SLOPEN "SAMPLE.DB3","C"
    SLEXE "DROP TABLE IF EXISTS CHILD"  'DROP CHILDREN FIRST REQUIRED
    SLEXE "DROP TABLE IF EXISTS PARENT1" 'DROP PARENTS
    SLEXE "DROP TABLE IF EXISTS PARENT2" 'DROP PARENTS
    SLEXE "CREATE TABLE PARENT1(P1 PRIMARY KEY)"
    SLEXE "CREATE TABLE PARENT2(P2 PRIMARY KEY)"
    SLEXE "CREATE TABLE CHILD (C1 PRIMARY KEY, C2, FOREIGN KEY(C1) REFERENCES PARENT1(P1), FOREIGN KEY(C2) REFERENCES PARENT2(P2))

    SLEXE "INSERT INTO PARENT1  VALUES(1)"

    SLEXE "INSERT INTO CHILD  VALUES(1, 2)"
    PRINT SLGETERROR  '19 = FOREIGN KEY CONSTRAINT FAILED

END FUNCTION
But which foreign key, C1 or C2?

/Fim W.

[Bern Ertl]

SQLite error handling will not provide more detail or explanation.  You will have to analyze the situation yourself to determine the specific cause for the error.

There is a potential option for helping to debug the issue if you can't see it by examining the SQL statement and the data in the database.  You could try defining one of the foreign keys as DEFERRED*, wrap your insert within a transaction and test for errors immediately after the insert and then again after the COMMIT.  It should help you figure out which key is causing the error.

* see section 4.2: http://www.sqlite.org/foreignkeys.html

[Fim]

"SQLite error handling will not provide more detail or explanation.  You will have to analyze the situation yourself to determine the specific cause for the error."
I suspect it was that way.
I probably will not work with foreign keys.
Thanks for the help.
/Fim W.

[Fim]

Thank you cj.
But to me it's easier to solve it programmatically in PowerBasic.
/Fim W.

[cj]

Return error value in an argument, uses replace statement and an optional trace

%TraceOn = 0
#INCLUDE "sqlitening.inc" 'foreigncc.bas
FUNCTION PBMAIN () AS LONG
LOCAL result,c1,c2 AS LONG
slOpen "sample.db3","C"
slexe "drop table if exists child"     'DROP CHILDREN FIRST REQUIRED
slexe "drop table if exists parent1"   'DROP PARENTS
slexe "drop table if exists parent2"   'DROP PARENTS
slexe "create table parent1(p1 int primary key)"
slexe "create table parent2(p2 int primary key)"
slexe "create table child(c1 int primary key, c2, foreign key(c1) REFERENCES parent1(P1), foreign key(c2) REFERENCES parent2(p2))
slexe "insert into parent1 values(1)"
slexe "insert into parent2 values(2)"
IF InsertChild(c1,c2,result) THEN ShowResult(c1,c2,result) ELSE ? USING$("Insert (#_,#)",c1,c2),,"Success"
c1=1
c2=2
InsertChild c1,c2,result
IF result THEN ShowResult(c1,c2,result) ELSE ? USING$("Insert (#_,#)",c1,c2),,"Success"
END FUNCTION

FUNCTION InsertChild(c1 AS LONG, c2 AS LONG,result AS LONG) AS LONG

LOCAL sql AS STRING
result = 0 'init required

sql = "insert into child values(@c1,@c2)"
REPLACE "@c1" WITH FORMAT$(c1) IN sql
REPLACE "@c2" WITH FORMAT$(c2) IN sql
slexe sql,"E0"

IF slGetChangeCount <> 1 THEN
  sql = "select p1 from parent1 where p1 = @c1"
  REPLACE "@c1" WITH FORMAT$(c1) IN sql
  slSel sql
  IF ISFALSE(slGetRow) THEN result = 1

  sql = "select p2 from parent2 where p2 = @c2"
  REPLACE "@c2" WITH FORMAT$(c2) IN sql
  slSel sql
  IF ISFALSE(slGetRow) THEN result+=2

  sql = "select c1 from child where c1 = @c1 and c2 = @c2"
  REPLACE "@c1" WITH FORMAT$(c1) IN sql
  REPLACE "@c2" WITH FORMAT$(c2) IN sql
  slSel sql
  IF slGetRow THEN result+=4
  FUNCTION = result
END IF
IF %TraceOn THEN ShowResult(c1,c2,result)
END FUNCTION

SUB ShowResult(c1 AS LONG,c2 AS LONG,result AS LONG)
SELECT CASE result
  CASE 0:? USING$("c1=# c2=#",c1,c2),,"Error 0 - Insert Success"
  CASE 1:? USING$("Parent1 (#) not found",c1),,"Error 1"
  CASE 2:? USING$("Parent2 (#) not found",c2),,"Error 2"
  CASE 3:? USING$("Parent1 (#) and Parent2 (#) not found",c1,c2),,"Error 3"
  CASE 4:? USING$("child c1=(#) c2=(#)",c1,c2),,"Duplicate child, Error 4"
  CASE ELSE:? "Error" + STR$(result),,"Untrapped error"
END SELECT
END SUB

[cj]

These are in no way required and adding more indexes may not be helpful

There is no index on child other than the rowid so added 3rd column (child,father,mother) all indexed
There is no mother index so added
Added integer primary key autoincrement which might prevent vacuum problem
Intentionally used duplicate column names just to be sure they were working

Might be good to  explain query plan select ... with different select statements to see if indexes are used.


drop table if exists ChildTable;
drop table if exists FatherTable;
drop table if exists MotherTable;
create table FatherTable(father integer primary key autoincrement);
create table MotherTable(mother integer primary key autoincrement);
create table ChildTable(child integer primary key autoincrement, father Integer, mother Integer, foreign key(Father) references FatherTable(father),foreign key(Mother) references MotherTable(mother));
create index ChildTableFatherIndex on ChildTable(father);
create index ChildTableMotherIndex on ChildTable(mother);
insert into FatherTable values(1);insert into FatherTable values(2);insert into FatherTable values(3);
insert into MotherTable values(1);insert into MotherTable values(2);insert into MotherTable values(3);
insert into ChildTable values(null,1,1);insert into ChildTable values(null,1,1);insert into ChildTable values(3,3,3);
-------------------------------------------------------------------------------------------------------
explain query plan    SELECT child,father,mother from childtable where Child = 2;

SEARCH TABLE childtable USING INTEGER PRIMARY KEY (rowid=?)
-------------------------------------------------------------------------------------------------------

explain query plan    SELECT childtable.mother from childtable,mothertable where MotherTable.Mother = 2;

SEARCH TABLE mothertable USING INTEGER PRIMARY KEY (rowid=?)   
SCAN TABLE childtable USING COVERING INDEX ChildTableMotherIndex
-------------------------------------------------------------------------------------------------------

explain query plan    SELECT childtable.mother from childtable,fathertable where FatherTable.Father = 2;

SEARCH TABLE fathertable USING INTEGER PRIMARY KEY (rowid=?)   
SCAN TABLE childtable USING COVERING INDEX ChildTableMotherIndex

-------------------------------------------------------------------------------------------------------

select * from childtable;
child   father   mother

1   1   1   
2   1   1   
3   3   3

[Bern Ertl]

Someone asked about this OP question on the SQLite mailing list/forum:

http://sqlite.1065341.n5.nabble.com/foreign-key-constraint-failure-td95683.html

Looks like there is a way, but it's not straightforward.