sql - Deleting a record with child items and a self-referencing constraint -

so have following tables (with irrelevant columns omitted):

create table [dbo].[step] (    [stepid] int not null primary key identity,     [parentstepid] int null,     constraint [fk_step_parentstep] foreign key ([parentstepid]) references [step]([stepid]) )  create table [dbo].[stepinput] (    [stepinputid] int not null primary key identity,     [stepid] int not null,     [childstepid] int null,     constraint [fk_stepinput_step] foreign key ([stepid]) references [step]([stepid]),     constraint [fk_stepinput_childstep] foreign key ([childstepid]) references [step]([stepid]), ) 

there step, has zero-to-many stepinputs. stepinput has optional child step, , step has optional parent step (self referencing).

this works expected. want able delete step, , have of stepinputs associated step deleted, child steps , inputs.

i using entity framework 5. there convenient way ef, or need create stored procedure, set cascade options on fk constraints, or there else better solution?

i did try using on delete cascade in various ways, nothing tried worked. read shouldn't rely on cascading deletes when comes hierarchical data, didn't understand alternatives suggested (ctes?)

would make things simpler if nixed parentstepid column? it's real utility determine if step top-level step, can use bit field for. there no parent-child relationships don't involve stepinput in between.

i pretty green when comes sql in general , sql server in particular, bear in mind answers ;)

i've tried , created simple procedure remove rows given stepid

declare @stepid int set @stepid = 1  declare @delete table (   id int )  ;with idstodelete (id) (   select childstepid   stepinput   stepid = @stepid   union   select s.childstepid   stepinput s   inner join idstodelete on i.id = s.stepid )    insert @delete (id)   select id   idstodelete    delete stepinput stepid = @stepid or stepid in (select id @delete) delete step stepid in (select id @delete) or stepid = @stepid 

the idea create recursive cte , store ids remove , store them declared table, has cte losse data after first statment.

then have delete id on table. i've removed constrain have on step table has making deletes fail.

i hope got right time, , hope understand have done in there.

cheers,