On 2015-04-09 12:00 AM, Gustav Melno wrote:
> The example below is s subset of my ical storage database which has
> problems with foreign keys. Although I tried a lot I couldn't figure
> out why the foreign key doesn't work. I took the example from the
> documentation and compared to my two tables, there is no major
> difference or I don't see the mistake. The delete statement in the
> last line doesn't work. I also tried the lates sqlite version but the
> error is the same. I always get "Error: near line 44: FOREIGN KEY
> constraint failed". Any help is appreciated.
> //...<Snipped>
Funny glitch - It's because you use "oid" which is an alias for the
row_id of the table.
To prove that point, here it is running in SQLitespeed and the results
is much the same through the Command-line utility (I added some selects
to just be sure the data actually goes in and actually gets deleted).
This first iteration fails:
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- Script Items: 10 Parameter Count: 0
-- 2015-04-09 16:23:06.966 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE ical(
id INTEGER PRIMARY KEY,
oid VARCHAR,
description VARCHAR
);
CREATE TABLE icalentry(
id INTEGER PRIMARY KEY,
calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE
CASCADE,
uuid VARCHAR
);
INSERT INTO ical(oid,description) VALUES('123','');
INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd');
SELECT * FROM ical;
-- | |descript-
-- id | oid | ion
-- ------------|------------|----------
-- 1 | 123 |
SELECT * FROM icalentry;
-- id | calendar |uuid
-- ------------|------------|----------
-- 1 | 1 |abcd
SELECT C.*, CE.*
FROM ical AS C
LEFT JOIN icalentry AS CE ON CE.calendar=C.id
;
-- | |descript-| | |
-- id | oid | ion | id | calendar |uuid
--
------------|------------|---------|------------|------------|----------
-- 1 | 123 | | 1 | 1 |abcd
DELETE FROM ical;
-- 2015-04-09 16:23:06.983 | [ERROR] FOREIGN KEY constraint failed
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.030s
-- Total Script Query Time: 0d 00h 00m and
00.006s
-- Total Database Rows Changed: 2
-- Total Virtual-Machine Steps: 196
-- Last executed Item Index: 8
-- Last Script Error: Script Failed in Item 7:
FOREIGN KEY constraint failed
--
------------------------------------------------------------------------------------------------
-- 2015-04-09 16:23:06.984 | [Info] Script failed - Rolling
back...
-- 2015-04-09 16:23:06.985 | [Success] Transaction Rolled back.
-- 2015-04-09 16:23:06.985 | [ERROR] Failed to complete:
Script Failed in Item 7: FOREIGN KEY constraint failed
-- ------- DB-Engine Logs (Contains logged information from all DB
connections during run) ------
-- [2015-04-09 16:23:06.937] APPLICATION : Script
D:\Documents\SQLiteAutoScript.sql started at 16:23:06.937 on 09 April.
-- [2015-04-09 16:23:06.981] ERROR (284) : automatic index on
icalentry(calendar)
--
================================================================================================
Nevermind that error 284, that's just SQLite infoming us it had to make
an index to do the queries, so you should add an index there, but it
matters none in the test.
And this next iteration succeeds after I changed the "oid" to "xid":
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- Script Items: 10 Parameter Count: 0
-- 2015-04-09 16:25:11.141 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE ical(
id INTEGER PRIMARY KEY,
xid VARCHAR,
description VARCHAR
);
CREATE TABLE icalentry(
id INTEGER PRIMARY KEY,
calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE
CASCADE,
uuid VARCHAR
);
INSERT INTO ical(xid,description) VALUES('123','');
INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd');
SELECT * FROM ical;
-- | |descript-
-- id | xid | ion
-- ------------|------------|----------
-- 1 | 123 |
SELECT * FROM icalentry;
-- id | calendar |uuid
-- ------------|------------|----------
-- 1 | 1 |abcd
SELECT C.*, CE.*
FROM ical AS C
LEFT JOIN icalentry AS CE ON CE.calendar=C.id
;
-- | |descript-| | |
-- id | xid | ion | id | calendar |uuid
--
------------|------------|---------|------------|------------|----------
-- 1 | 123 | | 1 | 1 |abcd
DELETE FROM ical;
SELECT * FROM ical;
SELECT * FROM icalentry;
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.033s
-- Total Script Query Time: 0d 00h 00m and
00.009s
-- Total Database Rows Changed: 4
-- Total Virtual-Machine Steps: 228
-- Last executed Item Index: 10
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2015-04-09 16:25:11.161 | [Success] Script Success.
-- 2015-04-09 16:25:11.162 | [Success] Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all DB
connections during run) ------
-- [2015-04-09 16:25:11.114] APPLICATION : Script
D:\Documents\SQLiteAutoScript.sql started at 16:25:11.114 on 09 April.
-- [2015-04-09 16:25:11.157] ERROR (284) : automatic index on
icalentry(calendar)
--
================================================================================================
