On Mon, Feb 17, 2014 at 6:37 AM, Tim Streater <t...@clothears.org.uk> wrote:
> If I have a text column defined as it might be as MYCOL TEXT (that is with
> no default value), is there a way to distinguish in some row or other
> between a column into which no data has ever been entered, and a column
> that might have been set to a string, but later set to the empty string?
> (or even only ever set to the empty string, perhaps).
>
> ====
Hi, Tim,
I would think of two ways:
1) You might default the field to a value you know from your data to be
impossible. E.g.
MYCOL2 TEXT DEFAULT '#UNUSED#'
Your initial building of the table would then OMIT the field name
MYCOL2 in order to invoke the default, e.g.
INSERT INTO MyTable (MyCol1, Mycol3) VALUES ('a', 'b');
2) You might set and/or default the field to NULL. (Importantly, but as
you may already know, NULL is not like an ordinary value. It means
something like 'Value is unknown.' As a result, testing for a value equal
to NULL will ALWAYS return FALSE, for example. If this would be your
first exposure to NULL you'd want to read up on it first.)
Now if instead what you want is to:
Initialize a value to the empty string
set it to something else
set it back to the empty string
Then have sqlite "invoke a past memory of that value" then, no, sql
won't do that directly.
If that's what you need then perhaps you could
define a new boolean column to keep track of whether MyCol had ever been
used.
Update that new column directly or via a trigger.
Others on this list may have better advice.
Donald
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
