*** DRAFT ***
About Documentation Download License Support Purchase

SQLite Requirement Matrix Details
stricttables.html

Index Summary Markup Original

R-04253-22539-00035-22201-40709-27368-32520-29417 tcl slt th3 src

SQLite strives to be flexible regarding the datatype of the content that it stores.

th3/req1/strictreq01.test:11 

/* IMP: R-04253-22539 */
# EVIDENCE-OF: R-04253-22539 SQLite strives to be flexible regarding the
# datatype of the content that it stores.

R-03919-01344-62529-26602-44436-50756-40442-48789 tcl slt th3 src

For example, if a table column has a type of "INTEGER", then SQLite tries to convert anything inserted into that column into an integer.

th3/req1/strictreq01.test:21 

/* IMP: R-03919-01344 */
# EVIDENCE-OF: R-03919-01344 For example, if a table column has a type
# of "INTEGER", then SQLite tries to convert anything inserted into that
# column into an integer.

R-53045-50509-28028-29043-56897-58356-06470-19451 tcl slt th3 src

So an attempt to insert the string '123' results in an integer 123 being inserted.

th3/req1/strictreq01.test:25 

/* IMP: R-53045-50509 */
# EVIDENCE-OF: R-53045-50509 So an attempt to insert the string '123'
# results in an integer 123 being inserted.

R-30732-36962-32140-22588-07980-22821-60699-49282 tcl slt th3 src

But if the content cannot be losslessly converted into an integer, for example if the input is 'xyz', then the original string is inserted instead.

th3/req1/strictreq01.test:36 

/* IMP: R-30732-36962 */
# EVIDENCE-OF: R-30732-36962 But if the content cannot be losslessly
# converted into an integer, for example if the input is 'xyz', then the
# original string is inserted instead.

R-62842-02885-47183-08530-16131-50819-34642-24055 tcl slt th3 src

In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table.

th3/req1/strictreq01.test:47 

/* IMP: R-62842-02885 */
# EVIDENCE-OF: R-62842-02885 In a CREATE TABLE statement, if the
# "STRICT" table-option keyword is added to the end, after the closing
# ")", then strict typing rules apply to that table.

R-60210-08703-54697-04139-16865-40143-01079-14563 tcl slt th3 src

Every column definition must specify a datatype for that column.

th3/req1/strictreq01.test:61 

/* IMP: R-60210-08703 */
# EVIDENCE-OF: R-60210-08703 Every column definition must specify a
# datatype for that column.

R-42858-58769-58822-33299-41637-22635-42274-51914 tcl slt th3 src

The datatype must be one of following:

th3/req1/strictreq01.test:69 

/* IMP: R-42858-58769 */
# EVIDENCE-OF: R-42858-58769 The datatype must be one of following: INT
# INTEGER REAL TEXT BLOB ANY

R-43870-25292-29173-31012-48772-23978-21156-46728 tcl slt th3 src

Content inserted into the column with a datatype other than ANY must be either a NULL (assuming there is no NOT NULL constraint on the column) or the type specified.

th3/req1/strictreq01.test:86 

/* IMP: R-43870-25292 */
# EVIDENCE-OF: R-43870-25292 Content inserted into the column with a
# datatype other than ANY must be either a NULL (assuming there is no
# NOT NULL constraint on the column) or the type specified.

R-34136-00089-38264-11064-58519-06639-00881-26978 tcl slt th3 src

SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do.

th3/req1/strictreq01.test:152 

/* IMP: R-34136-00089 */
# EVIDENCE-OF: R-34136-00089 SQLite attempts to coerce the data into the
# appropriate type using the usual affinity rules, as PostgreSQL, MySQL,
# SQL Server, and Oracle all do.

R-64842-30903-04494-11389-12737-16159-45438-00930 tcl slt th3 src

If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised.

th3/req1/strictreq01.test:90 

