How to prevent creation of records where the value of two fields is the same?

Question

I have the following table.

CREATE TABLE people(
first_name VARCHAR(128) NOT NULL,
nick_name VARCHAR(128) NULL
)

I would like to prevent people from having their nickname be the same as their firstname if they attempt that insertion. I do not want to create an index on either of the columns just a rule to prevent the insertion of records where the first_name and nick_name are the same.

Is there a way to create a rule to prevent insertion of records where the first_name would equal the nick_name?

[This is not currently possible.](http://stackoverflow.com/q/2115497/501250) Consider enforcing this at the application layer until MySQL implements CHECK constraints, or use a different database engine. (PostgreSQL is currently my favorite.) — cdhowie, Apr 26 '13 at 21:16
@cdhowie Out of curiosity, what about that ["trigger"](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working#2115641) answer? Does that not work? — showdev, Apr 26 '13 at 21:20
I'm not a MySQL specialist, however, using a trigger seems to be a solution, e.g.: http://blog.christosoft.de/2012/08/mysql-check-constraint/; — Trinimon, Apr 26 '13 at 21:21
@showdev That could be a possible workaround, if the OP documents the hell out of his implementation. — cdhowie, Apr 26 '13 at 21:26
http://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint — didierc, Apr 26 '13 at 21:26

score 3 · Accepted Answer · answered Apr 26 '13 at 21:21

3

CREATE TRIGGER `nicknameCheck` BEFORE INSERT ON `people` FOR EACH ROW begin
  IF (new.first_name = new.nick_name) THEN
    SET new.nick_name = null;
  END IF;
END

Or you can set first_name to NULL which will cause SQL error and you can handle it and show some warning.

answered Apr 26 '13 at 21:21

JerabekJakub

5,268
4
26
33

That looks quite good; a signal might also be interesting, e.g. `SIGNAL sqlstate '45000' SET message_text = 'Nickname violation';` – Trinimon Apr 26 '13 at 21:23
Doesnt prevent the insert, my 2 values are each NOT NULL. – ekerner Jul 31 '17 at 14:23

score 0 · Answer 2 · answered Apr 26 '13 at 21:21

You only need triggers for BEFORE INSERT and BEFORE UPDATE. Let these check the values and abort the operation, if they are equal.

Caveat: On older but still widely used versions of MySQL (before 5.5 IIRC) you need to do something bad, such as read from the written table or easier read from an inexistant table/column (in order to abort).

ekerner · Answer 3 · 2017-07-31T15:01:06.080

AFTER INSERT trigger to test and remove if same ...

CREATE TABLE ek_test (
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    one INT NOT NULL,
    two INT NOT NULL
);

delimiter //
CREATE TRIGGER ek_test_one_two_differ AFTER INSERT ON ek_test 
FOR EACH ROW 
BEGIN
  IF (new.one = new.two) THEN
    DELETE FROM ek_test WHERE id = new.id;
  END IF;
END//
delimiter ;

INSERT INTO ek_test (one, two) VALUES (1, 1);

SELECT * FROM ek_test;

NOTE you will also need AFTER UPDATE trigger.

How to prevent creation of records where the value of two fields is the same?

3 Answers3

Linked

Related