I like this one...but I tested it a bit and the bad part is that doing a select on t requires a complete table scan. So finding these values is too slow. I did have to modify a bit to get it to work correctly...now I'll try one of the other suggestions.
sqlite> create table t (nm integer primary key); sqlite> create view tv as select (nm & 1073741823) as n, nm >> 30 as m from t; sqlite> create trigger tv_ins instead of insert on tv begin ...> select case when typeof(NEW.n) != 'integer' then raise(fail,'n is not an integer') end; ...> select case when typeof(NEW.m) != 'integer' then raise(fail,'m is not an integer') end; ...> select case when NEW.m > (1 << 30) then raise(fail, 'n is too large') end; ...> select case when NEW.n < 0 then raise(fail, 'n must be non-negative') end; ...> select case when NEW.m < 0 then raise(fail, 'm must be non-negative') end; ...> select case (select count(*) from t where (nm = (NEW.m << 30) | NEW.n) or (nm = (New.n << 3 ) | New.m)) when 1 then raise(fail, 'This relation already exists!') end; ...> insert into t select New.n | (New.m << 30); ...> end; sqlite> insert into tv (n,m) values(1,2); sqlite> insert into tv (n,m) values(1,2); Error: This relation already exists! sqlite> insert into tv(n,m) values(3,1); sqlite> insert into tv(n,m) values(3,2); sqlite> insert into tv(n,m) values(5,4); sqlite> insert into tv(n,m) values(4,5); Error: This relation already exists! sqlite> select * from tv; 3|1 1|2 3|2 5|4 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Nico Williams [n...@cryptonector.com] Sent: Wednesday, February 09, 2011 2:28 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Bi-directional unique On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams <n...@cryptonector.com> wrote: > This way an insert into t requires one lookup and update in t_idx and > one lookup and update in the table's implied rowid index. I.e., twice > the work of an insert without this additional index and trigger. Also, you get double the DB size this way. Using Jim's suggestion you get to avoid those problems: sqlite> create table t (nm integer primary key); sqlite> create view tv as select nm >> 30 as n, (nm & (1 << 30)) >> 30 as m from t; sqlite> create trigger tv_ins instead of insert on tv begin ...> select case when typeof(NEW.n) != 'integer' then raise(fail, 'n is not an integer') end; ...> select case when typeof(NEW.m) != 'integer' then raise(fail, 'm is not an integer') end; ...> select case when NEW.n > (1 << 30) then raise(fail, 'n is too large') end; ...> select case when NEW.m > (1 << 30) then raise(fail, 'm is too large') end; ...> select case when NEW.n < 0 then raise(fail, 'n must be non-negative') end; ...> select case when NEW.m < 0 then raise(fail, 'm must be non-negative') end; ...> select case (select count(*) from t where nm = NEW.n << 30 | NEW.m) when 1 then raise(fail, 'This relation already exists!') end; ...> insert into t select NEW.m << 30 | NEW.n; ...> end; sqlite> insert into tv (n, m) values (1, 2); sqlite> insert into tv (n, m) values (1, 2); Error: PRIMARY KEY must be unique sqlite> insert into tv (n, m) values (3, 1); sqlite> insert into tv (n, m) values (3, 2); sqlite> insert into tv (n, m) values (1, 3); Error: This relation already exists! sqlite> This still requires two rowid index lookups for every insert, but only one update. Nico -- _______________________________________________ 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
