sqlite唯一索引多列任意组合

I have a table of locations and distances. The distance between A and B is the same as the distance between B and A. Is it possible to add a unique constraint to a table such that the columns when Trying to insert the combination 'B and A' fails if 'A and B' have already been inserted

我有一张位置和距离表。 A和B之间的距离与B和A之间的距离相同。是否可以向表中添加唯一约束,以便在尝试插入组合'B和A'时的列失败,如果'A和B'已插入

INSERT INTO locTbl VALUES ('A', B', 100.0);  //Good to insert

loc1 | loc2 | dist
-----+------+-----
A     B      100.0

INSERT INTO locTable VALUES('B','A',100.0); //Produce an error

2 个解决方案

#1

A CHECK constraint must not contain a subquery, so you have to do this with a trigger:

CHECK约束不能包含子查询,因此必须使用触发器执行此操作:

CREATE TRIGGER locTbl_loc12_unique
BEFORE INSERT ON locTbl
FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, "loc2/loc1 already exists")
    FROM locTbl
    WHERE loc1 = NEW.loc2
      AND loc2 = NEW.loc1;
END;

#2

Just require loc1<=loc2 (or >=)

只需要loc1 <= loc2(或> =)

create table locTbl (
   loc1 text,
   loc2 text,
   dist integer,
   constraint c check(loc1<=loc2)
);

#1