/* IMP: R-64842-30903 */
# EVIDENCE-OF: R-64842-30903 If the value cannot be losslessly converted
# in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is
# raised.

R-01879-24612-45296-09386-15855-45568-07479-46348 tcl slt th3 src

Columns with datatype ANY can accept any kind of data (except they will reject NULL values if they have a NOT NULL constraint, of course). No type coercion occurs for a column of type ANY in a STRICT table.

th3/req1/strictreq01.test:202 

/* IMP: R-01879-24612 */
# EVIDENCE-OF: R-01879-24612 Columns with datatype ANY can accept any
# kind of data (except they will reject NULL values if they have a NOT
# NULL constraint, of course). No type coercion occurs for a column of
# type ANY in a STRICT table.

R-41881-38269-61239-25689-14129-18867-37202-03971 tcl slt th3 src

Columns that are part of the PRIMARY KEY are implicitly NOT NULL.

th3/req1/strictreq01.test:244 

/* IMP: R-41881-38269 */
# EVIDENCE-OF: R-41881-38269 Columns that are part of the PRIMARY KEY
# are implicitly NOT NULL.

R-53080-41541-45026-17384-49917-00359-25749-28353 tcl slt th3 src

However, even though the PRIMARY KEY has an implicit NOT NULL constraint, when a NULL value is inserted into an INTEGER PRIMARY KEY column, the NULL is automatically converted into a unique integer, using the same rules for INTEGER PRIMARY KEY on ordinary, non-strict tables.

th3/cov1/build08.test:49   th3/cov1/check01.test:168   th3/req1/strictreq02.test:75 

/* IMP: R-53080-41541 */
# EVIDENCE-OF: R-53080-41541 However, even though the PRIMARY KEY has an
# implicit NOT NULL constraint, when a NULL value is inserted into an
# INTEGER PRIMARY KEY column, the NULL is automatically converted into a
# unique integer, using the same rules for INTEGER PRIMARY KEY on
# ordinary, non-strict tables.

R-23696-45205-56139-29220-39057-53925-58417-44046 tcl slt th3 src

The PRAGMA integrity_check and PRAGMA quick_check commands check the type of the content of all columns in STRICT tables and show errors if anything is amiss.

th3/cov1/strict02.test:31   th3/req1/strictreq02.test:42 

/* IMP: R-23696-45205 */
# EVIDENCE-OF: R-23696-45205 The PRAGMA integrity_check and PRAGMA
# quick_check commands check the type of the content of all columns in
# STRICT tables and show errors if anything is amiss.

R-27365-32065-31789-31471-31645-57630-15850-26496 tcl slt th3 src

CHECK constraints work the same.

th3/cov1/check01.test:10   th3/cov1/check02.test:11 

/* IMP: R-27365-32065 */
# EVIDENCE-OF: R-27365-32065 CHECK constraints work the same.

R-44991-05464-53731-06799-53286-51355-12803-56046 tcl slt th3 src

NOT NULL constraints work the same.

th3/cov1/insert11.test:12 

/* IMP: R-44991-05464 */
# EVIDENCE-OF: R-44991-05464 NOT NULL constraints work the same.

R-49895-22749-28747-51875-53183-63648-10463-26840 tcl slt th3 src

FOREIGN KEY constraints work the same.

th3/cov1/fkey01.test:11 

/* IMP: R-49895-22749 */
# EVIDENCE-OF: R-49895-22749 FOREIGN KEY constraints work the same.

R-01996-58977-34276-47341-41302-00217-64799-50366 tcl slt th3 src

UNIQUE constraints work the same.

th3/req1/conflict04.test:16 

/* IMP: R-01996-58977 */
# EVIDENCE-OF: R-01996-58977 UNIQUE constraints work the same.

R-19582-19642-51467-45115-55963-42677-19295-17348 tcl slt th3 src

DEFAULT clauses work the same.

th3/cov1/vdbemem05.test:12 

