CINXE.COM

<!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>  </head> <body> <div class=nosearch> <a href="index.html"> <img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> </a> <div></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>