On 2015-04-09 07:57 PM, Simon Slavin wrote:
> On 9 Apr 2015, at 6:04pm, Gustav Melno <knarks at online.de> wrote:
>
>> 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.
> The three names for the integer primary key column (oid, rowid, _rowid_) work
> only if the table doesn't have a defined column of that name. This allows
> compatibility with people who didn't know that they were had special meanings
> for SQLite. Clever, isn't it ?
Except it clearly did not work like that in the OP's case. He had
defined "oid" in his table, but then when he inserted into column oid,
the value either went to the rowid column or somehow messed up the rowid
enough to cause a constraint failure. I'm starting to think this might
be a bug.
I've checked it like this (Copy paste this to a file and feed to an
sqlite parser):
-- 2015-04-09 20:34:18.791 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE idTest( -- Table with every row id reference
id INTEGER PRIMARY KEY,
col1 TEXT,
oid INTEGER,
rowid INTEGER,
_rowid_ INTEGER
);
CREATE TABLE fkTest( -- table to test the Foreign Key
id INTEGER PRIMARY KEY,
Data TEXT,
FOREIGN KEY (id) REFERENCES idTest(id)
ON UPDATE CASCADE
ON DELETE CASCADE -- changes and deletes must cascade
);
INSERT INTO idTest (col1, oid, rowid, _rowid_) VALUES
('TestA', 10, 100, 1000),
('TestA', 20, 200, 2000),
('TestA', 30, 300, 3000)
;
INSERT INTO idTest (col1, oid) VALUES ('TestB', 'aaa');
INSERT INTO fkTest (id, Data) VALUES (last_insert_rowid(), 'FK 1');
INSERT INTO idTest (col1, oid) VALUES ('TestC', 20);
INSERT INTO fkTest (id, Data) VALUES (last_insert_rowid(), 'FK 2');
-- So far all happens as expected and works perfectly...
SELECT * FROM idTest;
-- id | col1 | oid | rowid | _rowid_
-- ------------ | ------- | ----- | ----- | -------
-- 1 | TestA | 10 | 100 | 1000
-- 2 | TestA | 20 | 200 | 2000
-- 3 | TestA | 30 | 300 | 3000
-- 4 | TestB | aaa | |
-- 5 | TestC | 20 | |
SELECT F.*, I.*
FROM fkTest AS F
LEFT JOIN idTest AS I ON F.id=I.id;
-- id | Data | id | col1 | oid | rowid |
_rowid_
-- ------------ | ------ | ------------ | ------- | ----- | ----- |
-------
-- 4 | FK 1 | 4 | TestB | aaa | |
-- 5 | FK 2 | 5 | TestC | 20 | |
-- Here the problem happens. this statement should in SQL terms succeed
and update the fkTest
-- table - here I am NOT using oid as the OP did, I am using the correct
reference but it still fails.
-- And - it works if I remove the rowid references from the first table
create.
UPDATE idTest SET id = 40 WHERE id = 4;
-- 2015-04-09 20:34:18.807 | [ERROR] FOREIGN KEY constraint failed
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.029s
-- Total Script Query Time: 0d 00h 00m and
00.005s
-- Total Database Rows Changed: 8
-- Total Virtual-Machine Steps: 396
-- Last executed Item Index: 12
-- Last Script Error: Script Failed in Item 11:
FOREIGN KEY constraint failed
--
------------------------------------------------------------------------------------------------
I won't add more test script outputs, but it fails for a DELETE request
too. I think including rowid / oid / _rowid_ in a table works normally
since the correct values ended up in the correct columns, but somehow
including those aliases confuses the FK check mechanism.