/* IMP: R-19582-19642 */
# EVIDENCE-OF: R-19582-19642 DEFAULT clauses work the same.

R-17195-37504-02291-24232-45006-33803-26491-52044 tcl slt th3 src

COLLATE clauses work the same.

th3/cov1/build08.test:71 

/* IMP: R-17195-37504 */
# EVIDENCE-OF: R-17195-37504 COLLATE clauses work the same.

R-51280-49561-27340-30722-26024-36446-47802-45698 tcl slt th3 src

Generated columns work the same.

th3/cov1/gencol01.test:11   th3/cov1/gencol02.test:11   th3/cov1/gencol04.test:9 

/* IMP: R-51280-49561 */
# EVIDENCE-OF: R-51280-49561 Generated columns work the same.

R-10844-63639-16825-02392-05522-65470-37861-17171 tcl slt th3 src

ON CONFLICT clauses work the same.

th3/req1/conflict04.test:18 

/* IMP: R-10844-63639 */
# EVIDENCE-OF: R-10844-63639 ON CONFLICT clauses work the same.

R-00293-38883-59042-48634-16424-01088-43070-55397 tcl slt th3 src

Indexes work the same.

th3/cov1/index01.test:14 

/* IMP: R-00293-38883 */
# EVIDENCE-OF: R-00293-38883 Indexes work the same.

R-42053-06220-45520-18434-62327-05125-05247-18783 tcl slt th3 src

AUTOINCREMENT works the same.

th3/cov1/autoinc01.test:20   th3/cov1/autoinc02.test:11 

/* IMP: R-42053-06220 */
# EVIDENCE-OF: R-42053-06220 AUTOINCREMENT works the same.

R-48533-46594-08674-01218-43796-46472-34581-15116 tcl slt th3 src

An INTEGER PRIMARY KEY column is an alias for the rowid, but an INT PRIMARY KEY column is not.

th3/req1/strictreq02.test:92 

/* IMP: R-48533-46594 */
# EVIDENCE-OF: R-48533-46594 An INTEGER PRIMARY KEY column is an alias
# for the rowid, but an INT PRIMARY KEY column is not.

R-01977-40534-32839-34523-14867-40621-38368-53470 tcl slt th3 src

The on-disk format for the table data is the same.

th3/req1/strictreq03.test:9 

/* IMP: R-01977-40534 */
# EVIDENCE-OF: R-01977-40534 The on-disk format for the table data is
# the same.

R-34966-53902-34242-28054-07198-58256-53673-36317 tcl slt th3 src

When the datatype of a column is "ANY", that means that any kind of data - integers, floating point values, strings, or binary blobs, can be inserted into that table and its value and datatype will be preserved exactly as it is inserted.

th3/req1/strictreq01.test:207 

/* IMP: R-34966-53902 */
# EVIDENCE-OF: R-34966-53902 When the datatype of a column is "ANY",
# that means that any kind of data - integers, floating point values,
# strings, or binary blobs, can be inserted into that table and its
# value and datatype will be preserved exactly as it is inserted.

R-22361-03044-33960-50539-23472-56447-56319-64487 tcl slt th3 src

The behavior of ANY is slightly different in a STRICT table versus an ordinary non-strict table.

th3/req1/strictreq01.test:252 

/* IMP: R-22361-03044 */
# EVIDENCE-OF: R-22361-03044 The behavior of ANY is slightly different
# in a STRICT table versus an ordinary non-strict table.

R-29579-23096-56379-38525-03672-46829-05671-41323 tcl slt th3 src

In a STRICT table, a column of type ANY always preserves the data exactly as it is received.

th3/req1/strictreq01.test:212 

/* IMP: R-29579-23096 */
# EVIDENCE-OF: R-29579-23096 In a STRICT table, a column of type ANY
# always preserves the data exactly as it is received.

R-49037-06490-58039-52656-29480-55472-29071-52724 tcl slt th3 src

