Jim C. Nasby wrote:
On Thu, Jan 05, 2006 at 01:39:02PM -0600, Kurt Welgehausen wrote:
create trigger EnumTrg before insert on MainTbl for each row
when (select count(*) from EnumVals where val = new.EnumCol) = 0 begin
select raise(rollback, 'foreign-key violation: MainTbl.EnumCol');
end;
Wouldn't that be a lot more efficient with some kind of EXISTS test
rather than a count(*)?
No. Because the count is at most 1 and therefore its cheaper than as exists,
because no additional nested query is needed.
However if the count(*) would be big EXISTS or LIMIT would make sense:
select count(*) where EXISTS (select * from TABLE where ...)
or
select count(*) from (select * from TABLE where ... LIMIT 1)
Michael
