CODEWITHSUNDEEP
mysqlsqldatabase
Taha Sami
Taha Sami

Reputation: 1697

How can I prevent duplicate entries over multiple columns?

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 can I prevent duplicate entries over multiple columns?

For example, I don't need anything like this

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

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

Upvotes: 0

Views: 193

Answers (1)

Barmar
Barmar

Reputation: 780714

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.

Upvotes: 2

Related Questions