How to prevent duplicate entry in multiple columns?

Question

CREATE TABLE `FOLLOWERS` 
(`FOLLOWER_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
`FOLLOWING_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
`FOLLOWING_IN` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

How to prevent repeated input in multiple columns?

For example, I don’t need something like this

FOLLOWER_ID	FOLLOWING_ID	FOLLOWING_IN
283	283	...
193	283	...
908	908	...

• First line = error
• Second=Good
• Third=Bad

I want to ask, does the followers table look well designed?

Answer 1

If you are using MySQL 8.0.16 or later, you can use CHECK constraints.

CREATE TABLE `FOLLOWERS` (
    `FOLLOWER_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
    `FOLLOWING_ID` char(255) COLLATE utf8_unicode_ci NOT NULL, 
    `FOLLOWING_IN` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT CHECK (FOLLOWER_ID != FOLLOWING_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

If you are using an older version, see Can MySQL triggers emulate CHECK constraints? Learn how to use triggers to simulate check constraints.