Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the file format documentation with additional information about internal schema objects. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
68d26663983d22074d2eb60a6816591c |
User & Date: | drh 2012-03-29 14:14:50.574 |
Context
2012-03-31
| ||
12:10 | Fix typo in the fileformat2 document. (check-in: 912e06a446 user: drh tags: trunk) | |
2012-03-29
| ||
14:14 | Enhance the file format documentation with additional information about internal schema objects. (check-in: 68d2666398 user: drh tags: trunk) | |
2012-03-28
| ||
17:00 | Add the typeof()/length() optimization to the change log. (check-in: a2d5d784a5 user: drh tags: trunk) | |
Changes
Changes to pages/autoinc.in.
︙ | ︙ | |||
73 74 75 76 77 78 79 | has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error. </p> <p> ^SQLite keeps track of the largest ROWID that a table has ever held using | > | | | 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error. </p> <p> ^SQLite keeps track of the largest ROWID that a table has ever held using an [internal table] named "[sqlite_sequence]". ^The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. ^The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. ^But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes. </p> <p> |
︙ | ︙ |
Changes to pages/fileformat2.in.
︙ | ︙ | |||
16 17 18 19 20 21 22 | <p>During a transaction, SQLite stores additional information in a second file called the "rollback journal", or if SQLite is in [WAL mode], a write-ahead log file. If the application or host computer crashes before the transaction completes, then the rollback journal or write-ahead log contains critical state information needed to restore the main database file to a consistent state. When a rollback | | | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | <p>During a transaction, SQLite stores additional information in a second file called the "rollback journal", or if SQLite is in [WAL mode], a write-ahead log file. If the application or host computer crashes before the transaction completes, then the rollback journal or write-ahead log contains critical state information needed to restore the main database file to a consistent state. When a rollback journal or write-ahead log contains information necessary for recovering the state of the database, they are called a "hot journal" or "hot WAL file". Hot journals and WAL files are only a factor during error recovery scenarios and so are uncommon, but they are part of the state of an SQLite database and so cannot be ignored. This document defines the format of a rollback journal and the write-ahead log file, but the focus is on the main database file.</p> |
︙ | ︙ | |||
985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 | are unique.</p> <p>^There is a one-to-one mapping between rows in a table and entries in each index associated with that table. ^Corresponding rows int the index and table b-trees share the same rowid value, and contain the same value for all indexed columns.</p> <h3>2.5 Storage Of The SQL Database Schema</h3> <p>^Page 1 of a database file is the root page of a table b-tree that holds a special table named "sqlite_master" (or "sqlite_temp_master" in the case of a TEMP database) which stores the complete database schema. ^(The structure of the sqlite_master table is as if it had been created using the following SQL:</p> <blockquote><pre> CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text ); </pre></blockquote>)^ | > | | | > > > | | | | | | 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 | are unique.</p> <p>^There is a one-to-one mapping between rows in a table and entries in each index associated with that table. ^Corresponding rows int the index and table b-trees share the same rowid value, and contain the same value for all indexed columns.</p> <tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl> <h3>2.5 Storage Of The SQL Database Schema</h3> <p>^Page 1 of a database file is the root page of a table b-tree that holds a special table named "sqlite_master" (or "sqlite_temp_master" in the case of a TEMP database) which stores the complete database schema. ^(The structure of the sqlite_master table is as if it had been created using the following SQL:</p> <blockquote><pre> CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text ); </pre></blockquote>)^ <p>^The sqlite_master table contains one row for each table, index, view, and trigger (collectively "objects") in the database schema, except there is no entry for the sqlite_master table itself. ^The sqlite_master table contains entries for [internal schema objects] in addition to application- and programmer-defined objects. <p>^(The sqlite_master.type column will be one of the following text strings: 'table', 'index', 'view', or 'trigger' according to the type of object defined. The 'table' string is used for both ordinary and [virtual tables].)^</p> </p>^(The sqlite_master.name column will hold the name of the object.)^ ^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create [internal indices] with names of the form "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one with each constraint seen in the table definition.)^</p> <p>The sqlite_master.tbl_name column holds the name of a table or view that the object is associated with. ^For a table or view, the tbl_name column is a copy of the name column. ^For an index, the tbl_name is the name of the table that is indexed. ^For a trigger, the tbl_name column stores the name of the table or view that causes the trigger to fire.</p> |
︙ | ︙ | |||
1053 1054 1055 1056 1057 1058 1059 | <li>^Leading spaces are removed. <li>^All spaces following the first two keywords are converted into a single space. </ul> <p>^(The text in the sqlite_master.sql column is a copy of the original CREATE statement text that created the object, except normalized as | | > > | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 | <li>^Leading spaces are removed. <li>^All spaces following the first two keywords are converted into a single space. </ul> <p>^(The text in the sqlite_master.sql column is a copy of the original CREATE statement text that created the object, except normalized as described above and as modified by subsequent [ALTER TABLE] statements.)^ ^(The sqlite_master.sql is NULL for the [internal indices] that are automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p> <tcl>hd_fragment intschema {internal schema objects} \ {internal schema object} {internal index} {internal indices} \ {internal table} {internal tables}</tcl> <h4>2.5.1 Internal Schema Objects</h4> <p>^In addition to the tables, indices, views, and triggers created by the application and/or the developer using CREATE statements SQL, the sqlite_master table may contain zero or more entries for <i>internal schema objects</i> that are created by SQLite for its own internal use. ^The names of internal schema objects always begin with "sqlite_" and any table, index, view, or trigger whose name begins with "sqlite_" is an internal schema object. ^SQLite prohibits applications from creating objects whose names begin with "sqlite_". <p>Internal schema objects used by SQLite may include the following: <ul> <li><p>Indices with names of the form "sqlite_autoindex_TABLE_N" that are used to implement [UNIQUE] and [PRIMARY KEY] constraints on ordinary tables. <li><p>A table with the name "sqlite_sequence" that is used to keep track of the maximum historical [INTEGER PRIMARY KEY] for a table that using [AUTOINCREMENT]. <li><p>Tables with names of the form "sqlite_statN" where N is an integer. Such tables store database statistics gathered by the [ANALYZE] command and used by the query planner to help determine the best algorithm to use for each query. </ul> <p>Additional internal schema objects names, always beginning with "sqlite_", may be added to the SQLite file format in future releases. <tcl>hd_fragment seqtab {sqlite_sequence}</tcl> <h4>2.5.2 The sqlite_sequence table</h4> <p>^The sqlite_sequence table is an internal table used to help implement [AUTOINCREMENT]. ^The sqlite_sequence table is created automatically whenever any ordinary table with an AUTOINCREMENT integer primary key is created. ^Once created, the sqlite_sequence table exists in the sqlite_master table forever; it cannot be dropped. The schema for the sqlite_sequence table is: <blockquote><pre> CREATE TABLE sqlite_sequence(name,seq); </pre></blockquote> <p>^There is a single row in the sqlite_sequence table for each ordinary table that uses AUTOINCREMENT. The name of the table (as it appears in sqlite_master.name) is in the sqlite_sequence.main field and the largest [INTEGER PRIMARY KEY] ever used by that table is in the sqlite_sequence.seq field. ^New automatically generated integer primary keys for AUTOINCREMENT tables are guaranteed to be larger than the sqlite_sequence.seq field for that table. ^If the sqlite_sequence.seq field of an AUTOINCREMENT table is already at the largest integer value (9223372036854775807) then attempts to add new rows to that table with an automatically generated integer primary will fail with an [SQLITE_FULL] error. ^The sqlite_sequence.seq field is automatically updated if required when new entries are added to an AUTOINCREMENT table. ^The sqlite_sequence row for an AUTOINCREMENT table is automatically deleted when the table is dropped. ^If the sqlite_sequence row for an AUTOINCREMENT table does not exist when the AUTOINCREMENT table is updated, then a new sqlite_sequence row is created. ^If the sqlite_sequence.seq value for an AUTOINCREMENT table is manually set to something other than an integer and there is a subsequent attempt to insert the or update the AUTOINCREMENT table, then the behavior is undefined. <p>^Application code is allowed to modify the sqlite_sequence table, to add new rows, to delete rows, or to modify existing rows. ^However, application code cannot create the sqlite_sequence table if it does not already exist. ^Application code can delete all entries from the sqlite_sequence table, but application code cannot drop the sqlite_sequence table. <tcl>hd_fragment stat1tab {sqlite_stat1}</tcl> <h4>2.5.3 The sqlite_stat1 table</h4> <p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command and used to hold supplemental information about tables and indices that the query planner can use to help it find better ways of performing queries. ^Applications can update, delete from, insert into or drop the sqlite_stat1 table, but may not create or alter the sqlite_stat1 table. ^The schema of the sqlite_stat1 table is as follows: <blockquote><pre> CREATE TABLE sqlite_stat1(tbl,idx,stat); </pre></blockquote> <p>There is normally one row per index, with the index identified by the name in the sqlite_stat1.idx column. The sqlite_stat1.tbl column is the name of the table to which the index belongs. In each such row, the sqlite_stat.stat column will be a string consisting of a list of integers. The first integer in this list is the number of rows in the index and in the table. The second integer is the average number of rows in the index that have the same value in the first column of the index. The third integer is the average number of rows in the index that have the same value for the first two columns. The N-th integer (for N>1) is the average number of rows in the index which have the same value for the first N-1 columns. For a K-column index, there will be K+1 integers in the stat column. If the index is unique, then the last integer will be 1. <p>The list of integers in the stat column can optionally be followed by the keyword "unordered". The "unordered" keyword, if it is present, must be separated from the last integer by a single space. If the "unordered" keyword is present, then the query planner assumes that the index is unordered and will not use the index for a range query. <p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat column contains a single integer which is the (estimated) number of rows in the table identified by sqlite_stat1.tbl. <tcl>hd_fragment stat2tab {sqlite_stat2}</tcl> <h4>2.5.4 The sqlite_stat2 table</h4> <p>The sqlite_stat2 is only created and is only used if SQLite is compiled with SQLITE_ENABLE_STAT2 and if the SQLite version number is between 3.6.18 and 3.7.8. The sqlite_stat2 table is neither read nor written by any version of SQLite before 3.6.18 nor after 3.7.8. The sqlite_stat2 table contains additional information about the distribution of keys within an index. The schema of the sqlite_stat2 table is as follows: <blockquote><pre> CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample); </pre></blockquote> <p>The sqlite_stat2.idx column and the sqlite_stat2.tbl column in each row of the sqlite_stat2 table identify an index described by that row. There are usually 10 rows in the sqlite_stat2 table for each index. <p>The sqlite_stat2 entries for an index that have sqlite_stat2.sampleno between 0 and 9 inclusive are samples of the left-most key value in the index taken at evenly spaced points along the index. Let C be the number of rows in the index. Then the sampled rows are given by <blockquote> rownumber = (i*C*2 + C)/20 </blockquote> <p>The variable i in the previous expression varies between 0 and 9. Conceptually, the index space is divided into 10 uniform buckets and the samples are the middle row from each bucket. <p>The format for sqlite_stat2 is recorded here for legacy reference. Recent versions of SQLite no longer support sqlite_stat2 and the sqlite_stat2 table, it is exists, is simply ignored. <tcl>hd_fragment stat3tab {sqlite_stat3}</tcl> <h4>2.5.5 The sqlite_stat3 table</h4> <p>The sqlite_stat3 is only created and is only used if SQLite is compiled with [SQLITE_ENABLE_STAT3] and if the SQLite version number is 3.7.9 or greater. The sqlite_stat3 table is neither read nor written by any version of SQLite before 3.6.9. The sqlite_stat3 table contains additional information about the distribution of keys within an index, information that the query planner can use to devise better and faster query algorithms. The schema of the sqlite_stat3 table is as follows: <blockquote><pre> CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample); </pre></blockquote> <p>There are usually multiple entries in the sqlite_stat3 table for each index. The sqlite_stat3.sample column holds the value of the left-most field of an index identified by the sqlite_stat3.idx and the sqlite_stat3.tbl columns. If the sqlite_stat3.idx and sqlite_stat3.tbl columns hold the same value, then that row contains a sample from the [INTEGER PRIMARY KEY] of the table. The sqlite_stat3.nEq column holds the approximate number of entries in the index whose left-most column exactly matches the sample. The sqlite_stat3.nLt holds the approximate number of entries in the index whose left-most column is less than the sample. The sqlite_stat3.nDLt column holds the approximate number of distinct left-most entries in the index that are less than the sample. <p>Future versions of SQLite might change to store a string containing multiple integers values in the sqlite_stat3.nDLt column, a string in which the first integer will be the number of prior index entries that are distinct in the left-most column, the second integer is the number of prior index entries that are distinct in the first two columns, the third integer is the number of prior index entries that are distinct in the first three columns, and so forth With such an extension, the nDLt field will become similar in function to the sqlite_stat1.stat field. <p>There can be an arbitrary number of sqlite_stat3 entries per index. The [ANALYZE] command will typically generate sqlite_stat3 tables that contain between 10 and 40 samples that are distributed across the key space and with large nEq values. <tcl>hd_fragment rollbackjournal {rollback journal format}</tcl> <h2>3.0 The Rollback Journal</h2> <p>The rollback journal is a file associated with each SQLite database file that hold information used to restore the database file to its initial state during the course of a transaction. |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
186 187 188 189 190 191 192 | Section {ANALYZE} analyze ANALYZE BubbleDiagram analyze-stmt 1 </tcl> <p> ^The ANALYZE command gathers statistics about tables and indices and stores the collected information | | | | > > > | | | | | | | 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 | Section {ANALYZE} analyze ANALYZE BubbleDiagram analyze-stmt 1 </tcl> <p> ^The ANALYZE command gathers statistics about tables and indices and stores the collected information in a [internal tables] in the database where the query optimizer can use them to help make better query planning choices. ^If no arguments are given, all attached databases are analyzed. ^If a database name is given as the argument, then all tables and indices in that one database are analyzed. ^If the argument is a table name, then only that table and the indices associated with that table are analyzed. ^If the argument is an index name, then only that one index is analyzed.</p> <p> ^The default implementation stores all statistics in a single table named "[sqlite_stat1]". ^If SQLite is compiled with the [SQLITE_ENABLE_STAT3] option, then additional histogram data is collected and stored in [sqlite_stat3]. Older versions of SQLite would make use of the [sqlite_stat2] table when compiled with [SQLITE_ENABLE_STAT2] but all recent versions of SQLite ignore the sqlite_stat2 table. Future enhancements may create additional [internal tables] with the same name pattern except with final digit larger than "3".</p> <p> ^The [ALTER TABLE] command does not work on the sqlite_stat1 or sqlite_stat3 tables, but all the content of those tables can be queried using [SELECT] and can be deleted, augmented, or modified using the [DELETE], [INSERT], and [UPDATE] commands. ^(The [DROP TABLE] command works on sqlite_stat1 and sqlite_stat3 as of SQLite version 3.7.9.)^ Appropriate care should be used when changing the content of the statistics tables as invalid content can cause SQLite to select inefficient query plans. Generally speaking, one should not modify the content of the statistics tables by any mechanism other than invoking the ANALYZE command.</p> <p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.</p> <p> The query planner might not notice manual changes to the sqlite_stat1 and/or sqlite_stat3 tables. ^An application can force the query planner to reread the statistics tables by running <b>ANALYZE sqlite_master</b>. </p> <tcl> ############################################################################## Section {ATTACH DATABASE} attach *ATTACH |
︙ | ︙ | |||
603 604 605 606 607 608 609 610 611 612 613 614 615 616 | the built-in BINARY collating sequence is used.</p> <p>There are no arbitrary limits on the number of indices that can be attached to a single table. ^(The number of columns in an index is limited to the value set by [sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p> <p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. ^Any attempt to insert a duplicate entry will result in an error. ^For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and | > | 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 | the built-in BINARY collating sequence is used.</p> <p>There are no arbitrary limits on the number of indices that can be attached to a single table. ^(The number of columns in an index is limited to the value set by [sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p> <tcl>hd_fragment uniqueidx {unique index}</tcl> <p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. ^Any attempt to insert a duplicate entry will result in an error. ^For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and |
︙ | ︙ | |||
776 777 778 779 780 781 782 783 784 785 786 787 788 789 | compile-time parameter. ^A single row of a table cannot store more than [SQLITE_MAX_LENGTH] bytes of data. ^Both of these limits can be lowered at runtime using the [sqlite3_limit()] C/C++ interface.</p> <tcl>hd_fragment {constraints} {constraints}</tcl> <h3>SQL Data Constraints</h3> <p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. ^Or, if a PRIMARY KEY clause is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE statement, it is an error. | > | 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 | compile-time parameter. ^A single row of a table cannot store more than [SQLITE_MAX_LENGTH] bytes of data. ^Both of these limits can be lowered at runtime using the [sqlite3_limit()] C/C++ interface.</p> <tcl>hd_fragment {constraints} {constraints}</tcl> <h3>SQL Data Constraints</h3> <tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl> <p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. ^Or, if a PRIMARY KEY clause is specified as a [table-constraint], then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE statement, it is an error. |
︙ | ︙ | |||
806 807 808 809 810 811 812 813 814 815 816 817 818 819 | conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly. <p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. ^For each UNIQUE constraint on the table, each row must feature a unique combination of values in the columns identified by the UNIQUE constraint. ^As with PRIMARY KEY constraints, for the purposes of UNIQUE constraints NULL values are considered distinct from all other values (including other NULLs). ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so | > | 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 | conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly. <tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint}</tcl> <p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. ^For each UNIQUE constraint on the table, each row must feature a unique combination of values in the columns identified by the UNIQUE constraint. ^As with PRIMARY KEY constraints, for the purposes of UNIQUE constraints NULL values are considered distinct from all other values (including other NULLs). ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so |
︙ | ︙ | |||
838 839 840 841 842 843 844 845 846 847 848 849 850 851 | violation has occurred.)^ ^If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. ^The expression of a CHECK constraint may not contain a subquery. <p>CHECK constraints have been supported since [version 3.3.0]. Prior to version 3.3.0, CHECK constraints were parsed but not enforced. <p>^A <b>NOT NULL</b> constraint may only be attached to a column definition, not specified as a table constraint. Not surprisingly, ^(a NOT NULL constraint dictates that the associated column may not contain a NULL value. Attempting to set the column value to NULL when inserting a new row or updating an existing one causes a constraint violation.)^ <p>Exactly how a constraint violation is dealt with is determined by the | > | 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 | violation has occurred.)^ ^If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. ^The expression of a CHECK constraint may not contain a subquery. <p>CHECK constraints have been supported since [version 3.3.0]. Prior to version 3.3.0, CHECK constraints were parsed but not enforced. <tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl> <p>^A <b>NOT NULL</b> constraint may only be attached to a column definition, not specified as a table constraint. Not surprisingly, ^(a NOT NULL constraint dictates that the associated column may not contain a NULL value. Attempting to set the column value to NULL when inserting a new row or updating an existing one causes a constraint violation.)^ <p>Exactly how a constraint violation is dealt with is determined by the |
︙ | ︙ |