multiple on delete cascade of foreign key in one table

Posted by S_tarai under Sql Server on 2/2/2012 | Points: 10 | Views : 9043 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have two tables,user_info and follow with Primary-foregin relationship
like this:

create table user_info
(
ID bibigint gint identity(1,1) not null primary key
Name varchar(30),
.....
.....
)

create table follow
(
following bigint not null references user_info(ID) on delete cascade,
follower bigint not null references user_info(ID) on delete cascade,
CONSTRAINT uk_follow UNIQUE (following,follower)
)

Output:
First table is sucesfully created
but when i run to create follow table then error is thrown to me

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK__foll__follo__0AF29B96' on table 'follow' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Note: But i want to implement on delete cascade in both columns, following and follower.

My actual requirement:
when any record is deleted from user_info table then that user should deleted from follow table based on ID column, that ID may be exist in following or follower column.

i want to delete all records of follow table when any id is related parent table is deleted from parent table

Thanks
Surendra Tarai
tarai.surendra16@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pradeepkumar417 on: 2/2/2012 [Member] Starter | Points: 25

0	Hi, If you want create a foreign key (with a cascade delete) with more than one field then you have to create the tables like below: CREATE TABLE table1 ( table1_id numeric(10) not null, table1_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT table1_pk PRIMARY KEY (table1_id, table1_name) ); CREATE TABLE table2 ( table2_id numeric(10) not null, table1_id numeric(10) not null, table1_name varchar2(50) not null, CONSTRAINT fk_table1_comp FOREIGN KEY (table1_id, table1_name) REFERENCES table1(table1_id, table1_name) ON DELETE CASCADE ); Thanks & Regards, Software Engineer, Pradeep Kumar S_tarai, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

If you want create a foreign key (with a cascade delete) with more than one field then you have to create the tables like below:

CREATE TABLE table1
( table1_id numeric(10) not null,
table1_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT table1_pk PRIMARY KEY (table1_id, table1_name)
);

CREATE TABLE table2
( table2_id numeric(10) not null,
table1_id numeric(10) not null,
table1_name varchar2(50) not null,
CONSTRAINT fk_table1_comp
FOREIGN KEY (table1_id, table1_name)
REFERENCES table1(table1_id, table1_name)
ON DELETE CASCADE
);

Thanks & Regards,
Software Engineer,
Pradeep Kumar

S_tarai, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: S_tarai on: 2/2/2012 [Member] Starter | Points: 25

0	Dear Pradeepkumar, i have only one primary key with one column, not two columns, so please see my table defination please Thanks Surendra Tarai tarai.surendra16@gmail.com S_tarai, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: PandianS on: 2/2/2012 [Member] [MVP] Silver | Points: 25

0	Hi In your case, You can not force "Cyclic Cascade" here... So, You can go for TRIGGER (Specifically "instead of") create table user_info ( ID bigint identity(1,1) not null primary key , Name varchar(30) ) create table follow ( following bigint not null foreign key references user_info(ID), follower bigint not null foreign key references user_info(ID) CONSTRAINT uk_follow UNIQUE (following,follower) ) Create Trigger Trg_DeleteChild_Parent_Data On user_info instead of Delete as Begin Delete f from follow f, Deleted D Where f.following = D.ID Delete f from follow f, Deleted D Where f.follower = D.ID Delete UI from user_info UI, Deleted D Where UI.ID = D.ID End insert user_info (Name) values('pandian') insert user_info (Name) values('sql server') go insert follow values(1,1) insert follow values(1,2) insert follow values(2,1) insert follow values(2,2) go delete user_info where ID = 2 go Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions S_tarai, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

In your case, You can not force "Cyclic Cascade" here... So,

You can go for TRIGGER (Specifically "instead of")

create table user_info 

( 

ID bigint identity(1,1) not null primary key ,

Name varchar(30)

)

create table follow 

( 

following bigint not null foreign key references user_info(ID), 

follower bigint not null foreign key references user_info(ID)

CONSTRAINT uk_follow UNIQUE (following,follower) 

)

Create Trigger Trg_DeleteChild_Parent_Data On user_info instead of Delete

as

Begin



	Delete f from follow f, Deleted D

	Where f.following = D.ID



	Delete f from follow f, Deleted D

	Where f.follower = D.ID



	Delete UI from user_info UI, Deleted D

	Where UI.ID = D.ID

End

insert user_info (Name) values('pandian')

insert user_info (Name) values('sql server')

go

insert follow values(1,1)

insert follow values(1,2)

insert follow values(2,1)

insert follow values(2,2)

go

delete user_info where ID = 2

go

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

S_tarai, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts