FOREIGN KEY allows INSERT where it should not

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

FOREIGN KEY allows INSERT where it should not

Kühne, Tobias
Hello,
maybe I am missing something, but the example you give on foreign keys allows insert where in my opinion it should clearly not. Am I missing something?
Built sqlite3 binary myself on 'CYGWIN_NT-6.1 local 2.3.1(0.291/5/3) 2015-11-14 12:44 x86_64 Cygwin'. See the shell script to reproduce:


#!/bin/sh

rm -f /tmp/sqlite3.db

sqlite3 -version
# 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

cat <<"EOF" | sqlite3 -batch -echo /tmp/sqlite3.db
/* Example from https://sqlite.org/foreignkeys.html */
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
-- This fails because the value inserted into the trackartist column (3)
-- does not correspond to row in the artist table.
INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
EOF

sqlite3 /tmp/sqlite3.db 'SELECT * FROM track'
# Should be empty as the INSERT should have failed
# However, the result is:
#14|Mr. Bojangles|3





Gruß,
_______________________________

Tobias Kühne
Produktsoftwareentwickler
JOSEPH VÖGELE AG
Joseph-Vögele-Str. 1, D-67075 Ludwigshafen
www.voegele.info

T:  +49 621 / 8105 431
F:  +49 621 / 8105 493
[hidden email]
_______________________________

Joseph Vögele Aktiengesellschaft, Ludwigshafen
Amtsgericht Ludwigshafen HRB 62108
Vorsitzender des Aufsichtsrats: Domenic G. Ruccolo   Vorstand: Dipl.-Ing. Bernhard Düser   Dr.-Ing. Christian Pawlik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY allows INSERT where it should not

David Raymond
Basic question, did you remember to turn on foreign key support? The default default is for foreign key support to be off when you connect. So unless you ran "pragma foreign_keys = on;" or compiled with SQLITE_DEFAULT_FOREIGN_KEYS=1 then it won't enforce them.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Kühne, Tobias
Sent: Friday, February 02, 2018 3:27 AM
To: '[hidden email]'
Subject: [sqlite] FOREIGN KEY allows INSERT where it should not

Hello,
maybe I am missing something, but the example you give on foreign keys allows insert where in my opinion it should clearly not. Am I missing something?
Built sqlite3 binary myself on 'CYGWIN_NT-6.1 local 2.3.1(0.291/5/3) 2015-11-14 12:44 x86_64 Cygwin'. See the shell script to reproduce:


#!/bin/sh

rm -f /tmp/sqlite3.db

sqlite3 -version
# 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

cat <<"EOF" | sqlite3 -batch -echo /tmp/sqlite3.db
/* Example from https://sqlite.org/foreignkeys.html */
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
-- This fails because the value inserted into the trackartist column (3)
-- does not correspond to row in the artist table.
INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
EOF

sqlite3 /tmp/sqlite3.db 'SELECT * FROM track'
# Should be empty as the INSERT should have failed
# However, the result is:
#14|Mr. Bojangles|3





Gruß,
_______________________________

Tobias Kühne
Produktsoftwareentwickler
JOSEPH VÖGELE AG
Joseph-Vögele-Str. 1, D-67075 Ludwigshafen
www.voegele.info

T:  +49 621 / 8105 431
F:  +49 621 / 8105 493
[hidden email]
_______________________________

Joseph Vögele Aktiengesellschaft, Ludwigshafen
Amtsgericht Ludwigshafen HRB 62108
Vorsitzender des Aufsichtsrats: Domenic G. Ruccolo   Vorstand: Dipl.-Ing. Bernhard Düser   Dr.-Ing. Christian Pawlik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users