Hi Palmer,
>> Say one defines a column to be of text type, but you want only
>> 'text' to contain only alphabetic characters, no numeric
>> characters, how would one create this rule and enforce it in SQLite
>> SQL or does one have to use triggers.
>
> Perhaps something like this:
>
> create table t(x check (ltrim(x, 'ABC...Zabc...z') = ''));
>
> (with full alphabet in place of ellipsis, of course).
Personally, I'm more inclined to implement the constraint as triggers,
since you can change the trigger as needed. In contrast, altering a
constraint in the table requires dropping and recreating the whole
table and data. As far as I know there's no disadvantage in
implementing constraints in triggers, but I'd like to know if that's
false.
something like:
create table MyTable( MyTextColumn )
;
create trigger MyTriggerInsert before insert on MyTable
begin
select raise(abort, 'MyTextColumn must contain only alphabetic
characters.')
where ltrim(new.MyTextColumn,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') != '';
end
;
create trigger MyTriggerUpdate before update of MyTextColumn on MyTable
begin
select raise(abort, 'MyTextColumn must contain only alphabetic
characters.')
where ltrim(new.MyTextColumn,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') != '';
end
;
>> Another question I have is, has proper referential integrity been
>> finally established and things like full joins and other joins.
Not directly, but you can also do this via triggers.
I'd also like to know if support for foreign keys (defined in the
table) is planned for SQLite down the track.
Tom
BareFeet
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
