I'm trying to figure out what the problem is for hours :(
Table looks like this:
CREATE TABLE IF NOT EXISTS pages(
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
parent INTEGER DEFAULT NULL,
content TEXT,
time INTEGER,
FOREIGN KEY(category) REFERENCES categories(id) ON DELETE CASCADE,
FOREIGN KEY(parent) REFERENCES pages(id) ON DELETE CASCADE,
UNIQUE(title) ON CONFLICT REPLACE
)
The insert query:
INSERT OR REPLACE INTO pages (title, parent, content, time) VALUES (?, ?, ?, ?)
title
should be unique, so if it founds a record with the same title it should replace it. It gets replaced, but the ID changes too! This is a problem because all other records that were having a parent field that was pointing to the old ID are getting removed :(
Why is this happening?