1

I am using sybase's sqlanywhere17. I have tables inventory and user_inventory. I get an error when deleting a row in table "inventory":

Row with identifier of 1 cannot be deleted, because it has references in table user_inventory

Even though I have created table user_inventory with FOREIGN KEY (ITEM_ID) REFERENCES INVENTORY (ITEM_ID) ON DELETE CASCADE, it doesn't delete them.

Both tables have item_id ( item_id is PK in table inventory)

Why can't I delete inventory rows?

dontstopbelievin
  • 11
  • 2
  • I am using sqlanywhere17 – dontstopbelievin Nov 08 '19 at 18:15
  • Are there any tables that are referencing the user_inventory table? If those are not setup to cascade then this will break. If possible, can you provide the sql schema that you are using (sql used to create the tables)? – dmoore1181 Nov 08 '19 at 19:11
  • @dmoore1181 No references to user_inventory. The inventory table was migrated from other db, but user_inventory was created: CREATE TABLE USER_INVENTORY ( USER_ID INTEGER NOT NULL, ITEM_ID NUMERIC(8) NOT NULL, QUANTITY INTEGER NOT NULL DEFAULT 1, TYPE CHAR(2) NOT NULL, DATE_FROM TIMESTAMP NULL, DATE_TO TIMESTAMP NULL, CUSTOMIZATION_TYPES TEXT NULL, PRIMARY KEY (USER_ID, ITEM_ID), FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) ON DELETE CASCADE, FOREIGN KEY (ITEM_ID) REFERENCES INVENTORY (ITEM_ID) ON DELETE CASCADE ); By the way I use sqlanywhere17 with a bit difference from mysql. – dontstopbelievin Nov 09 '19 at 09:58
  • Does it work if you manually delete out the items that are referenced in the `user_inventory` table before deleting from the `inventory` table? If it won't let you delete at that point, then it may lead you to the true issue. – dmoore1181 Nov 12 '19 at 20:34

0 Answers0