Deleting from multiple tables using gridview delete command

Question

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.

Answer 1

There are a few ways to attack this.

You can declare your constraints in the SQL server and set Cascade on. When you delete depending on the SQL server you are using you can Delete from P where condition Cascade this will tell the SQL server to delete and then follow all references to cascade this delete down.

Often you can set such a condition in a DAL if you have everything in memory though, setting cascade on in the DAL if it supports it. Risk is that you have a concurrency issue and an issue to load all possible referenced data in memory before you know conclusively that it will succeed.

Note that cascade is usually turned off because it can turn into a serious problem if you get something wrong. Many people would prefer to write a conclusive query that deletes using your condition in order as a transaction.

Begin Transaction
    Delete from Tables_Child where ID in (--your query)
    Delete from Tables_Child2 where ID in (--your query)
    (your_Query)
Commit Transaction

That way you know what is about to occur and you can control it without ending up with cycles.

Answer 2

First i Want to know that what do u want to delete.

(1) Do U Want to delete all the detail of the single [Project] Table. If yes then its not a big issue because i think that you already have maintain the Primary and Foreign Key You Just have to do is just alter your foreign key constraint with "ON DELETE CASCADE" by this all the information of the Project(project_Id) will be deleted.

(2) If You want to delete single status like you mentioned

" 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 "

Then you just need to delete one table row of "ArticleStatus"

If you want to know about the "ON CASCADE DELETE" then tell me

Answer 3

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.