共享主键 [英] Shared Primary Key

问题描述

我想这是一个半常见的问题，但是我在过去的问题列表中找不到它.我为需要共享主键索引的产品提供了一组表.假设类似以下内容:

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.

更新:

很多人建议表继承(或gen-spec).我知道这是一个选项，但是在其他数据库系统中，我可以共享表之间的序列，我希望MySQL具有类似的解决方案.我将假定它不是基于响应的.我猜我将不得不继承表...谢谢大家.

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.

推荐答案

这并不常见，不.没有本机共享主键的方法.在您遇到的情况下，我可能会做的是:

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:

product_table
    id
    name
    category
    general_fields...

product_type1_table:
    id
    product_id
    product_type1_fields...

product_type2_table:
    id
    product_id
    product_type2_fields...

product_to_category_table:
    product_id
    category_id

也就是说，存在一个主产品表，该表具有所有产品的条目，并且具有在类型之间进行归纳的字段，以及具有主产品表中带有外键的类型指定的表，这些表具有特定于类型的数据.

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.

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