SQLite - 两个PK，一个FK [英] SQLite - two PK, one FK

问题描述

我正在设计一个存储有关学科信息的表 - 它允许用户添加记录，但我还需要能够自己添加记录(这将在更新时显示给用户).

I'm designing a table that stores information about Disciplines - it allows a user to add records but I also need to be able to add records myself (which will appear to the user on an update).

问题当然是自动递增的 ID - 如果我启动列出 9 的应用程序并在更新中添加第 10 个纪律，在我这边它会以 10 的 ID 出现，但他们可能已经创建了20. 很明显，基本的东西，但如果我改为将数据拆分为具有相同架构的单独表，我会遇到另一个表(列出主题)的问题，它需要一个单独的 PK 列表作为 Discpline FK.

The problem of course is the auto-incrementing ID - if I launch the app with 9 listed and add a 10th discipline in an update, on my side it'll appear with the ID of 10, but they may have already created 20. It's obvious, basic stuff but if I instead split the data into seperate tables with the same schema, I have the problem of a further table (which lists subjects) needing a single list of PKs for it's Discpline FK.

所以它们不能是两个单独的自动递增 ID 列表，因为 FK 不能有重复的值，但它们也不能是同一列以避免重复的 ID.我唯一想到的是将数据保存在一个表中，在数据库级别和应用程序代码中删除自动增量，在我知道的特定数字处开始自动增量(+1 到最后一个数字)不会接近用户添加的记录.但这似乎既脏又低效，我知道在数据库级别有一种更简单的方法，我只是没有想到.

So they can't be two seperate lists of auto-incrementing IDs because the FK can't have duplicate values but they also can't be the same column to avoid duplicate IDs. The only thing i've thought of is keeping the data in one table, removing the auto-increment at the database level and in the app's code, starting a auto-increment (+1 to last number) at a specific number I know I won't come close to for the user's added records. But this seems dirty and inefficient and I know there's an easier method at the database level I'm just not thinking of.

感谢您的帮助，保罗

我可以看出我不够清楚，因此对于任何混淆，我们深表歉意.我目前有两个表，但这也适用于未来的表.

I can see that i'm not being clear enough, so apologies for any confusion. I currently have two tables, however this will apply to future tables as well.

• tbl_disciplines(disciplines_id、jection_name)
• tbl_subjects(subjects_id、subjects_name、subjects_fk_disciplines)

我知道 tbl_disciplines 可以包含来自我自己和用户的条目，但是由于我对 SQLite 的理解以及我将添加更多表的知识，我希望将它们作为两个单独的表，其中许多表有几十个字段和FK.当未来的表可能需要在更新时添加 100 多条新记录时，我不想在 onUpgrade 中手动写出每条新记录.将使用数据库的应用旨在使添加到数据库的过程更高效、更简单.

I understand tbl_disciplines can have entries from both myself and a user however I was hoping to have them as two seperate tables due to my understanding of SQLite and the knowledge that I'll be adding more tables, many with dozens of fields and FKs. I don't want to manually write out each new record in onUpgrade when future tables could require 100+ new records to be added on an update. The app that'll be using the database, is designed to make adding to the database a more efficient, simplier process.

如果它们是单独的表，我知道使用 onUpgrade，我可以简单地删除并替换用户端记录的旧表，并使用自上次更新以来我使用该应用程序添加的所有记录的新表，保留第二个用户记录表不变.据我了解，如果我将我的记录和用户的记录保存在同一个表中，我将需要手动将所有更新记录逐条添加到 onUpgrade 区域的同一个现有表中，这样我就不会删除他们的数据.进一步的意思是，我不能使用该应用作为前端来添加我自己的记录，因为我不能在更新时用我的 tbl_disciplines 替换他们的 tbl_disciplines.

If they're seperate tables, I know with onUpgrade, I can simply drop and replace the older table of my records on the user side with a new table of all records i've added using the app since the last update, leaving a second table of user records untouched. From what I understand, if I keep mine and the user's records in the same table, I would need to manually add all of my updates record by record to the same existing table in onUpgrade area so I don't erase their data. Further meaning, I can't use the app as a front-end for adding my own records because I can't just replace their tbl_disciplines with my tbl_disciplines on an update.

此外，如果我需要编辑有关我添加的记录之一的内容，我无法通过其 ID 查询记录，因为每个用户的 ID 都不同.

Additionally, if I need to edit something about one of the records i've added, I cannot query the record by it's ID as it'll be different for every user.

推荐答案

我不清楚你认为问题是什么.

It's not clear to me what you think the problem is.

如果您使用表中定义的自增字段，则不会添加具有相同 ID 的两条记录.它们将保证在每个 INSERT 上都是唯一的.您不在 INSERT 语句中指定值，而是让 SQLite 为您决定值并在 INSERT 语句执行后读取它.

If you use an auto-increment field defined in the table, no two records will be added with the same ID. They will be guaranteed unique on each INSERT. You do not specify the value in the INSERT statement, you let SQLite decide the value for you and read it back after the INSERT statement executes.

在您引用的示例中，如果用户已经添加了足够多的记录，以至于内部计数器"已达到 20，您的新记录将被分配 21 用于自动递增列，用户添加的下一条记录将是22. 只要你把它留给数据库，这是有保证的.无需担心竞争条件.

In the example you cited, if users have already added enough records so that the internal "counter" has reached 20, your new record will be assigned 21 for the auto-increment column and the next record added by a user will be 22. As long as you leave it up to the database this is guaranteed. There are no race conditions to worry about.

这篇关于SQLite - 两个PK，一个FK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！