When a table is created in a database, the
SQL statement for creating the table defines the affinity, or data
type, for each column in the table. Although affinity declarations
can be omitted, it’s a good idea to explicitly declare column affinity in
your
CREATE TABLE
SQL statements.
As a general rule, any object that you store in a database using
an
INSERT
statement is returned as an instance
of the same data type when you execute a
SELECT
statement.
However, the data type of the retrieved value can be different depending
on the affinity of the database column in which the value is stored.
When a value is stored in a column, if its data type doesn’t match
the column’s affinity, the database attempts to convert the value
to match the column’s affinity. For example, if a database column
is declared with
NUMERIC
affinity, the database
attempts to convert inserted data into a numeric storage class (
INTEGER
or
REAL
)
before storing the data. The database throws an error if the data
can’t be converted. According to this rule, if the String “12345”
is inserted into a
NUMERIC
column, the database
automatically converts it to the integer value 12345 before storing
it in the database. When it’s retrieved with a
SELECT
statement,
the value is returned as an instance of a numeric data type (such
as Number) rather than as a String instance.
The best way to avoid undesirable data type conversion is to
follow two rules. First, define each column with the affinity that
matches the type of data that it is intended to store. Next, only
insert values whose data type matches the defined affinity. Following
these rules provides two benefits. When you insert the data it isn’t
converted unexpectedly (possibly losing its intended meaning as
a result). In addition, when you retrieve the data it is returned
with its original data type.
For more information about the available column affinity types
and using data types in SQL statements, see the
Data type support
.
