I am using (in SQL) a DELETE
with multiple SELECT
s, but I am getting a foreign key mismatch.
The SQL statement is:
DELETE FROM tile_items
WHERE tile_id IN (SELECT id
FROM tiles
WHERE house_id IN (SELECT id
FROM houses
WHERE owner <> 0
AND owner NOT IN (SELECT id FROM players)))
But I get an error
Foreign key mismatch - "tile_items" referencing "tiles"
What is weird is that the same statement, but with SELECT * FROM
, instead of DELETE FROM
, returns all the 44 matching records.
I also have tried to include the table name before the field, but I get the same error.
DELETE FROM tile_items
WHERE tile_items.tile_id IN (SELECT id
FROM tiles
WHERE tiles.house_id IN (SELECT id FROM houses
WHERE houses.owner <> 0
AND houses.owner NOT IN (SELECT id FROM players)))
Not sure if is needed, but here are the tables:
CREATE TABLE "tile_items"
(
"tile_id" INTEGER NOT NULL,
"world_id" INTEGER NOT NULL DEFAULT 0,
"sid" INTEGER NOT NULL,
"pid" INTEGER NOT NULL DEFAULT 0,
"itemtype" INTEGER NOT NULL,
"count" INTEGER NOT NULL DEFAULT 0,
"attributes" BLOB NOT NULL,
UNIQUE ("tile_id", "world_id", "sid"),
FOREIGN KEY ("tile_id") REFERENCES "tiles" ("id")
)
CREATE TABLE "tiles"
(
"id" INTEGER NOT NULL,
"world_id" INTEGER NOT NULL DEFAULT 0,
"house_id" INTEGER NOT NULL,
"x" INTEGER NOT NULL,
"y" INTEGER NOT NULL,
"z" INTEGER NOT NULL,
UNIQUE ("id", "world_id"),
FOREIGN KEY ("house_id", "world_id") REFERENCES "houses" ("id", "world_id")
)
As the error cited only the tables "tile_items" and"tiles", posted only those schemas.
Hope somebody can explain why I can't DELETE but can SELECT with this statement, thanks.