0

I am new at databases and have a question about deleting data. I want to delete a row from one table, while that table is linked to 2 other different tables.

see pictures of the table:

JBCITY

jbsupplier

jbitem

I tried this code, but it gave me an error:

delete jbsupplier,jbitem 
from jbitem 
inner join jbsupplier 
inner join jbcity  
where jbitem.supplier=jbsupplier.id 
    and jbsupplier.city=jbcity.id;


ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`louhj600`.`jbitem`, CONSTRAINT `fk_item_supplier` FOREIGN KEY (`supplier`) REFERENCES `jbsupplier` (`id`))

Can anyone give me som advice and help, please.

Thank in advance /Louise

user1834095
  • 5,332
  • 2
  • 20
  • 38
Louise Hjalmarson
  • 11
  • 1
  • 1
    Edit your question and add the **schema of the tables including indexes**. FYI, don't post images of data/code. It can't be copied/pasted when trying to create an answer for you. – Sloan Thrasher Apr 10 '18 at 20:00
  • You need to start deleting rows from the child table and then go up to parent tables. – clinomaniac Apr 10 '18 at 20:00
  • Are you trying to delete all suppliers and their products? – Sloan Thrasher Apr 10 '18 at 20:01
  • 1
    First delete the rows from the child table and then it's parent. Also, syntax for the Delete query is wrong. It should be in this form- **DELETE FROM jbitem WHERE** dept = 1 ; – manishk Apr 10 '18 at 20:11
  • i am sorry, but i dont know how to add the tables so you can see them and copy from them.. but the question is " Remove all suppliers in Los Angeles from the table jbsupplier. This will not work right away (you will receive error code 23000) which you will have to solve by deleting some other related tuples. However, do not delete more tuples from other tables than necessary and do not change the structure of the tables, i.e. do not remove foreign keys. Also, remember that you are only allowed to use “Los Angeles” as a constant in your queries, not “199” or “900” ". – Louise Hjalmarson Apr 10 '18 at 20:22
  • Possible duplicate of [delete rows from multiple tables](https://stackoverflow.com/questions/734567/delete-rows-from-multiple-tables) – jimh Apr 10 '18 at 22:50
  • I slightly reformatted the question to make it more clear to other users. Also, you will need to specify what table you want to delete a row from, because currently your query suggests you may want to delete data from both jbsuppier and jbitem (while I guess you only want to delete from jbitem?). Adding the table definitions here (the CREATE TABLE ... queries) would also help to determine what's causing the observed error. – user1834095 Apr 12 '18 at 20:25
  • @LouiseHjalmarson If you want people to help you, you better change those screenshots to text file. Provide table structure, sample input, expected output. Learn how to ask question. https://stackoverflow.com/help/how-to-ask – Eric Apr 12 '18 at 21:52

1 Answers1

0

Foreign keys are values stored in one table that are originally from another.

Even though your schema is missing, I have a feeling it looks something like this:

City is the top level table where it's ID is referenced in Supplier. Suppliers ID is referenced in Item.

Therefore, in order to delete Suppliers from say Atlanta. You'll need to first delete all Items that are from Suppliers in Atlanta. Then delete the Suppliers.

This is most easily done using 2 queries.

delete i.* from jbitem i join jbsupplier s on i.supplier = s.id join jbcity c on s.city = c.id WHERE c.id = ^ID of Atlanta^;

delete s.* from jbsupplier s join jbcity on jbsupplier.city = jbcity.id where c.id = ^ID of Atlanta^;
jnrcorp
  • 1,905
  • 1
  • 18
  • 25