I think I can get away with 31 bits (I'll have to add some error checking just in case).
I like this idea as it collapses into a single field that is easily indexed. I'll try doing this the typical way with a query first and then compare with this approach (which I expect should be notably faster). I'll post the results when I'm done. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________________ From: [email protected] [[email protected]] on behalf of Jim Wilcoxson [[email protected]] Sent: Wednesday, February 09, 2011 1:00 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Bi-directional unique 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) <[email protected]>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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
