How can I prevent duplicate entries over 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

For example, I don't need anything like this

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

First row = Bad
Second = Good
Third = Bad

And I want to ask, Is the followers' table looks good design?

Many people would expect an ID column to be numeric. The example data you share is consistent with that expectation. It's puzzling that you chose `char` as the type. — Jeff Holt, May 27 '22 at 16:20
@JeffHolt Yes User Id is Char like this A34zwie29143, But above is example only — Wei291, May 27 '22 at 16:33
That looks like a user name, not a user id. Would you really want to modify more than one row if a user wanted to use another name? — Jeff Holt, May 27 '22 at 16:37

Barmar · Accepted Answer · 2022-05-27T17:07:00.233

2

If you're using MySQL 8.0.16 or newer, you can use a CHECK constraint.

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're using an older version, see Can a MySQL trigger simulate a CHECK constraint? for how to emulate check constraints with triggers.

edited May 27 '22 at 17:07

answered May 27 '22 at 16:15

Barmar

669,327
51
454
560

a symbol name was expected a reserved keyword can not be used as a column name without backquotes (near CHECK) – Wei291 May 27 '22 at 16:29
The condition has to be in parentheses. – Barmar May 27 '22 at 16:34
Can I add CONSTRAINT CHECK manually in phpMyAdmin? – Wei291 May 27 '22 at 16:37
I don't use PhpMyAdmin, so I don't know how you do it there. – Barmar May 27 '22 at 16:41
See https://stackoverflow.com/questions/9499675/adding-constraints-in-phpmyadmin – Barmar May 27 '22 at 16:41
I used your command but I still can add same value in `FOLLOWER_ID` and `FOLLOWING_ID`, I used this command to again `SHOW CREATE TABLE FOLLOWERS` but the command looks without your edit, Looks like in my question above – Wei291 May 27 '22 at 16:49
What version of MySQL are you using? It's only enforced starting with 8.0.16. – Barmar May 27 '22 at 16:51
Less than that, What is the alternative? – Wei291 May 27 '22 at 16:58
You could write a BEFORE INSERT trigger that checks for it and signals an error. – Barmar May 27 '22 at 16:59
Could you leave an example please? – Wei291 May 27 '22 at 17:02
1

See https://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint for examples – Barmar May 27 '22 at 17:06

How can I prevent duplicate entries over multiple columns?

1 Answers1

Linked