Two SQLite Problems

1. Self-Growth of primary keys

If row_id is the primary key of a table, if auto increment is set in DDL, if it is set to null during insert row_id, the primary key can grow from 1.

However, if a problem occurs after the previous record is deleted at this time, the primary key value of the newly added record does not start from 1, but starts from the last deleted primary key value + 1, for example, if the primary key of the last deleted record is 1200 and the new record is 1201 instead of 1, the solution is to drop the table and recreate it.

2. in one business scenario, one table A updates another table B, and the record with the same primary key of A and B is updated with, if the primary key of A does not exist in B, insert this record of A to B.

In DB2, the combination of update. Select and exsits can be used, but it cannot be used in SQLite.

I have been searching for this question for a long time. Some answers on the Internet cannot meet my needs. They all use insert or replace .... values (...,...) this method of writing dead values also says that unique indexes are used. Basically, they are copied from each other and complain that they don't need to copy and paste them, and then put them on the Internet.

Solution:

Insert or replace into B (row_id, code, name) Select row_id, code, name from

Note:

1. If the primary key is of the integer type, the constraint violation error is reported.

2. It is recommended that the Field names after into and after select be fully written. Otherwise, in the updated table B, fields not written in the SELECT statement will be overwritten as null.