CODEWITHSUNDEEP
sqldatabasesqliteindexingconstraints
Wanderer
Wanderer

Reputation: 590

sqlite unique index multiple columns any combination

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

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

Upvotes: 0

Views: 60

Answers (2)

Serg
Serg

Reputation: 22811

Just require loc1<=loc2 (or >=)

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

Upvotes: 1

CL.
CL.

Reputation: 180280

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

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;

Upvotes: 1

Related Questions