SQL Server - Cyclic Cascade Path

Question

Lets say I have two tables - User and Post

I introduced a customized Join table Vote to have a many-to-many relationship between User and Post.

The tables have following structure:

• User (UseId, Name)
• Post (PostId, UserId, Content)
• Vote(Id, UserId, PostId, Value)

Notes:

• The emphasized columns of each table is a PK.
• The UserId in Post is a FK to User table.
• The UserId and PostId columns in Vote table are FK to the respective tables.
• Other columns like Value, Content, Name, etc. are varchar.

---

Considering the above design is appropriate (if not, suggestions are welcomed :) .....

What I want is:

1. If a row in Post Table is deleted, the related row in Vote should also be deleted.
2. If a row in User Table is deleted, the related row in Vote should also be deleted.
3. If a row in User Table is deleted, the related row's UserId column in Post should be set to NULL.

Can I achieve such kind of relationships, without any Cyclic-Redundancy? If yes, How?

---

UPDATE:

Check out this awesome answer if you too have faced multiple cascade paths:

Answer 1

You are looking for cascading foreign key relationships. For the first two:

alter table vote
    add constraint fk_vote_user
        foreign key (userid) references user(userid) on delete cascade;

alter table vote
    add constraint fk_vote_post
        foreign key (postid) references post(postid) on delete cascade;

For the third:

alter table post
    add constraint fk_post_user
        foreign key (userid) references user(userid) on delete set null;

These are explained in the documentation.