Michael Scharf <[EMAIL PROTECTED]> writes:
> 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
If you have many enum values, for slightly better efficiency (since all rows
need not be scanned), you should be able to do something like this:
CREATE TRIGGER EnumTrg
BEFORE INSERT ON MainTbl
FOR EACH ROW
WHEN (SELECT 1
FROM EnumVals
WHERE val = new.EnumCol
LIMIT 1) IS NULL
BEGIN
SELECT raise(rollback, 'forign-key violation: MainTbl.EnumCol');
END;
Derrell
