mysql - sql:如何避免在表行中使用相同的id

CREATE TABLE follows2
(
    follower_id INTEGER NOT NULL,
    followee_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

如何避免在一行中插入相同的follower_id和followee_id？

例：

正确：

INSERT INTO follows2 (follower_id, followee_id) VALUES
(1, 2);

不正确：

INSERT INTO follows2 (follower_id, followee_id) VALUES
(1, 1);

最佳答案

创建一个trigger

CREATE TRIGGER follows2valuecheckTrigger
BEFORE INSERT
ON follows2
  FOR EACH ROW BEGIN
    IF (NEW.follower_id = NEW.followee_id) THEN
      SET msg := 'Error: follower_id and followee_id can not be same.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;

在更新之前也要创建

CREATE TRIGGER follows2valuecheckTriggerOnUpdate
BEFORE UPDATE
ON follows2
  FOR EACH ROW BEGIN
    IF (NEW.follower_id = OLD.followee_id OR NEW.followee_id=OLD.follower_id) THEN
      SET msg := 'Error: follower_id and followee_id can not be same.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;

关于mysql - sql:如何避免在表行中使用相同的id，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52223048/