自动增量与复合键 [英] Auto-increment vs composite key

问题描述

我还有一个与帖子相关的其他问题：
数据库中的复合主键

I have an additional question related to post: Composite primary keys in databases

请查看那里的帖子（否则我只需要重复一次）。

Please check out the post over there (otherwise I just need to repeat it).

现在我的问题是：
如果我去一个ID自动增量作为主键（就像我接受的那样，我可以通过这个再次引用当前的表格键），我如何确保在User_ID和Admin_ID（两个FK的）之间的组合只能存在一次（是唯一）插入？
这是一个多对多的关系。

可以在前端的编程中完成（通过选择检查现有记录），但是我的感觉告诉我这不是最好的方法，我想知道我是否可以直接把限制放在后端。

Now my question is: If I go for an ID autoincrement as primary key (as I accepted and which allows me to again reference the current table through this key), how can I assure that a combination between User_ID and Admin_ID (both FK's) can only exist once (is unique) on insert? It is a many-to-many relationship.
It could be done in the programming of the front end (check for existing record by select), however my feelings tell me that this is not the best way and I wonder if I can directly put the restriction in the back end.

我将逻辑上添加FK到主键，但是我回到了复合键，这是我一般建议不要使用的。 br>
这样做的正确方法是什么？

I would logically add the FK's to the primary key, but then I'm back to the composite key and this is what I was generally advised rather not to use.
What is the proper way of doing this?

感谢您的帮助。

推荐答案

如何确保User_ID和Admin_ID（FK的）之间的组合只能存在一次（是唯一的）插入？

how can I assure that a combination between User_ID and Admin_ID (both FK's) can only exist once (is unique) on insert?

创建一个复合键

可以在前端的编程中完成（通过选择检查现有记录）

It could be done in the programming of the front end (check for existing record by select)

除非你是访问表的唯一客户端 ¹

It couldn't, unless you are the only client accessing the table.¹

在一个真正的并发环境中，你永远不会知道另一个事务是否插入相同的值（如您正在尝试插入） 您的SELECT，但 您的INSERT。

In a real, concurrent environment, you can never know if another transaction inserted the same value (as you are trying to insert) after your SELECT but before your INSERT.

即使您是唯一的一个访问数据库，你需要一个索引来有效地执行SELECT。那么为什么不让DBMS使用这个索引来获取密钥？

And even if you were the only one accessing the database, you'd need an index for efficient execution of SELECT anyway. So why not let the DBMS utilize this index for a key?

我将逻辑上添加FK到主键， m回复合键，这是我一般建议不要使用的。

I would logically add the FK's to the primary key, but then I'm back to the composite key and this is what I was generally advised rather not to use.

错误的建议。如果列或列的组合必须是唯一的，您有来创建密钥。 ² 您不能跳过创建一个强制执行数据正确性的密钥，因为你有另一个（代理）键。

Wrong advice. If a column or a combination of columns has to be unique, you have to create a key.² You cannot skip creating a key that enforces the correctness of your data just because you have another (surrogate) key.

代理人通常不能替换自然键，只能添加。 ³ 所以问题变成：代理人的额外开销是否值得？有时候，有时候不是，但是在这里没有干细则。

Surrogates cannot generally replace natural keys, they can only be added.³ So the question becomes: is the additional overhead of the surrogate worth it? Sometimes it is, sometimes it isn't, but there are no cut-and-dry rules here.

¹ 还是愿意锁定整个表格，破坏该过程中的可扩展性。

¹ Or are willing to lock the whole table, destroying the scalability in the process.

² 尽管不一定需要

² Though it doesn't necessarily need to be primary.

³ 他们通常替换是自然的钥匙作为主要的角色，但自然键仍然继续作为替代键。

³ What they typically "replace" is the natural key's role as a primary key, but the natural key still continues to live as alternate key.

这篇关于自动增量与复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！