sql: How to avoid same id in table row

Question

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)
);

How to avoid inserting same follower_id and followee_id in one row?

example:

CORRECT:

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

INCORRECT:

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

You could add a constraint checking the values are different — Robert Kock, Sep 07 '18 at 12:56
To put this constraint, you could either handle this in application code, OR, write a trigger ON INSERT/UPDATE. But triggers are dangerous when you have lots of concurrent sessions, due to locking issues. — Madhur Bhaiya, Sep 07 '18 at 13:00
You can use a trigger to achieve what you want. There's an example here: http://mysqlserverteam.com/new-and-old-ways-to-emulate-check-constraints-domain/ — user1717259, Sep 07 '18 at 13:02
"But triggers are dangerous when you have lots of concurrent sessions, due to locking issues." @MadhurBhaiya the locking depens on what you are using in the trigger code https://dev.mysql.com/doc/refman/8.0/en/lock-tables-and-triggers.html .. In this context the triggers wil not lock anything. — Raymond Nijland, Sep 07 '18 at 13:10
@RaymondNijland by locking I mean - deadlock issues. Triggers make debugging very hard also. Many times, we would be looking at application code for hours and hours, figuring out what is causing the value change; forgetting about triggers. — Madhur Bhaiya, Sep 07 '18 at 13:14
"Triggers make debugging very hard also. Many times, we would be looking at application code for hours and hours, figuring out what is causing the value change; forgetting about triggers." Yes @MadhurBhaiya code comments to the rescue `/* this insert also triggers the MySQL database trigger trigger */ $db->insert(array(...))` most simple solution and or copy the complete SQL trigger code as code comment in your code so you can directly see what the trigger is "doing" — Raymond Nijland, Sep 07 '18 at 13:19

Prahalad Gaggar · Accepted Answer · 2018-09-07T13:23:38.823

2

Create a 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;

Creating before update as well

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;

edited Sep 07 '18 at 13:23

answered Sep 07 '18 at 13:10

Prahalad Gaggar

11,389
16
53
71

For MySQL versions between 5.1 and 5.4 you can replace `SIGNAL SQLSTATE` with something like `UPDATE 'Error: follower_id and followee_id can not be same.'` to simulate `SIGNAL SQLSTATE '45000'` and getting a readable error `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Error: follower_id and followee_id can not be same.'' at line 1` – Raymond Nijland Sep 07 '18 at 13:25

Terry Carmen · Answer 2 · 2018-09-07T15:08:02.037

0

Your only options are

Don't add the data you don't want
Add all the data and filter out the data you don't want in the SELECT statement. (WHERE follower_id <> followee_id)
Use a trigger to check the data before insert. If it's a quick check on two numeric columns it probably won't cause much trouble.

edited Sep 07 '18 at 15:08

answered Sep 07 '18 at 13:04

Terry Carmen

3,720
1
16
32

score 0 · Answer 3 · answered Sep 07 '18 at 14:01

0

If its only in the same row, you just need a CONSTRAINT stating that follower_id <> followee_id

See https://www.w3schools.com/sql/sql_constraints.asp

answered Sep 07 '18 at 14:01

Miguel Cruz

11
3

CHECK constraints are currently non-functional in MYSQL. – Terry Carmen Sep 07 '18 at 15:09

sql: How to avoid same id in table row

3 Answers3