"Anabell Chan" <[EMAIL PROTECTED]>
24/03/2004 05:23 PM
Please respond to anabell
To: <[EMAIL PROTECTED]>
cc:
Subject: [sqlite] Text(3)
> Firstly, how do I define a string table field with constraints on its
size?
> For example Text(3), string of three characters. Secondly, how is it
> enforced during updates? Many thanks!
Sqlite will make you work a little to enforce contraints like this. If you
really want to, here's how:
CREATE TABLE foo (a); -- we want a to be at most three characters long
CREATE TRIGGER foo_a_insert_constraint BEFORE INSERT ON foo
BEGIN
SELECT RAISE(ABORT, "a is too long") WHERE length(NEW.a) > 3;
END;
CREATE TRIGGER foo_a_update_constraint BEFORE UPDATE OF a ON foo
BEGIN
SELECT RAISE(ABORT, "a is too long") WHERE length(NEW.a) > 3;
END;
sqlite> INSERT INTO foo VALUES("abcd");
SQL error: a is too long
sqlite> INSERT INTO foo VALUES("abc");
sqlite>
Adjust table and column names, and string length to suit. See
http://sqlite.org/lang.html#createtrigger for reference material. This
approach can be taken to check a wide variety of constraints. If you can
define a WHERE clause that can detect a problem, you can abort the
offending operation using a couple of triggers.
Does anyone have a more succinct version of the above?
Benjamin.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
