Deleting records from multiple tables in SQL server

I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.

I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):

Table 1: Classes --Columns: ClassID, ClassName

Table 2: Roster--Columns: ClassID, StudentID, Student Name

Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName

Table 4: Scores--StudentID, AssignmentID, Score

What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.

What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.

The stored procedure I created was

DELETE FROM Classes WHERE ClassID=@.classid

I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:

The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.

What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?

I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.

Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.

Further, is there something else I need to do besides assigning primary keys and foreign keys?

WHen you create a foreign key, there are some additional options you have to set to tell it to do the cascade delete.

If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:

Code Snippet

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)

ON DELETE CASCADE;

If using SSMS, modify the table and go to where you created the relationship. If you look around in there you should see an option to set the "Delete Rule" Set that to CASCADE.

|||

OK, the concept of deleting rows from multiple tables in a single delete statement cannot be done in just that statement. There is the concept of triggers on the tables that do deletes in a cascading style, but I would not recommend you do it that way for sake of control of the actions of the data.

But I udnerstand what you want to do, and the best way to explain it is this:

Say you have these tables and each one has a relationship up the chain. If you have Classes, and they are on Rosters, and Assignments are given to Classes and Scores have Students and Assignments you have to see which is the last one in the chain.

So in your case you have Classes that is the base and then you have Classes in Rosters (this is the next level) and you have Classes in Assignments (same level as Rosters).

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

So say you no longer had ClassId 3 and you wanted to just get rid of all the records that are associated with ClassId 3. Here are the steps that you would need to take. You would delete in the reverse order than you inserted.

So in this case, you would want to use the ClassId = 3 to get all the assignments that have that ClassId and delete the Scores that have the AssignmentId and then delete the Assignments with the ClassId = 3

Then you would delete the Rosters with the ClassId = 3 and then finally delete the Classes with ClassId = 3

SQL:

DELETE Scores

FROM Assignments A

INNER JOIN Scores S ON A.AssignmentId = S.AssignmentId

WHERE A.ClassId = 3

DELETE Assignments

WHERE ClassId = 3

DELETE Roster

WHERE ClassId = 3

DELETE Classes

WHERE ClassId = 3

So you really just need to delete the Foreign Key tables records with the Primary Key record in it first and then delete the Primary Key records in the Primary table or Base table last.

HTH.

Ben Miller

|||I disagree with you an the answer that this cannot be done within one statement as the suggestions from Andrew about Cascading deletes should solve the problem, if the architecture is appropiate for the original poster.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Ben

I also disagree with your presentation. With a properly established set of relationships, CASCADE DELETE works wonderfully. The PK-FK relationships must be properly set-up, and there cannot be any circular relationships.

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

In this situation, a deletion on [Classes] will remove related data from all lower tables. Deleting [Assignments] will also delete related data from [Scores]. A deletion on [Roster] or [Scores] will only affect those tables.