1

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:

Community
  • 1
  • 1
Vijay Chavda
  • 826
  • 2
  • 15
  • 34

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You forgot the `references` parts, or am I missing something? – Mike Nakis Oct 01 '16 at 14:24
  • @GordonLinoff I tried the cascading foreign keys, but I ended up with cyclic-redundancies. I'll try it your way and see :) – Vijay Chavda Oct 01 '16 at 15:45
  • @GordonLinoff It sure worked, thanks! I had messed up with the relationships and got stuck with cyclic paths.. its done now.. – Vijay Chavda Oct 01 '16 at 16:29
  • @VijayChavda . . . Glad I could help. Foreign key relationships can get confusing, especially when you are trying to manage multiple relationships. – Gordon Linoff Oct 01 '16 at 16:39
  • @GordonLinoff Yeah, my actual schema is pretty complex. I have like, 12 tables linked to my User table, and the design is getting really crazy!! – Vijay Chavda Oct 01 '16 at 17:05
1

One way is to add isdeleted bit, changed datetime columns to each table and use triggers to update column values on delete. In that case you will keep history of your votes, posts and users.

Or just on delete triggers.

Or to use cascade relationship as Gordon Linoff posted.

gofr1
  • 15,741
  • 11
  • 42
  • 52