How to delete rows in associated SQL tables?

Question

I want to delete rows in GlassesColor table that associated with GlassesID in Glasses table. I want to implement this in stored procedure.The stored procedure gets only one parameter from client side , a CollectionID.

Here are the following tables:

Here is example of tables content:

Any idea how can i implement this? Thank you in advance!

Answer 1

The easiest way is to define FOREIGN KEYS with CASCADE options for DELETE.

http://msdn.microsoft.com/en-us/library/aa933119%28v=sql.80%29.aspx

I see you already have Foreign Keys defined, so you just need to make sure they have CASCADE option for DELETE.

Answer 2

Manually deleting would be something like this:

delete 
from GlassesColor 
where GlassesID in (select GlassesID from Glasses where CollectionID = 3)

However, unless this is a one time cleanup, you should start specifying foreign key cascading rules, like the other answers already suggested.

Answer 3

Without stored procedure, use Constrains -> http://www.mssqlcity.com/Articles/General/using_constraints.htm OnDelete Cascade, so when you delete row in Glasses, it will deleted in Glasses color also.

Answer 4

In your foreign key constraint for the GlassesColor join table, have on delete cascade. What that means is when the primary key referenced record is deleted, the corresponding foreign key reference row will also be deleted.

So your GlassesColor table definition would look like this:

create table GlassesColor
(
    GlassesId int foreign key references Glasses(GlassesID) on delete cascade,
    .....
)
go

Answer 5

I answered something similar for using the INFORMATION_SCHEMA in MSSQL

SQL Server: drop table cascade equivalent?

Answer 6

Here's the easiest way to do it in Microsoft SQL Server: when you create the foreign key constraint, add ON UPDATE CASCADE ON DELETE CASCADE to its definition. I'm assuming you want changes to GlassesID to also propogate. ;-) If not, then you don't need the "ON UPDATE CASCADE".

Example:

ALTER TABLE
  [GlassesColor]
WITH CHECK ADD CONSTRAINT
  [FK_GlassesColor_GlassesID]
FOREIGN KEY
  ([glassesID]) REFERENCES [Glasses] ([glassesID])
ON UPDATE CASCADE ON DELETE CASCADE