For an ordinary non-strict table, a column of type ANY will attempt to convert strings that look like numbers into a numeric value, and if successful will store the numeric value rather than the original string.

th3/req1/strictreq01.test:255 

/* IMP: R-49037-06490 */
# EVIDENCE-OF: R-49037-06490 For an ordinary non-strict table, a column
# of type ANY will attempt to convert strings that look like numbers
# into a numeric value, and if successful will store the numeric value
# rather than the original string.

R-59216-33891-20715-29523-18605-08261-10809-30843 tcl slt th3 src

For example:

STRICTordinary non-strict 
CREATE TABLE t1(a ANY) STRICT;
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: text '000123'
CREATE TABLE t1(a ANY);
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: integer 123

th3/req1/strictreq01.test:260 

/* IMP: R-59216-33891 */
# EVIDENCE-OF: R-59216-33891 For example: STRICTordinary non-strict
# CREATE TABLE t1(a ANY) STRICT; INSERT INTO t1 VALUES('000123'); SELECT
# typeof(a), quote(a) FROM t1; -- result: text '000123' CREATE TABLE
# t1(a ANY); INSERT INTO t1 VALUES('000123'); SELECT typeof(a), quote(a)
# FROM t1; -- result: integer 123

R-52554-26776-48251-05833-19057-37824-14862-46518 tcl slt th3 src

One of the features of PRAGMA writable_schema=ON is that it disables errors in the schema parser.

th3/req1/strictreq02.test:11 

/* IMP: R-52554-26776 */
# EVIDENCE-OF: R-52554-26776 One of the features of PRAGMA
# writable_schema=ON is that it disables errors in the schema parser.

R-58828-53895-13815-04017-42542-10483-39276-05822 tcl slt th3 src

So with writable_schema=ON, when the schema parser reaches the STRICT keyword, it says to itself "I don't know what to do with this, but everything up to this point seems like a valid table definition so I'll just use what I have."

th3/req1/strictreq02.test:14 

/* IMP: R-58828-53895 */
# EVIDENCE-OF: R-58828-53895 So with writable_schema=ON, when the schema
# parser reaches the STRICT keyword, it says to itself "I don't know
# what to do with this, but everything up to this point seems like a
# valid table definition so I'll just use what I have."

R-39255-30546-51738-15531-11992-13732-26407-08285 tcl slt th3 src

Hence, the STRICT keyword is effectively ignored.

th3/req1/strictreq02.test:19 

/* IMP: R-39255-30546 */
# EVIDENCE-OF: R-39255-30546 Hence, the STRICT keyword is effectively
# ignored.

R-38229-16527-29793-36324-34944-03946-52461-48816 tcl slt th3 src

The SQLite parser accepts a comma-separated list of table options after the final close parenthesis in a CREATE TABLE statement.

th3/req1/strictreq01.test:280 

/* IMP: R-38229-16527 */
# EVIDENCE-OF: R-38229-16527 The SQLite parser accepts a comma-separated
# list of table options after the final close parenthesis in a CREATE
# TABLE statement.

R-52777-21695-38206-28598-32008-52074-59855-17879 tcl slt th3 src

two options are recognized:

th3/req1/strictreq01.test:284 

/* IMP: R-52777-21695 */
# EVIDENCE-OF: R-52777-21695 two options are recognized: STRICT WITHOUT
# ROWID

R-00932-23802-62098-12819-52011-48588-05338-32758 tcl slt th3 src

If there are multiple options, they can be specified in any order.

th3/req1/strictreq01.test:287 

/* IMP: R-00932-23802 */
# EVIDENCE-OF: R-00932-23802 If there are multiple options, they can be
# specified in any order.

R-18141-03110-11368-12345-11263-65050-17384-54287 tcl slt th3 src

the current parser accepts duplicate options without complaining

th3/req1/strictreq01.test:302 

/* IMP: R-18141-03110 */
# EVIDENCE-OF: R-18141-03110 the current parser accepts duplicate
# options without complaining