I'm now working on a new MySQL database (interface PHPmyadmin) for my personal project and I don't know how to avoid two columns on a same table to have the same value. So, i want that we can't insert same values for 2 differents columns in one record.
id name firstname
1 john Marry => will be possible
2 Marry John => will be possible
3 John John => won't be possible
4 test test => won't be possible
I want that two columns in same record can't have the same value
Best How To :
Normally you would use a CHECK constraint for that. Bu MySQL does currently not support them. It accepts the input but ignores them.
But you can use a trigger that cancels the insertion/update on certain conditions like this
CREATE TRIGGER check_trigger BEFORE INSERT ON your_table
FOR EACH ROW
IF (NEW.name = NEW.firstname) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'same names error. Insertion canceled';
You also need a trigger for updates doing the same.