Thanks for the help. Adding a trailing underscore helped also. I'm still
wondering why insertion worked at all because defining oid as column
name with the type VARCHAR should result in an error on execution.
>
>
> 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)
> --
> ================================================================================================
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
