Search code examples
sqldatabasesql-deletemultiple-tablescascading-deletes

Deleting from multiple tables using gridview delete command

I have a grid view utilizing sql data source. Now I want to delete a record using grid view's delete command, the problem is that a single record is based on information collected from multiple tables, so I have to delete parts of information from all those tables to completely remove the record, here is my table structure.

  1. Projects
  2. ProjectArticles
  3. ArticleStatus
  4. ProjectAssignments

Projects is a table with complete information about projects like, project title, client name etc, it has primary key "project_id"

ProjectArticles is a table that has information about how many articles are associated with one project for example if a project has 3 articles then this table has 3 rows with the following data

article_id  project_id

1 --------- 1 
2 --------- 1 
3 --------- 1

where "article_id" is the primary key.

Article status is a table with information regarding one article such as

status_id- article_id- filename ---- writer_status- editor_status- sales_status

1 -------- 1 --------- Any filename -- done --------- pending ------ pending

"status_id" is primary key

And finally ProjectAssignments is a table with data about which project is assigned to which writer it has assignment_id as primary key and using project_id as foreign key similar as shown above.

Any ideas how can I delete a complete project with all the relevant information in other tables ?

I have made this example query but it is not working, sql management studio saying "unable to parse query text".

DELETE P, A FROM Projects AS P, ProjectArticles AS A WHERE P.project_id = A.project_id AND P.project_id = @project_id

In the query text above I just used two tables to check if its working or not but its not working, any help will be appreciated.

Solution

  • Cascade is useful when you want to Delete all the row related to that Perticular primary key which is used in as foreign key in Referenctial table.

    Eg :- Primary or Parant Table Tab1(Id int (primary key) ,Name varchar(10)) Child Table Tab2(Class int, Id int (refrenctial key of Tab1(Id),address varchar(10))

    now when You want to delete 1 number id from your record then "ON DELETE CASCADE" is best but when you only want to delete one Record in Child Table then the Transaction is good.