If you don't care about the order and the integers are smallish, like 31 bits or less, I'd do this:
create table t(k int primary key); i = whatever j = whatever if i < j: k = i<<32 | j else: k = j<<32 | i insert k into table To see if a pair is in the table, do the same steps and lookup k. If you do care about the order, you can add k as primary key to the table layout you mentioned with i and j. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Feb 9, 2011 at 1:12 PM, Black, Michael (IS) <michael.bla...@ngc.com>wrote: > I have a need to create a unique bi-directional relationship. > > You can think of it as pairings of people who eat dinner together. > > > > create table t(i int, j int); > > insert into t(1,2); > > insert into t(2,1); << should give an error because the pairing of 1-2 > already exists. > > insert into t(3,2); << OK > > insert into t(3,1); << OK > > insert into t(1,3); << should be error > > > > You can't guarantee that one column is less than the other so there's no > win there. > > > > Speed is of the utmost concern here so fast is really important (how many > ways can I say that???). > > > > Is there anything clever here that can be done with indexes or such? > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
