Thanks Barefeet! I thought that it could be done with triggers, but I am knew and had difficult time finding good tutorials on triggers in SQLite. Thanks for your detailed code below.
I always saw SQLite as trying to be the database to beat out MS Access for the desktop user. And due to this belief I thought that referential integrity would have been the one of the first fundamental things that would have been created from version 1.0, as wells as support for the basic fundamental types of joins. I hope these things will be coming in the next release. If this is done, in my opinion SQLite will have won over MS Access!!! I am waiting for that GLORIOUS day to come!!! Palmer > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Tue, 20 May 2008 09:22:59 +1000 > Subject: Re: [sqlite] text datatype and referential integrity > > 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 _________________________________________________________________ Change the world with e-mail. Join the i’m Initiative from Microsoft. http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ChangeWorld _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
