On 2020/01/13 12:25 AM, Tom Browder wrote:
On Sun, Jan 12, 2020 at 14:05 Keith Medcalf <kmedc...@dessus.com> wrote:
Close, but no banana. Every value has a type. A column may contain
multiple values (as in one per row)....
Thanks, Keith.
I assume that is just for SQLite, or am I wrong again?
You are not wrong. This comes up from time to time and is always quite
interesting.
As Keith explained, the Relational Model can be applied on most kinds of
data-stores. SQLite's data store happens to allow per-value typing, and
then only using "Duck typing" (https://en.wikipedia.org/wiki/Duck_typing).
While on the subject of SQLite peculiarities, there's a few to note if
you are new to SQLite, like:
- You don't need any type for a column, but untyped columns are treated
as having blob affinity, not text.
- A column declared as VARCHAR(30), or TEXT(30) or INT(11) for that
matter, will be fully accepted, but there is no actual length
constraint. You can put any length value in the column.
- NULL values are distinct from each other (in some cases) so that if A
is NULL and B is NULL, then A = B returns FALSE (0) and A <> B also
returns FALSE!. This is useful, but note that a Primary Key in SQLite
without also having the NOT NULL constraint, will allow duplicate NULL
values in the key. (the row-id alias being the exception).
- While on the point, the special type "INTEGER PRIMARY KEY" is an Alias
for the internal index (row-id) of the table, and so doesn't allow NULLs.
- While on it still... Tables don't always have row-ids, and there is no
easy check to know if it does, so unless you made the DB yourself, you
can't safely just query the row-id.
- Since typing is per value, it is not an error in SQLite for Parent and
Child Key columns in a Foreign-Key relationship to have different types
- this can have some unexpected results!
- A datetime is a Numeric type expressed as text (ISO8601) and doesn't
inherently know anything about time or time-zones. (The date-handling
functions work amazingly well though).
- A column with the type STRING will have Integer affinity.
- A spelling error in your schema, such as CREATE TABLE t(id INT, name
T3XT); will not be an error - SQLite will silently regard that column
affinity as blob.
- Quotes are more or less ambivalent... CREATE TABLE t("id" int, [name]
text, `age` [int]) is a perfectly valid schema statement, and Set a =
"John" can mean different things based on whether there is a column
named John or not.
I'm probably not remembering all of them now, but we've spent lots of
time musing about it before (forum searches will probably provide a host
of discussions, including much lobbying for a "strict" mode) and because
of all that, there's a fun feature added in SQLitespeed that does
Schema-checking and prints a list of warnings if it contains one or more
of these SQLite quirks (including misspelled types, which helps me a
lot), and on the SQLite site there's also a section on some of these
peculiarities (https://www.sqlite.org/quirks.html).
I'm hoping someone else will add the quirks which I forgot about :)
We all came to love (mostly) and often use these quirks to some
advantage, but it pays to be aware of them, especially coming from
another DB architecture where typing and the like are more rigid.
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
