主键删除后删除外键SQLite [英] Decrementing foreign key after primary key deletion SQLite

问题描述

SQL Noob在这里.我正在设计一个GPS跟踪器应用程序，并且有两个表；轨道描述符表和轨道数据表.轨道描述符表具有主键(自动递增)，轨道数据表具有外键.我想在从描述符表中删除一行后，将所有大于该键的键都递减1(自动递增会这样做吗?).我还希望在数据表中发生相同的事情，其中​​所有大于删除的键都将递减. 我正在为Android开发，并且知道如何通过以下方法进行操作:

SQL Noob here. I am designing a GPS-tracker application and have two tables; a track descriptor table and a track data table. The track descriptor table has the primary key (autoincrement) and the track data table has the foreign key. I would like that after I delete a row from descriptor table, all keys greater than that key are decremented by 1 (does autoincrement do this?). I would also like the same thing to happen in the data table, where all keys greater than deleted are decremented. I am developing for Android and have know how to do it by this method: How do I add and subtract numbers in SQLite for android? . But this seems very resource intensive, is there any way to do the same thing at Table creation (triggers)? Here is the declaration of the two tables (sorry for the Java!!!):

// TRACK_DESCRIPTION table create statement
        String createTableTrackDescription =
                "CREATE TABLE " + Constants.TABLE_TRACK_DESCRIPTION + "("
                        + Constants.COL_TRACK_DESC_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                        + Constants.COL_TRACK_NAME + " TEXT,"
                        + Constants.COL_LOCATION + " TEXT)";
        // TRACK_DATA table create statement
        String createTableTrackData =
                "CREATE TABLE " + Constants.TABLE_TRACK_DATA + "("
                        + Constants.COL_TRACK_DATA_ID + " INTEGER,"
                        + Constants.COL_LATITUDE + " REAL,"
                        + Constants.COL_LONGITUDE + " REAL,"
                        + Constants.COL_SPEED + " REAL,"
                        + Constants.COL_ALTITUDE + " INTEGER,"
                        + Constants.COL_TIMESTAMP + " INTEGER,"
                        + "FOREIGN KEY(" + Constants.COL_TRACK_DATA_ID + ") REFERENCES "
                        + Constants.TABLE_TRACK_DESCRIPTION + "(" + Constants.COL_TRACK_DESC_ID + "))";

推荐答案

这会自动递增吗?

does autoincrement do this?

不，实际上，编码为 AUTOINCREMENT 限制了未使用的 rowids 的重复使用.

No, in fact coding AUTOINCREMENT, restricts the re-use of unused rowids.

除非您指定 WITHOUT ROWID ，否则SQLite会为每一行创建一个统一的标识符 rowid .该标识符称为 rowid .

SQLite creates a unqiue identifier, the rowid, for every row unless you specify WITHOUT ROWID. This identifier is known as the rowid.

如果您对 column_name INTEGER PRIMARY KEY 进行编码，则 column_name 是 rowid 的别名(例如，CREATE TABLE x (_id INTEGER PRIMARY KEY, another_column TEXT)使用以下命令创建表x 2列， _id 列是 rowid )

If you code column_name INTEGER PRIMARY KEY then column_name is an alias for the rowid (e.g. CREATE TABLE x (_id INTEGER PRIMARY KEY, another_column TEXT) creates table x with 2 columns, column _id is an alias of the rowid)

行通常会是一个较高的值(首先是 1 )，通常是下一个，以此类推，以此类推，以此类推，以此类推，以此类推，以此类推，以此类推1,2在这种情况下， AUTOINCREMENT (是否经过编码)被认为如下:-

The rowid will be a higher value (first it is 1) generally the next so normally 1,2 etc, unless the highest number has been used 9223372036854775807 in which case AUTOINCREMENT if coded or not is considered as follows :-

如果已编码 AUTOINCREMENT (即column_name INTEGER PRIMARY KEY AUTOINCREMENT). AUTOINCREMENT 保证更高的 rowid ，因此，一旦达到9223372036854775807，就会发生SQLITE_FULL 异常；和

If AUTOINCREMENT has been coded (i.e column_name INTEGER PRIMARY KEY AUTOINCREMENT). AUTOINCREMENT guarantees a higher rowid, so once 9223372036854775807 has been reached an SQLITE_FULL exception is raised; and

• 如果您使用值为9223372036854775807的 rowid 别名(其中已编码 AUTOINCREMENT 的别名)强行插入，则SQLITE_FULL异常将在其他情况下发生尝试插入.

• if you force an insertion using the value 9223372036854775807 for an alias of rowid where AUTOINCREMENT has been coded, then the SQLITE_FULL exception will occur if another insert is attempted.

没有 AUTOINCREMENT 的情况下，SQLITE会随机选择未使用的 rowid (例如，已删除的行).

AUTOINCREMENT 关键字可确保 rowid 更大.

• 正是由于未编码WITHOUT ROWID，导致唯一的通常递增的列为 rowid .
• 使用column_name INTEGER PRIMARY KEY为 rowid 创建别名.
• 行通常是不可见的，即SELECT *，将不包含行列.然而;它会包含 rowid 的别名.
• 您可以通过将行指定为一列来添加它，例如SELECT rowid, * (行和所有其他列).
• 编码 AUTOINCREMENT 关键字会带来额外的CPU，内存，磁盘空间和磁盘I/O开销，如果不是严格需要，则应避免使用.通常不需要.

• It is when WITHOUT ROWID is not coded that causes a unique generally incrementing column as the rowid.
• It is the use of column_name INTEGER PRIMARY KEY that creates an alias for the rowid.
• The rowid is not normally visible i.e. SELECT *, will not include the rowid column. However; it would include an alias of the rowid.
• You can include the rowid by specifying it as a column e.g. SELECT rowid, * (the rowid and all other columns).
• Coding the AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

您不妨看一下:-

• SQLite自动增量

表格不透明

这篇关于主键删除后删除外键SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！