strange foreign keys error

Question

strange foreign keys error

nimdil

Say Hey Kid

Points: 676
More actions
September 3, 2009 at 5:49 am

#225670

Hi.
I wanted to create table describing hierarchy between various groups in our system. The table:
create table group_2_group (
sup_group int not null,
sub_group int not null,
primary key CLUSTERED (sup_group, sub_group),
foreign key (sup_group) references groups (group_id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key (sub_group) references groups (group_id) ON DELETE CASCADE ON UPDATE CASCADE
)
surprisingly returned error.
Introducing FOREIGN KEY constraint 'FK__rys_group__sub_g__1BC821DD' on table 'rys_group_2_group' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In this particular situation it does not make much sense - how can i work around it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You've got two foreign keys that are going to try to cascade an update or delete on the same table... It's a circular reference.

Since you're on 2008, why don't you use the new hiearchy data type?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

nimdil Say Hey Kid Points: 676 More actions · Answer 2

HierarchyID is good for defining trees but actually it is incredibly unpleasent when it comes to rearrange the tree. And hierarchy here is not a tree - it's just DAG.

And no - it's not circular reference as far as I understand circular reference. No table is referencing this one. I just want to delete records from the table in both possible situations.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think only one of the two foreign keys should have the cascade options; that would resolve your circular reference issue, right?

the second column should reference, but not delete related data?

create table group_2_group (

sup_group int not null,

sub_group int not null,

primary key CLUSTERED (sup_group, sub_group),

foreign key (sup_group) references groups (group_id) ON DELETE CASCADE ON UPDATE CASCADE,

foreign key (sub_group) references groups (group_id)

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nimdil Say Hey Kid Points: 676 More actions · Answer 4

Of course this will make my SQL Statement valid but the problem is that I have to put additional trigger on the table.

The use -case scenario is like

INSERT INTO groups (group_id, group_name) VALUES (1,'LEADERS');

INSERT INTO groups (group_id, group_name) VALUES (2,'WORKERS');

INSERT INTO group_2_group (sub_group, sup_group) VALUES (2,1);

DELETE FROM groups WHERE group_id = 2

and here - with only following foreign key:

( sup_group ) references groups (group_id) on delete cascade

I will have incomplete structure in db.