ROWID is of alias of an integer primary key:
CREATE TABLE test1(id INTEGER, b TEXT, PRIMARY KEY(id))
INSERT INTO test1 (id, b) VALUES (5, 'five');
INSERT INTO test1 (id, b) VALUES (6, 'six');
INSERT INTO test1 (b) VALUES ('seven');
SELECT rowid, * FROM test1;
the result is the following:
(5, 5, 'five')
(6, 6, 'six')
(7, 7, 'seven')
DELETE FROM test1 WHERE id = 6;
INSERT INTO test1 (b) VALUES ('eight');
(5, 5, 'five')
(7, 7, 'seven')
(8, 8, 'eight')
An example with composite primary key
CREATE TABLE t2(id INTEGER, a INTEGER, b TEXT, PRIMARY KEY(id, a));
INSERT INTO t2 (id, a, b) VALUES (5, 15, 'five');
(1, 5, 15, 'five')
Primary key is not unique in SQLite
https://stackoverflow.com/questions/79462083/primary-key-is-not-unique-in-sqlite
Rightfully SQLite should not allow NULL as primary key value, but due to a bug it allows it
https://www.geeksforgeeks.org/mysql-unique-index/
ALTER TABLE tbl_name
DROP INDEX index_unique_columns;
ALTER TABLE tbl_name
ADD UNIQUE INDEX index_unique_columns (new_column1, new_column2, …);
https://stackoverflow.com/questions/26106237/sqlitedatabase-nested-transaction-and-workaround
db.execSql(“SAVEPOINT test”); // declare savepoint
// … do some operations
db.execSql(“;ROLLBACK TO test”); // rollback
db.execSql(“RELEASE test”); // save changes
https://stackoverflow.com/questions/70160629/many-to-many-link-table-foreign-key-modeling-in-sqlite
https://dbfiddle.uk/pCcnj9IW
CREATE TABLE Links_Parts (
link_id INTEGER NOT NULL REFERENCES Links(link_id),
part_id INTEGER NOT NULL REFERENCES Parts(part_id),
PRIMARY KEY(link_id, part_id)
);
SQLite starting rowid of 0
https://stackoverflow.com/questions/35043256/sqlite-starting-rowid-of-0
The documentation says that, with AUTOINCREMENT,
the ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.
So the algorithm looks not only at the value in the sqlite_sequence table, but also at the last row in the table, and uses the larger of these two values.
When the table is empty, the largest actual rowid is instead assumed to be zero. This is done so that the first inserted rowid becomes 1.
Therefore, the only way to generate a rowid less than one is to have another row already in the table.
select * from sqlite_sequence;