Is there any difference between INT PRIMARY KEY
and INTEGER PRIMARY KEY
when defining a schema for a table? When int primary key is used, I got sqlite_autoindex
thing generated; when integer primary key , I got sqlite_sequence
table generated. what's the difference? what side effects can have the first and second variants?
However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. So in MS Sql Server (for example), an "int" == "integer" == 4 bytes/32 bits. In contrast, a SqlLite "integer" can hold whatever you put into it: from a 1-byte char to an 8-byte long long.
The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables. If a table has a single column primary key and the declared type of that column is "INTEGER" and the table is not a WITHOUT ROWID table, then the column is known as an INTEGER PRIMARY KEY.
Introduction to SQLite primary key A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.
In SQLite, a primary key is a single field or combination of fields that uniquely defines a record. A table can have only one primary key. TIP: While the SQL-89 and SQL-92 standards do not allow a NULL value in a primary key, SQLite does allow a NULL under certain circumstances.
UPDATE: SQLite's ROWID column is now a 64-bit integer:
In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
It is all explained in SQLite 3 documentation:
2.0 The INTEGER PRIMARY KEY
One exception to the typelessness of SQLite is a column whose type is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT". A column of type INT PRIMARY KEY is typeless just like any other.) INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any attempt to insert non-integer data will result in an error.
INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY KEY column, the column will actually be filled with an integer that is one greater than the largest key already in the table. Or if the largest key is 2147483647, then the column will be filled with a random integer. Either way, the INTEGER PRIMARY KEY column will be assigned a unique integer. You can retrieve this integer using the sqlite_last_insert_rowid() API function or using the last_insert_rowid() SQL function in a subsequent SELECT statement.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With