Microblog table relationship with foreign key, trouble with followers?

I'm having a small problem thinking a small microblog system i'm doing for exercise. I have three tables:

users
  id
  开发者_如何学JAVAusername

tweets
  id
  tweet

followers
  id_user
  id_following

How do i make the relationship for followers? id_user and id_following are both PKs that relate to the same table?

Here's my query:

CREATE TABLE tweets (
  tweet_id INT NOT NULL AUTO_INCREMENT,
  tweet VARCHAR(140) NOT NULL,
  PRIMARY KEY (tweet_id)
) ENGINE=INNODB;


CREATE TABLE users (
  user_id INT NOT NULL AUTO_INCREMENT,
  user VARCHAR(255) NOT NULL,
  password VARCHAR(40) NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE user_tweets (
  id INT NOT NULL AUTO_INCREMENT,
  id_user INT NOT NULL,
  id_tweet INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (id_tweet)
    REFERENCES tweets(tweeth_id)
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  FOREIGN KEY (id_user)
    REFERENCES users(user_id)) ENGINE=INNODB;

CREATE TABLE followers (
  id_user INT NOT NULL REFERENCES users (user_id),
  id_following INT NOT NULL REFERENCES users (user_id),
  PRIMARY KEY (id_user, id_following)
) ENGINE=INNODB;

Something along these lines?

create table followers (
  id_user integer not null references users (id),
  id_following integer not null references users (id),
  primary key (id_user, id_following)
);

If MySQL supported CHECK constraints, you'd also CHECK (id_user <> id_following). But it doesn't. You'd have to use a trigger instead, but it's simpler (and probably not a problem for a Twitter clone) to just let people follow themselves if they want to. I think most of them will quickly tire of following themselves.

The CHECK clause is parsed but ignored by all storage engines.