Can i delete user's questions and answers if i delete the user SQL

Question

I have user table questions table and answers table on SQL. I want to add foreign key with on delete cascade on questions user_ID foreign key and answers user_ID foreign key that when i delete a user all his questions and answers are also deleted. But SQL says:

Introducing FOREIGN KEY constraint 'FK__questions__usID__1BC821DD' on table 'questions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

How can i do that.

I did this:alter table questions add constraint user_question_id_fkey foreign key (usID) references users (uID) on delete cascade; but when i tried the same for answer table it returned the error — Armend Ceka, Jan 18 '20 at 22:52
SQL Server does not support this kind of cascade delete. You will need to either use an SP to handle this or a trigger. — Thom A, Jan 18 '20 at 22:53
Does this answer your question? [Foreign key constraint may cause cycles or multiple cascade paths?](https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) — SMor, Jan 18 '20 at 23:17

score 0 · Accepted Answer · answered Jan 18 '20 at 23:05

You want a foreign key on the questions (resp: answers) table that references the users table and that deletes all questions (resp: answers) of a user when it is removed from the users table.

This should work as per design:

alter table answers add constraint answers_users 
    foreign key(user_id)
    references users(user_id)
    on delete cascade
;

alter table questions add constraint questions_users 
    foreign key(user_id)
    references users(user_id)
    on delete cascade
;

Consider this demo:

-- create the tables
create table users (user_id int primary key);
create table questions(question_id int primary key, user_id int);
create table answers(answer_id int primary key, user_id int);

-- add the constraints
alter table answers add constraint answers_users 
    foreign key(user_id)
    references users(user_id)
    on delete cascade
;
alter table questions add constraint questions_users 
    foreign key(user_id)
    references users(user_id)
    on delete cascade
;

-- insert a few records - we have two users: 1 and 2
insert into users values (1), (2);
insert into questions values(1, 1), (2, 2);
insert into answers values(1, 1), (2, 2);

-- now delete user 1
delete from users where user_id = 1;

-- the corresponding record was removed from "questions"
select * from questions;

question_id | user_id
----------: | ------:
          2 |       2

-- ... and it was removed from "answers" as well
select * from answers;

answer_id | user_id
--------: | ------:
        2 |       2

It worked on a new database but not in my current database. Anyways thanks for the help — Armend Ceka, Jan 18 '20 at 23:35

Can i delete user's questions and answers if i delete the user SQL

1 Answers1