I have two tables,user_info and follow with Primary-foregin relationship
create table user_info
ID bibigint gint identity(1,1) not null primary key
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)
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