Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Delete Cascade Problem

Author  Topic 

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-10-15 : 20:17:17
Hello,

I have the following tables:


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;


When I delete a Document its DocumentsPages are deleted but the Pages
records not.

What am I missing?

Thanks,
Miguel

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-16 : 02:14:14
You added two foreign keys(FK_DocumentsPages_Documents,FK_DocumentsPages_Pages) to Table DocumentsPages for the two columns DocumentId and PageId respectively. But there are no foreign keys on dbo.Pages Table and hence no cascade delete.If you delete any records from Documents or Pages the associated child records will be deleted from DocumentsPages. But when you delete from Documents no record will be deleted from Pages and vice versa (beacuase there is no primary key-foreign key relations between these two tables.)

If you want the pages to be deleted, when you delete the Document, the pages should be a subset of document.You need to include a foregn key in Pages table that references the Documents(id). But I think the documents and pages are independent each other in your case.
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-16 : 02:15:10
there is no direct relation between Document and Pages tables like you have for Document and DocumentPages tables
SIMPLY: according to your defined relations (1) When you will delete record from Document table, related record will be deleted from DocumentPages and (2) When you will delete record from Pages table related record will be deleted from DocumentPages.
Go to Top of Page

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-10-16 : 07:45:22
I don't think that having a Pages related to Documents makes much sense in this case.

How is this deletion usually made?

Maybe a trigger? (Could you give me an example?)

Thank You,
Miguel
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-16 : 15:33:01
trigger is best choice, for ur case
Go to Top of Page
   

- Advertisement -