deleting records in associated foeign key table

Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks

There are a few options, but the best two, IMO, are:

Write a SQL statement that first deletes the associated records and then deletes the parent record. Imagine that you had a Categories table and a Products table, and there is a one to many relationship from Categories to Products. Now, imagine that we wanted to delete a category that has associated products. We'd first need to delete the Products - DELETE FROM Products WHERE CategoryID = @.CategoryIDToDelete - then we'd delete the category: DELETE FROM Categories WHERE CategoryID = @.CategoryIDToDelete.
|||

Thank a lot Scott for quick response...i have got multiple tables and all these table are kind of chained up with each other using multiple foreign key .i am liking the idea of 'cascade delete',I have already set up the foreign key constraints in all these table.I will read up on cascade delete,,have no idea about it rightnow...

|||

Scott is correct on the two main options. Given that this question is a "newbie" question, it's probably best to clarify that the first option is to write TWO sql statements, not one - a delete for each table.

|||

David, do you mean,two sql statements(or as many delete as reqd for linked up tables) within one stored procedures or two(or multiple depends?) separate sql statements ?i am heavily using SP in my application.thanks

|||

I mean 1 delete statement per table that needs deleting. Those delete statements can be in one stored procedure.

My comments were to avoid some one new to sql trying to issue a delete statement like this (because it will never work):

delete department and employee where department_id = 5

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
Ryan
Create an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>
|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>
sql

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hi All,
>> If I delete a field in SQL Server, will all constraints, foreign keys,
>> indices etc. associated with that field also be removed?
>> Thanks in advance
>> Ryan
>>
>

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>

delete w/ foreign key question

Is there a way to see the locks associated with a delete statement on a table (tab1) that has 5 or 6 Foreign key relationships. Trying to understand the impact of the delete on concurrency There are several delete deadlocks on one of the foreign key tables (tab2) and the system does not delete from the table (tab2) that is throwing the delete deadlock error. Wondering about the impact of foreign keys on deletes on Tab1 on Tab2.

Didn't see anything in query analyzer that would show the actual locks. It seems to have scans or such but no lock levels etc are shown.

Does anyone have any knowledge of how to see the actual locks thrown by a given statement. The delete on Tab1 statement is very quick so using EM has proved fruitless.

MikeOh, such simple question but resolving blocking/deadlocking is really an art. You might want to start here.

http://support.microsoft.com/kb/224453