Debian Codegraph @

www/stricttables.html

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