How to delete rows in linked SQL tables?

Question

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

Here are the following tables:

Here is an example of the contents of the tables:

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

Answer 1

In manual deletion there will be something like this:

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

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

Answer 2

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

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

I see that you already have foreign keys, so you just need to make sure that they have the CASCADE option for DELETE.

Answer 3

Without a stored procedure, use Constrains -> http://www.mssqlcity.com/Articles/General/using_constraints.htm OnDelete Cascade, so when you delete a line in Glasses, it will be deleted in the Glasses color as well.

Answer 4

In your external key constraint for the GlassesColor join GlassesColor enter on delete cascade . What this means is that when the entry associated with the primary key is deleted, the corresponding link string of the foreign key will also be deleted.

So, the GlassesColor table GlassesColor will look like this:

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

Answer 5

I answered something like this for using INFORMATION_SCHEMA in MSSQL

SQL Server: cascading equivalent quote table?

Answer 6

Here's the easiest way to do this in Microsoft SQL Server: when creating a foreign key constraint, add ON UPDATE CASCADE ON DELETE CASCADE in its definition. I assume that you want the changes in GlassesID to also propagate. ;-) If not, then you will not need "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