none
Delete Record RRS feed

  • Question

  • Hello,

    I am deleting a Document with Linq and the related records in DocumentsPages are deleted but the Pages are not.

    What am I missing? Here are my SQL tables and relationships which I mapped using Linq To Sql:

    create table dbo.Documents
    (
      Id int identity not null,
      Created datetime not null,
      [File] varbinary(max) filestream constraint DF_Documents_File default
    (0x),
      Updated datetime not null,
        constraint PK_Documents primary key clustered(Id)
    ) -- Documents
    
    create table dbo.DocumentsPages
    (
      DocumentId int not null,
      PageId int not null,
        constraint PK_DocumentsPages primary key clustered(DocumentId,
    PageId)
    ) -- DocumentsPages
    
    create table dbo.Pages
    (
      Id int identity not null,
      Number int not null,
      [Key] uniqueidentifier not null rowguidcol constraint U_Pages_Key
    unique,
      [File] varbinary(max) filestream constraint DF_Pages_File default
    (0x),
        constraint PK_Pages primary key clustered(Id)
    ) -- Pages
    
    
    And the following relationships:

    alter table dbo.DocumentsPages
    add constraint FK_DocumentsPages_Documents foreign key (DocumentId)
    references Documents(Id) on delete cascade on update cascade,
        constraint FK_DocumentsPages_Pages foreign key (PageId) references
    Pages(Id) on delete cascade on update cascade;
    
    Thanks,
    Miguel
    Friday, October 16, 2009 1:48 AM

Answers

  • Hi Miguel,

    I believe the behavior is expected and correct. You have a many-to-many relationship between Document and Pages which means that One document can have one or more pages and one page can have one or more documents . So deleting a page entry when you delete a document does not makes sense because that page can be associated to some other documents also (at least your db structure says this) and this may lead to invalid foreign keys in your database.

    Are you sure there is no mistake in your db schema design and you need a Many-Many relationship here?

    Also, it would make sense if you want to delete a Page when ALL the related documents are deleted. But AFAIK you will have to manually do it in your application’s business logic or use a table trigger.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Yichun_Feng Thursday, October 22, 2009 1:14 AM
    Monday, October 19, 2009 6:27 AM