By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(
a int not null,
b int not null
)
create trigger before_insert_t
before insert on t
for each row
begin
if (exists(
select 1
from t
where (least(a, b), greatest(a, b)) = (least(new.a, new.b), greatest(new.a, new.b))
)) then
signal sqlstate value '45000' set message_text = 'cannot insert duplicate pair';
end if;
end
show warnings
| Level | Code | Message |
|---|
insert into t values (1, 2)
insert into t values (1, 2)
cannot insert duplicate pair
insert into t values (2, 1)
cannot insert duplicate pair
truncate table t;
DROP TRIGGER before_insert_t
alter table t add constraint unique (a, b)
Records: 0 Duplicates: 0 Warnings: 0
alter table t add constraint check (a < b)
Records: 0 Duplicates: 0 Warnings: 0
insert into t values (1, 2)
insert into t values (1, 2)
Duplicate entry '1-2' for key 't.a'
insert into t values (2, 1)
Check constraint 't_chk_1' is violated.