Deleted specific id from different tables

Question

I would like to delete specific IDs from different tables to remove these values.

My formatted question is this:

Earthquake! California sinks into the ocean, and all our suppliers in California disappear under the water. Write a query to delete them from the database. What happens when you run the query, if you have declared a foreign key? What happens if you have not declared a foreign key?

The problem is that I don't really know how to handle this

I'm expecting it to delete all the columns with the id that "California" has. One this is that California is mentioned multiple times with different IDs because there are different Cities from California such as "Los Angeles" and "San Francisco"

Edit:

Tables that contains Cities/State

Table Supplier with columns city and state

Table Item with column Supplier(Id of table Supplier)

Answer 1

Assuming you don't have cascading deletes enabled, then you must first delete from the Items table all rows which relate to Californian suppliers. If you try to delete the suppliers first, it will fail for any Suppliers which are referenced in the Item table, because this would violate the foreign key constraint. The constraint ensures the integrity of the data, and makes sure that entries in the Item table cannot contain orphaned/non-existent Supplier IDs.

So if "California" is State ID 20, then you can run:

DELETE FROM Item where Supplier IN (SELECT ID FROM Supplier WHERE State = 20)

and then

DELETE FROM Supplier WHERE State = 20

and it will delete all the Item rows which link back to a Californian supplier, followed by all the Suppliers.

And of course if California no longer exists, you might want to DELETE FROM States where ID = 20 as well. (I'm assuming here that you have a properly normalised database with a States table, which you really should have, rather than duplicating the State description every time in your Supplier table).

However if you were to define your foreign key(s) with ON DELETE CASCADE) i.e. "cascading delete", then you could just run the deletion command directly against the Suppliers table (or even the States table, if you have one), and it would remove all the dependent rows in the foreign key table(s) automatically.