共享主键
我猜这是一个半常见的问题，但我在过去的问题列表中找不到它。我有一组需要共享主键索引的产品表。假设如下：
product1_table:
id,
name,
category,
...other fields
product2_table:
id,
name,
category,
...other fields
product_to_category_table:
product_id,
category_id
显然，在两个产品表之间拥有共享索引会很有用。请注意，将它们分开的想法是因为除了基础知识之外，它们还具有很大不同的字段集，但它们具有共同的分类。
更新：
很多人建议使用表继承（或gen-spec）。这是我知道的一个选项，但在其他数据库系统中，我可以在表之间共享序列，我希望 MySQL 有类似的解决方案。我假设它不是基于回复。我想我必须使用表继承......谢谢大家。
I would guess this is a semi-common question but I can't find it in the list of past questions. I have a set of tables for products which need to share a primary key index. Assume something like the following:
product1_table:
id,
name,
category,
...other fields
product2_table:
id,
name,
category,
...other fields
product_to_category_table:
product_id,
category_id
Clearly it would be useful to have a shared index between the two product tables. Note, the idea of keeping them separate is because they have largely different sets of fields beyond the basics, however they share a common categorization.
UPDATE:
A lot of people have suggested table inheritance (or gen-spec). This is an option I'm aware of but given in other database systems I could share a sequence between tables I was hoping MySQL had a similar solution. I shall assume it doesn't based on the responses. I guess I'll have to go with table inheritance... Thank you all.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
这并不常见，不。没有共享主键的本机方法。在您的情况下我可能会做的是：
也就是说，有一个主产品表，其中包含所有产品的条目，并具有在类型之间泛化的字段，以及带有外键的类型指定表到主产品表中，其中具有特定于类型的数据。
It's not really common, no. There is no native way to share a primary key. What I might do in your situation is this:
That is, there is one master product table that has entries for all products and has the fields that generalize between the types, and type-specified tables with foreign keys into the master product table, which have the type-specific data.
更好的设计是将公共列放在一个产品表中，将特殊列放在两个单独的表中。在所有三个表中使用product_id 作为主键，但在两个特殊表中，它还是返回主产品表的外键。
这简化了按类别查找 ID 和名称的基本产品搜索。
另请注意，您的设计允许每种产品最多属于一个类别。
A better design is to put the common columns in one products table, and the special columns in two separate tables. Use the product_id as the primary key in all three tables, but in the two special tables it is, in addition, a foreign key back to the main products table.
This simplifies the basic product search for ids and names by category.
Note, also that your design allows each product to be in one category at most.
看来您正在寻找表继承。
您可以使用一个公共表
product，其中包含product1和product2共有的属性，再加上一个type属性，该属性可以是“product2”或< code>"product1"那么表
product1和product2将拥有其所有特定属性以及对父表的引用产品。It seems you are looking for table inheritance.
You could use a common table
productwith attributes common to both product1 and product2, plus atypeattribute which could be either"product2"or"product1"Then tables
product1andproduct2would have all their specific attributes and a reference to the parent tableproduct.首先我要声明，我同意混沌、拉里和菲尔所说的一切。
但如果你坚持另一种方式...
你们共同PK有两个原因。两个表之间的一个唯一性和两个完整的引用完整性。
我不确定 Auto_increment 列支持什么“序列”功能。似乎有一个 系统设置按值定义增量，但每列不定义增量。
我在 Oracle 中要做的只是在两个表之间共享相同的序列。另一种技术是在 auto_increment 中将 STEP 值设置为 2，并从 1 开始，另一个从 2 开始。无论哪种方式，您都会在它们之间生成唯一的值。
您可以创建第三个表，其中除了 PK 列之外什么都没有。如果无法在一台服务器内创建跳过自动编号，则此列还可以提供自动编号。然后在每个数据表上添加 CRUD 触发器。对任一数据表的插入都会首先启动对伪索引表的插入（并返回 ID 以在本地表中使用）。同样，从本地表中删除将启动从伪索引表中的删除。任何需要指向父表的子表都指向这个伪索引表。
请注意，这需要是每行触发器，并且会减慢这些表上的增删改查速度。但像“产品”这样的表一开始往往不会有很高的 DML 率。任何抱怨“性能影响”的人都没有考虑规模。
请注意，这是作为一种可行的替代方案提供的，而不是我推荐的最佳方式
First let me state that I agree with everything that Chaos, Larry and Phil have said.
But if you insist on another way...
There are two reasons for your shared PK. One uniqueness across the two tables and two to complete referential integrity.
I'm not sure exactly what "sequence" features the Auto_increment columns support. It seem like there is a system setting to define the increment by value, but nothing per column.
What I would do in Oracle is just share the same sequence between the two tables. Another technique would be to set a STEP value of 2 in the auto_increment and start one at 1 and the other at 2. Either way, you're generating unique values between them.
You could create a third table that has nothing but the PK Column. This column could also provide the Autonumbering if there's no way of creating a skipping autonumber within one server. Then on each of your data tables you'd add CRUD triggers. An insert into either data table would first initiate an insert into the pseudo index table (and return the ID for use in the local table). Likewise a delete from the local table would initiate a delete from the pseudo index table. Any children tables which need to point to a parent point to this pseudo index table.
Note this will need to be a per row trigger and will slow down crud on these tables. But tables like "product" tend NOT to have a very high rate of DML in the first place. Anyone who complains about the "performance impact" is not considering scale.
Please note, this is provided as a functioning alternative and not my recommendation as the best way
您不能“共享”主键。
在不了解所有细节的情况下，我最好的建议是将这些表合并为一个产品表。为某些产品填充可选字段而不为其他产品填充不一定是糟糕的设计。
另一种选择是采用某种继承模型，其中有一个产品表，然后是两个产品“子类型”表，它们引用主产品表并拥有自己的专门字段集。恕我直言，查询这个模型比单个表更痛苦，这就是为什么我认为它是不太理想的选择。
You can't "share" a primary key.
Without knowing all the details, my best advice is to combine the tables into a single product table. Having optional fields that are populated for some products and not others is not necessarily a bad design.
Another option is to have a sort of inheritence model, where you have a single product table, and then two product "subtype" tables, which reference the main product table and have their own specialized set of fields. Querying this model is more painful than a single table IMHO, which is why I see it as the less-desirable option.
您的解释有点模糊，但是，从我的基本理解来看，我会很想这样做
产品表包含公共字段
Product_extra1 表和 Product_extra2 表包含不同的字段
这些表在product.product_id 和之间强制执行一对一的关系
Product_extra1.product_id 等。通过使用唯一约束将外键表（product_extra1 等）中的product_id 设置为唯一，强制执行一对一关系。
您需要决定如何填充此数据的业务规则根据
您上面的内容，product_category 表是一个相交表（1 对多 - 多对 1），这意味着每个产品可以与许多类别相关
现在可以保持不变。
Your explanation is a little vague but, from my basic understanding I would be tempted to do this
The product table contains common fields
the product_extra1 table and the product_extra2 table contain different fields
these tables habe a one to one relationship enforced between product.product_id and
product_extra1.product_id etc. Enforce the one to one relationship by setting the product_id in the Foreign key tables (product_extra1, etc) to be unique using a unique constraint.
you will need to decided on the business rules as to how this data is populated
Based on what you have above the product_category table is an intersecting table (1 to many - many to 1) which would imply that each product can be related to many categories
This can now stay the same.
这是gen-spec的又一个案例。
查看之前的讨论
This is yet another case of gen-spec.
See previous discussion