![]() ![]() An "INTEGER PRIMARY KEY" column in a WITHOUT ROWID table works like an "INT PRIMARY KEY" column in an ordinary table: It is a PRIMARY KEY that has integer affinity.ĪUTOINCREMENT does not work on WITHOUT ROWID tables. But since there is no rowid in a WITHOUT ROWID table, that special meaning no longer applies. In an ordinary table, "INTEGER PRIMARY KEY" means that the column is an alias for the rowid. The special behaviors associated " INTEGER PRIMARY KEY" do not apply on WITHOUT ROWID tables. An attempt to create a WITHOUT ROWID table without a PRIMARY KEY results in an error. ![]() But there are some additional restrictions on WITHOUT ROWID tables that do not apply to ordinary rowid tables:Įvery WITHOUT ROWID table must have a PRIMARY KEY. The only advantage of a WITHOUT ROWID table is that it can sometimes use less disk space and/or perform a little faster than an ordinary rowid table.įor the most part, ordinary rowid tables and WITHOUT ROWID tables are interchangeable. Anything that can be done using a WITHOUT ROWID table can also be done in exactly the same way, and exactly the same syntax, using an ordinary rowid table. The WITHOUT ROWID syntax is an optimization. 2.0 Differences From Ordinary Rowid Tables ![]() The WITHOUT ROWID concept was added in order to meet that need without breaking backwards compatibility with the billions of SQLite databases already in use at the time (circa 2013). But as the demands on SQLite grew, the need for tables in which the PRIMARY KEY really did correspond to the underlying row key grew more acute. This approach worked well for many years. However, when SQLite was first designed, it used only integer rowids for row keys to simplify the implementation. In an elegant system, all tables would behave as WITHOUT ROWID tables even without the WITHOUT ROWID keyword. WITHOUT ROWID is found only in SQLite and is not compatible with any other SQL database engine, as far as we know. An attempt to open a database that contains one or more WITHOUT ROWID tables using an earlier version of SQLite will result in a "malformed database schema" error. SQLite version 3.8.2 or later is necessary in order to use a WITHOUT ROWID table. ![]() However, only "rowid" works as the keyword in the CREATE TABLE statement. In most contexts, the special "rowid" column of normal tables can also be called "oid" or " rowid". An error is raised if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY. One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and it will mean the same thing.Įvery WITHOUT ROWID table must have a PRIMARY KEY. For example: CREATE TABLE IF NOT EXISTS wordcount(Īs with all SQL syntax, the case of the keywords does not matter. To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement. There are sometimes space and performance advantages to omitting the rowid. However if the phrase "WITHOUT ROWID" is added to the end of a CREATE TABLE statement, then the special "rowid" column is omitted. The WITHOUT ROWID Optimization 1.0 Introductionīy default, every row in SQLite has a special column, usually called the " rowid", that uniquely identifies that row within the table. The C language interface to SQLite Version 2 Upgrading SQLite, Backwards Compatibility Locking And Concurrency In SQLite Version 3 NULL Handling in SQLite Versus Other Database Engines An Introduction To The SQLite C/C++ InterfaceĬ-language Interface Specification for SQLite ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |