sql - How can I stop the delete on a parent if a child entity that references that parent exists? -


i have following ddl using sql server 2012:

create table subject (    [subjectid] int identity (1, 1) not null,    [name] nvarchar (50) not null,    constraint [pk_subject] primary key clustered ([subjectid] asc) )             create table topic (    [topicid] int identity (1, 1) not null,    [name] nvarchar (50) not null,    [subjectid] int not null,    constraint [pk_topic] primary key clustered ([topicid] asc) ) alter table [topic] check add  constraint [fk_topicsubject]     foreign key([subjectid]) references [subject] ([subjectid])     on delete cascade  create table subtopic (    [subtopicid] int identity (1, 1) not null,    [topicid] int not null,    [name] nvarchar (4000) not null,    constraint [pk_subtopic] primary key clustered ([subtopicid] asc) )  alter table [subtopic] check add  constraint [fk_subtopictopic]     foreign key([topicid]) references [topic] ([topicid])     on delete cascade 

when try run scripts following message:

{"introducing foreign key constraint 'fk_topicsubject'  on table 'topic' may cause cycles or multiple cascade paths.  specify on delete no action or on update no action,  or modify other foreign key constraints.\r\ncould not create constraint.  see previous errors."} 

what need when person tries delete subject when there topics delete fail. if include neither delete on cascade or delete no action happen. if not how can stop delete on subject happening if there topics subject?

please refer link. has given detail explanation of error, , has suggested create trigger alternative. foreign key constraint may cause cycles or multiple cascade paths?


Comments

Popular posts from this blog

c++ - Creating new partition disk winapi -

Android Prevent Bluetooth Pairing Dialog -

VBA function to include CDATA -