sqlite> create table t(n integer not null check (typeof(n) = 'integer'), m integer not null check (typeof(m) = 'integer')); sqlite> create index t_idx on t (n, m); sqlite> explain query plan select count(*) != 0 from t t2 where t2.n = ? and t2.m = ?; 0|0|TABLE t AS t2 WITH INDEX t_idx sqlite> create trigger t_ins before insert on t begin ...> select case when (select count(*) != 0 from t t2 where t2.n = NEW.m and t2.m = NEW.n) then raise(fail, 'That relation is already in t!') end; ...> end; sqlite> insert into t values (1,2); sqlite> insert into t values (2,1); Error: That relation is already in t! sqlite> insert into t values (3,2); sqlite> insert into t values (3,1); sqlite> insert into t values (1,3); Error: That relation is already in t! sqlite>
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. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
