dimanche 2 août 2015

How to express check for unique pair of values in sqlite3

I'm in need of expressing pathing data in sqlite3 database. I have graph and I need to save shortest paths between some vertexes. Now, since my graph is bidirectional, shortest path between A and B will be the same as path between B and A.

My current db structure looks likes this:

create table shortest (
    id1 long,
    id2 long,
    ...
);

Now, how can I express constraint that (id1, id2) must be unique regardless the order? So if I try to insert insert into shortest values (2, 1, ...) and there already exists record with (1, 2, ...), the insert will fail?

I cannot figure this out :/

NOTE: changing db structure is ok if it's needed, this is a new project.

Aucun commentaire:

Enregistrer un commentaire