-1

I am using (in SQL) a DELETE with multiple SELECTs, 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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Madson Paulo
  • 57
  • 1
  • 9
  • 1
    try this https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server – Kostya May 06 '19 at 23:44
  • What did you learn when checking all the cases in many answers to many googles of your error message? – philipxy May 07 '19 at 00:40
  • Well @philipxy, I thought I was not clearly and correctly specifying which fields I was trying to delete, so I got the error, but as Kostya pointed out, it's about the need to use DELETE ON CASCADE. So I will try to ALTER my table in add it. Will post the result ASAP. – Madson Paulo May 07 '19 at 00:57
  • Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) What is your DBMS? Your "tile_items" FK declaration is illegal standard/common SQL, even before you run a select or delete--there is no PK/UNIQUE "tiles" ("id") for it to reference. Did you really get a select to run? Read about FK declarations. – philipxy May 07 '19 at 02:59
  • Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. Please format your code reasonably. Read the edit help re code blocks. – philipxy May 07 '19 at 05:22
  • @marc_s Thanks for the code formatting effort. But some of us don't have the combined real estate & eyesight for such profligate indentation. – philipxy May 07 '19 at 05:22
  • 1
    You should always tag SQL questions with the DBMS you are using. I suppose this is SQLite? – Thorsten Kettner May 07 '19 at 06:30
  • (Obviously) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy May 07 '19 at 06:52
  • Possible duplicate of [What is causing Foreign Key Mismatch error?](https://stackoverflow.com/questions/5208245/what-is-causing-foreign-key-mismatch-error) – philipxy May 07 '19 at 06:55
  • @ThorstenKettner, sorry, I am using DB Browser for SQLite v3.11.2, in a Windows 10 x64 – Madson Paulo May 08 '19 at 00:08
  • @Kostya, since I am using DB Browser for SQLite, as stated in: https://stackoverflow.com/questions/16537154/create-a-foreign-key-in-sqlite-database-browser , **"If the tables already exist and you don't want to build a complete script then you're out of luck, SQLite doesn't support adding foreign keys once the table has been generated"**. You happen to know if there is anything else to try? All system is built and running, could not drop the table. If you tell there is nothing else to do, will not be lethal, this code was for personal learning only. – Madson Paulo May 08 '19 at 00:49

1 Answers1

1

What is weird is that the same statement, but with SELECT * FROM, instead of DELETE FROM, returns all the 44 matching records.

This isn't weird. The error reports a foreign key problem. Foreign keys exist to guarantee data consistency in inserts, updates, and deletes. If you merely select data, foreign keys don't matter at all.

Foreign key mismatch - "tile_items" referencing "tiles"

This error is not really on your delete statement, but on the database and its foreign keys. Your statement is valid, but your DBMS allows invalid foreign key constraints and only reports them when you insert, update or delete data. It would be way better in my opinion did the DBMS prevent such situations beforehand.

In your database you are using composite keys. The unique key for the tiles table is (id, world_id), such that a tile ID does only have a meaning in combination with a world ID. The same tile ID can mean one tile in world X and another in world Y. (It would be better, didn't you call this column id, as it doesn't identify a record in the table. I would suggest a non-ID name like tile_no or tile_code for a column that is only part of the uniquely identifying column group. The same applies for the house_id of course.)

FOREIGN KEY ("tile_id") REFERENCES "tiles" ("id")

Here you want to say which tiles row a tile_items row refers to. But which is it? The tile ID alone does not suffice to identify a row in that table, you are missing the world ID. It must be:

FOREIGN KEY (tile_id, world_id) REFERENCES tiles (id, world_id)
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thanks for being so clear about it all. There are two points here... Since I am using BD Browser for SQLite, as stated in: https://stackoverflow.com/questions/16537154/create-a-foreign-key-in-sqlite-database-browser **"If the tables already exist and you don't want to build a complete script then you're out of luck, SQLite doesn't support adding foreign keys once the table has been generated"** This makes me unable to change the table, nor create another one, since all the system is already built, I was only making a new script. In summary, there is nothing I can do? – Madson Paulo May 08 '19 at 00:36
  • Also, I tried using the composite key in the SELECT and it worked, but not with the DELETE. DELETE FROM tile_items WHERE (tile_id, world_id) IN (SELECT id, world_id FROM tiles WHERE (house_id, world_id) IN (SELECT id, world_id FROM houses WHERE owner <> 0 AND owner NOT IN (SELECT id FROM players))) – Madson Paulo May 08 '19 at 00:41
  • I understand that you cannot `ALTER TABLE tile_items ...` in order to remove the wrong constraint and add the correct one. But why do you say you cannot create a table? You should be able to use `ALTER TABLE tile_items RENAME TO renamed_table;` to rename your existing table. Then `CREATE TABLE tile_items (...);` with the correct foreign key constraint and then `INSERT INTO tile_items SELECT * FROM renamed_table` to copy its content (and at last `DROP TABLE renamed_table`). – Thorsten Kettner May 08 '19 at 05:30