Can I make sure two columns dont have the same value
No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK
constraint:
ALTER TABLE foo
ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK
CHECK (aa <> bb) ;
I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal values and simply ignore the rows by accessing the table always through a view:
CREATE VIEW foo_correct AS
SELECT id, aa, bb
FROM foo
WHERE aa <> bb ;
Alternatively, you can restrict Insert and Update operations through (stored) procedures that take care of the constraint and not allow data to be inserted (or changed) that do not satisfy it.
MySQL does not support CHECK
contrsaints directly, though if you have a recent enough version it supports triggers and error raising via SIGNAL
, so you could define BEFORE INSERT
and BEFORE UPDATE
triggers that check the data and raise errors if the intended constraint is not satisfied.
This will be less efficient than native support for check constraints of course, so if you are performing a high volume of writes to that structure be sure to analys the performance difference caused by the trigger in case it harms your application too much.
Good News!!
Mysql supports CHECK CONSTRAINT
https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html