数据库表 id-key 空值和引用完整性
我正在学习数据库，使用 SQLce。遇到一些问题，出现此错误：
A foreign key value cannot be inserted because a corresponding primary key value does not exist.
尝试保存没有指定外键的数据行时，数据的完整性和接受度如何工作。是否可以以某种方式将其设置为 NULL，这意味着它不会引用其他表？万一我该怎么办呢？ （对于整数键字段）
此外，如果您使用与其他表中的现有主键相对应的有效外键保存一行，会怎么样。但随后决定删除另一个表中的该条目。所以外键将不再有效。我可以删除吗？它是如何运作的？我认为它应该简单地重置为空值..但也许事情没那么简单？
I'm learning databases, using SQLce. Got some problems, with this error:
A foreign key value cannot be inserted because a corresponding primary key value does not exist.
How does the integrity and acceptance of data work when attempting to save a data row that does not have specified one foreign key. Isn't it possible to set it to NULL in some way, meaning it will not reference the other table? In case, how would I do that? (For an integer key field)
Also, what if you save a row with a valid foreign key that corresponds to an existing primary key in other table. But then decide to delete that entry in this other table. So the foreign key will no longer be valid. Will I be allowed to delete? How does it work? I would think it should then be simply reset to a null value.. But maybe it's not that simple?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您需要做的就是从父级开始向下插入数据。
因此，如果您有一个订单表和一个引用订单的项目表，则必须先创建新订单，然后再将所有子订单添加到列表中。
您可以获得的许多数据访问库（在 C# 中有 Linq to SQL）都会尝试抽象这个问题。
如果您需要删除数据，则实际上必须采用其他方法，请在删除父订单记录之前删除项目。
当然，这假设您正在强制执行外键，则可以不强制执行该键，这在批量删除期间可能很有用。
What you need to do is insert your data starting from the parent down.
So if you have an orders table and an items table that refers to orders, you have to create the new order first before adding all the children to the list.
Many of the data access libraries that you can get (in C# there is Linq to SQL) which will try and abstract this problem.
If you need to delete data you actually have to go the other way, delete the items before you delete the parent order record.
Of course, this assumes you are enforcing the foreign key, it is possible to not enforce the key, which might be useful during a bulk delete.
这是因为表中存在“错误数据”。检查主表中是否有所有对应的值。
DBMS 检查引用完整性，以确保数据库内数据的“正确性”。
例如，如果 TableA 中有一个名为 some_id 的列，其值为 1 到 10，而 TableB 中有一个名为 some_id 的列如果 的值介于 1 到 11 之间，那么 TableA 就没有 TableB 中已有的对应值 (11)。
This is because of "bad data" you have in the tables. Check if you have all corresponding values in the primary table.
DBMS checks the referential integrity for ensuring the "correctness" of data within database.
For example, if you have a column called some_id in TableA with values 1 through 10 and a column called some_id in TableB with values 1 through 11 then TableA has no corresponding value (11) for that which you have already in TableB.
您可以使外键可为空，但我不推荐这样做。可能会出现太多的问题和不一致。重新设计表，以便不需要为不存在的值填充外键。通常，您可以通过将列移动到新表来做到这一点。
You can make a foreign key nullable but I don't recommend it. There are too many problems and inconsistencies that can arise. Redesign your tables so that you don't need to populate the foreign key for values that don't exist. Usually you can do that by moving the column to a new table for example.