SQL (MySQL): Best way to force different values two columns of a row?

Question

The following table is given:

CREATE TABLE `tbl_termsynonyms` (
 `ts_term1Id` int(10) unsigned NOT NULL,
 `ts_term2Id` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ts_term1Id`,`ts_term2Id`),
 KEY `fk_tbl_termSynonyms_tbl_term_t_id2` (`ts_term2Id`),
 CONSTRAINT `fk_tbl_termSynonyms_tbl_term_t_id1` FOREIGN KEY (`ts_term1Id`) REFERENCES `tbl_term` (`t_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `fk_tbl_termSynonyms_tbl_term_t_id2` FOREIGN KEY (`ts_term2Id`) REFERENCES `tbl_term` (`t_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

As you can see this table is for referencing synonyms in a tbl_term table.

Example: The table tbl_term contains the words "fun" and "joke" with the ids 34 and 71, those IDs are saved together as a row.

Now I want to prevent rows where a term is referencing itself like:

71 - 71
31 - 31

Is there an (SQL way) to achieve this easily?

Answer 1

There is an easy way to do this in SQL:

alter table tbl_termsynonyms add constraint chk_ts_term1Id_ts_term2Id check (ts_term1Id <> ts_term2Id);

Unfortunately, MySQL ignores check constraints, so this doesn't work in MySQL.

Alas, your only choice is to use a trigger.

Presumably, you can have cycles in your data -- so 3 is a synonym of 4 and 4 is a synonym of 3. If that is the case, I don't see the real damage of having terms being synonyms of themselves.

You could just filter them out when you want to query the table:

create view vw_termsynonyms as
    select ts.*
    from tbl_termsynonyms ts
    where ts_term1Id <> ts_term2Id;