How to handle cascading deletes on inherited tables?

Question

I've run into a problem with ON DELETE CASCADE when using inherited tables.

I've got the following tables (model first) database model

Now if I want to delete an entry in Entity1.

using (var ctx = new MyEntities())
{
   var first = ctx.Entity1Set.First();
   ctx.DeleteObject(first);
   ctx.SaveChanges();
}

Simple... But SaveChanges throws the following exception.

The DELETE statement conflicted with the REFERENCE constraint FK_EntityA_inherits_BaseEntity". The conflict occurred in database "MyDB", table "dbo.BaseEntitySet_EntityA", column 'Id'. The statement has been terminated.

How do I fix this?

UPDATE

I noticed a difference between how VS2010 and VS2012 model designer outputs SQL.

VS2010:

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityA'
ALTER TABLE [dbo].[BaseEntitySet_EntityA]
ADD CONSTRAINT [FK_EntityA_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[BaseEntitySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityB'
ALTER TABLE [dbo].[BaseEntitySet_EntityB]
ADD CONSTRAINT [FK_EntityB_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[BaseEntitySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

Notice ON DELETE NO ACTION

VS2012

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityA'
ALTER TABLE [BaseEntitySet_EntityA]
ADD CONSTRAINT [FK_EntityA_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [BaseEntitySet]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityB'
ALTER TABLE [BaseEntitySet_EntityB]
ADD CONSTRAINT [FK_EntityB_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [BaseEntitySet]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

Notice ON DELETE CASCADE

That's rather significant...

score 0 · Accepted Answer · answered Apr 05 '13 at 09:53

There appears to be a bug in the Visual Studio 2010 ADO entity designer which causes incorrect SQL to be generated.

VS2010:

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityA'
ALTER TABLE [dbo].[BaseEntitySet_EntityA]
ADD CONSTRAINT [FK_EntityA_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[BaseEntitySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityB'
ALTER TABLE [dbo].[BaseEntitySet_EntityB]
ADD CONSTRAINT [FK_EntityB_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[BaseEntitySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

Notice ON DELETE NO ACTION

VS2012

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityA'
ALTER TABLE [BaseEntitySet_EntityA]
ADD CONSTRAINT [FK_EntityA_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [BaseEntitySet]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'BaseEntitySet_EntityB'
ALTER TABLE [BaseEntitySet_EntityB]
ADD CONSTRAINT [FK_EntityB_inherits_BaseEntity]
    FOREIGN KEY ([Id])
    REFERENCES [BaseEntitySet]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

Notice ON DELETE CASCADE

After applying these changes to the database I could delete without problems.

How to handle cascading deletes on inherited tables?

UPDATE

1 Answers1