1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>STRICT Tables</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
STRICT Tables
</div>
</div>
<h1 id="introduction"><span>1. </span>Introduction</h1>
<p>SQLite strives to be flexible regarding the datatype of
the content that it stores. For example, if a table column has a type of
"INTEGER", then SQLite tries to convert anything inserted into that column
into an integer. So an attempt to insert the string '123' results
in an integer 123 being inserted. 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.
See the <a href="datatype3.html">Datatypes In SQLite</a> document for additional information.
</p><p>Some developers <a href="flextypegood.html">appreciate the freedom</a> that SQLite's flexible typing
rules provide and use that freedom to advantage.
But other developers are aghast at SQLite's
flagrant rule-breaking and prefer the traditional rigid type
system found in all other SQL database engines and in the
SQL standard. For this latter group, SQLite supports a strict typing
mode, as of version 3.37.0 (2021-11-27), that is enabled
separately for each table.
</p><h1 id="strict_tables"><span>2. </span>STRICT Tables</h1>
<p>In a <a href="lang_createtable.html">CREATE TABLE</a> statement, if the "STRICT" table-option keyword is
added to the end, after the closing ")", then strict typing rules apply
to that table.
The STRICT keyword causes the following differences:
</p><ol>
<li><p>
Every column definition must specify a datatype for that column.
The freedom to specify a column without a datatype is removed.
</p></li><li><p>
The datatype must be one of following:
</p><ul>
<li> INT
</li><li> INTEGER
</li><li> REAL
</li><li> TEXT
</li><li> BLOB
</li><li> ANY
</li></ul>
<p>No other datatype names are allowed, though new types might be added in
future releases of SQLite.
</p></li><li><p>
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.
SQLite attempts to coerce the data into the appropriate type using the usual
affinity rules, as PostgreSQL, MySQL, SQL Server,
and Oracle all do. If the value cannot be
losslessly converted in the specified datatype, then an
SQLITE_CONSTRAINT_DATATYPE error is raised.
</p></li><li><p>
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.
</p></li><li><p>
Columns that are part of the PRIMARY KEY are implicitly NOT NULL.
However, even though the PRIMARY KEY has an implicit NOT NULL constraint,
when a NULL value is inserted into an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column, the
NULL is automatically converted into a unique integer, using the same
rules for <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> on ordinary, non-strict tables.
</p></li><li><p>
The <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> and <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> commands check the
type of the content of all columns in STRICT tables and show errors if
anything is amiss.
</p></li></ol>
<p>
Everything else about a STRICT table works the same as it does in an
ordinary non-strict table:
</p><ul>
<li> <a href="lang_createtable.html#ckconst">CHECK constraints</a> work the same.
</li><li> <a href="lang_createtable.html#notnullconst">NOT NULL constraints</a> work the same.
</li><li> <a href="foreignkeys.html">FOREIGN KEY constraints</a> work the same.
</li><li> <a href="lang_createtable.html#uniqueconst">UNIQUE constraints</a> work the same.
</li><li> <a href="lang_createtable.html#dfltval">DEFAULT clauses</a> work the same.
</li><li> <a href="lang_createtable.html#collateclause">COLLATE clauses</a> work the same.
</li><li> <a href="gencol.html">Generated columns</a> work the same.
</li><li> <a href="lang_conflict.html">ON CONFLICT clauses</a> work the same.
</li><li> <a href="lang_createindex.html">Indexes</a> work the same.
</li><li> <a href="autoinc.html">AUTOINCREMENT</a> works the same.
</li><li> An <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column is an alias for the <a href="lang_createtable.html#rowid">rowid</a>, but an
INT PRIMARY KEY column is not.
</li><li> The <a href="fileformat2.html">on-disk format</a> for the <a href="fileformat2.html##sqltab">table data</a> is the same.
</li></ul>
<h1 id="the_any_datatype"><span>3. </span>The ANY datatype</h1>
<p>The ability to host any type of data in a single column has proven to
be remarkably useful over the years. In order to continue supporting this
ability, even in STRICT tables, the new ANY datatype name is introduced.
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. As far as we know, SQLite is the only SQL database engine
that supports this advanced capability.
</p><p>The behavior of ANY is slightly different in a
STRICT table versus an ordinary non-strict table. In a STRICT table,
a column of type ANY always preserves the data exactly as it is received.
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.
For example:
</p><center>
<table border="1">
<tr><th>STRICT</th><th>ordinary non-strict
</th></tr><tr><td><pre>CREATE TABLE t1(a ANY) STRICT;
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: text '000123'</pre>
</td><td><pre>
CREATE TABLE t1(a ANY);
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: integer 123</pre>
</td></tr></table>
</center>
<h1 id="backwards_compatibility"><span>4. </span>Backwards Compatibility</h1>
<p>The STRICT keyword at the end of a CREATE TABLE statement is only
recognized by SQLite version 3.37.0 (2021-11-27) and later. If
you try to open a database containing the STRICT keyword in an earlier
version of SQLite, it will not recognize the keyword and will report
an error (except as noted below). But apart from the extra STRICT keyword,
the underlying <a href="fileformat2.html">file format</a> of the database is identical.
</p><p>Thus, in general, a database file that contains one or more STRICT
tables can only be read and written by SQLite version 3.37.0 or later.
However, a database created by SQLite 3.37.0 or later can still be
read and written by earlier versions of SQLite, going all the way back
to version 3.0.0 (2004-06-18) as long as the database does not contain
any STRICT tables or other features that were introduced after the older
version of SQLite.
</p><p>The STRICT keyword may still be used as an identifier.
(It is only treated as a keyword in a certain part of the syntax,
and sqlite3_keyword_check(..) does not recognize it as a regular keyword.)
</p><h2 id="accessing_strict_tables_in_earlier_versions_of_sqlite"><span>4.1. </span>Accessing STRICT tables in earlier versions of SQLite</h2>
<p>Because of a quirk in the SQL language parser, versions of SQLite prior
to 3.37.0 can still read and write STRICT tables if they set
"<a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a>" immediately after opening the database
file, prior to doing anything else that requires knowing the schema.
One of the features of PRAGMA writable_schema=ON is that it disables
errors in the schema parser. This is intentional, because a big reason for
having PRAGMA writable_schema=ON is to facilitate recovery of database files
with corrupt schemas. 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." Hence, the STRICT
keyword is effectively ignored. Because nothing else about the file
format changes for STRICT tables, everything else will work normally.
Of course, rigid type enforcement will not occur because the earlier
versions of SQLite do not know how to do that.
</p><p>The <a href="cli.html#dump">.dump</a> command in the <a href="cli.html">CLI</a> sets <a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a>, because
.dump is designed to extract as much content as it can even from a corrupt
database file. Hence, if you are using an older version of SQLite and
you open a database with STRICT tables in the CLI and issue the ".dump"
command before doing anything else, you will be able to read and write
to the STRICT tables without rigid type enforcement. This could, potentially,
corrupt the database, by allowing incorrect types into STRICT tables.
Reopening the database with a newer version of SQLite and running
"<a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a>" will detect and report all such corruption.
</p><h1 id="other_table_options"><span>5. </span>Other Table Options</h1>
<p>The SQLite parser accepts a comma-separated list of table options after
the final close parenthesis in a CREATE TABLE statement. As of this
writing (2021-08-23) only two options are recognized:
</p><ul>
<li> STRICT
</li><li> <a href="withoutrowid.html">WITHOUT ROWID</a>
</li></ul>
<p>If there are multiple options, they can be specified in any order.
To keep things simple, the current parser accepts duplicate options without
complaining, but that might change in future releases, so applications
should not rely on it.
</p><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/stricttables.in?m=8e4caa36c0">2022-04-18 02:55:50</a> UTC </small></i></p>
|
