0

pls guys...is there a way delete all iDs referenced to particular ID in a database when that particular ID is deleted?

take for instance i have a company data that has list of departments with staff under them and also all those staff have different records under them like attendance, bio data, salary scheme e.t.c.

the way i designed my database is that staffs under a department are referenced to that department using a foreign key and like wise all records under a particular staff is referenced to that staff using a foreign key.

now, is it possible to have a sort of trigger or any other sort of mechanism so that when i delete a staff from the staffs table every other record referenced to that staff will be deleted also.

i would have considered using multi table delete but since am using java sqlite, it doesnt support that from what i read. And i wouldnt like to go through the stress of having to delete all records one after the other from all the tables.

thanks a lot guys

spynoble
  • 33
  • 1
  • 9

1 Answers1

1

ON DELETE CASCADE could help you. Take a look at the documentation:

CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted.

you just need to reference the row id of the parent table:

CREATE TABLE parentTable(
  parent_id    INTEGER PRIMARY KEY, 
  data  TEXT
);

CREATE TABLE childTable(
  child_id     INTEGER,
  parent_id INTEGER REFERENCES parentTable(parent_id) ON DELETE CASCADE
);

and I think you would also need to active the foreign keys for this to work:

PRAGMA foreign_keys = ON;
ILovemyPoncho
  • 2,762
  • 2
  • 24
  • 37
  • so you mean if i am to delete the parentTable you referenced above the child table will go too?? – spynoble Aug 12 '14 at 22:24
  • It means that when a row in the parent table is deleted, all the related rows in the child table, which has a FOREIGN KEY pointing to deleted parent table's row, will be automatically deleted. So, if you delete the whole content of the parent table, the content of the child table is also going to be deleted. – ILovemyPoncho Aug 12 '14 at 22:49
  • please will something like this still be applicable when i want to display all rows referenced to the parent ID. for example i have a table of staffs and i will love to display the staff and all his/her records from other tables in one table? – spynoble Aug 13 '14 at 01:29
  • for that you need to use JOIN http://www.tutorialspoint.com/sqlite/sqlite_using_joins.htm – ILovemyPoncho Aug 13 '14 at 01:51
  • Please don't forget to mark the answer as accepted if your problem is already solved. You can do it clicking on the check mark beside the answer to toggle it from hollow to green. – ILovemyPoncho Aug 13 '14 at 01:52
  • I just read the tutorial and i discovered that only two tables were involved in the Join...but in my case i need to join up to 4 tables minimum. please i dont know if that will be possible...thanks a lot for your time – spynoble Aug 13 '14 at 02:28
  • yeah, don't worry mate. Just keep adding as many JOINs as you need until you relate all the tables http://stackoverflow.com/questions/11105895/sqlite-left-outer-join-multiple-tables (note that the letters 'a', 'b' and 'c' are just aliases for the tables) – ILovemyPoncho Aug 13 '14 at 03:44