Deleting records from a table with heirachial data

Question

I have two tables in an SQL database with fields as follows:

Table A: Key | Id1 | Name

Table B: Key | Id2 | Idx1 | Idx2 | Id1

Table B stores hierarchical data. Field Id1 in Table B is linked back to Id1 in Table A with FKs set up such that deleting an item in Table 1 (say with Id1=1) will delete all of the items in Table B (with the same Id1) (ie. cascade).

What I want to do is that for a record in Table B that is deleted with a certain Id1 (as a consequence of a record with the same Id1 in Table A), all of the records in Table B with the same Id2 to be deleted too.

For example ...

Table A:
  1,1,A
  2,2,B
  3,3,C

Table B:
  1,1,0,0,1
  2,1,0,1,2
  3,1,0,2,3
  4,1,1,0,3
  5,1,1,1,2
  6,2,0,0,3

I now delete Item 1 (with Id1=1) from Table A such that:

Table A:
  2,2,B
  3,3,C

Table B:
  6,2,0,0,3

Can this be done automatically (eg. through triggers, etc.) in SQL or would I need to handle it 'manually' outside of the database?

Harsh Sharma · Accepted Answer · 2017-06-12T05:40:33.817

In this case you can take advantage of DELETE CASCADE feature of SQL Server.

If you have parent-child mapping between two tables in SQL you can delete child table records along with a single query to delete parent records.

To achieve this you will have to enable Delete cascading while establishing the relationship between the tables, following way:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE
);

Or if you have already defined the table data you can use the following method to do this:

ALTER TABLE dbo.T2
   DROP CONSTRAINT FK_T1_T2   -- or whatever it's called

ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

or If you are using MYSQL (as it is not mentioned in question) you can follow the instructions on following StackOverflow post:

MySQL foreign key constraints, cascade delete

I can see how this will delete the (single) record in Table B that has the same Id1. What I'm wanting is for that record (Key=1) to be deleted AND THEN all of the records that have the same Id2 as that record in Table B to be deleted too (Key=2,3,4 and 5). (I have updated the question as I left off the Key values to Table B.) — Alex M, Jun 12 '17 at 20:39
I can see how this will delete the (single) record in Table B that has the same Id1. What I'm wanting is for that record (Key=1) to be deleted AND THEN all of the records that have the same Id2 as that record in Table B to be deleted too (Key=2,3,4 and 5). (I have updated the question as I left off the Key values to Table B.) If an element with Id1 is deleted in Table A (record i), then any element in Table B with the same Id1 is deleted (by cascading) (record ii) but so too shall any record in Table B that ha the same Id2 (as record ii). — Alex M, Jun 12 '17 at 20:45

Deleting records from a table with heirachial data

1 Answers1