MySQL alter two column for same foreign key

Question

I have a table called user and the primary key is user_id.

I have another table called follows. This table is for storing which user follow which user(it is something like twitter follow function).

This is my follow table.

CREATE TABLE `follows` (
  `id` int(11) NOT NULL,
  `orginal_user_id` int(11) NOT NULL,
  `follow_user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `follows`
  ADD PRIMARY KEY (`id`);

So, how can I alter this table to set both orginal_user_id and follow_user_id as a foreign key of user_id of user table...

If a user is deleted from the user table, I want to automatically delete rows in follows table either that user id appears on an orginal_user_id column or follow_user_id column.

Answer 1

You may use cascading delete constraints in your table:

CREATE TABLE follows (
    id int(11) NOT NULL PRIMARY KEY,
    orginal_user_id int(11) NOT NULL,
    follow_user_id  int(11) NOT NULL,
    CONSTRAINT fk_original_user FOREIGN KEY (orginal_user_id)
        REFERENCES user(id) ON DELETE CASCADE,
    CONSTRAINT fk_follow_user FOREIGN KEY (follow_user_id)
        REFERENCES user(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;