MYSQL：两个字段为PRIMARY KEY + 1字段为UNIQUE，一个问题 [英] MYSQL: Two fields as PRIMARY KEYs + 1 Field as UNIQUE, a question

问题描述

我有两个主要（复合）键，指的是商店和分支。
我以为我应该为每行使用相应的ID，所以我添加了一个名为ID的UNIQUE + AUTO_INCREMENT。

所以我在表上有一列命名ID（AUTO INCREMENT），但它被声明为PRIMARY - 这是自动完成的，我不希望ID为PRIMARY。

我已经学会了如何让MYSQL接受ID字段为UNIQUE和AUTO INCREMENT，因为使AUTO_INCREMENT不是很简单（它想要使它成为主要）。
我不得不删除ID字段（由于某种原因，它不让我删除其PRIMARY索引），然后声明它INDEX，然后只有AUTO INCREMENT。

这是一个好办法吗？
这个设计可能有什么不好吗？

谢谢!!!

解决方案

普遍的智慧是，每个表都应该有一个名为Id的独立自动列。

在经典数据建模中，由Codd和Date，ID字段对于数据的完整逻辑模型不是必需的。

您的ID字段有什么好处？你有没有在此表中引用其ID的一行？如果没有，那么只是离开现场。 （店，分店）提供了一个非常好的候选人成为PK。

I have two primary (composite) keys that refer to a shop and a branch. I thought I should have used a corresponding ID for each row, so I added a UNIQUE + AUTO_INCREMENT named ID.

So I had on the table a column named ID (AUTO INCREMENT), but it was declared PRIMARY - which was done automatically, and I don't want the ID to be PRIMARY. Just the shop and branch.

I have learnt how to trick MYSQL to accept the ID field as UNIQUE and AUTO INCREMENT, as it was not extremely trivial to make the AUTO_INCREMENT (it wanted to make it PRIMARY). I had to ERASE the ID Field (for some reason it didn't let me erase its PRIMARY index), then declare it INDEX, and only then AUTO INCREMENT.

Is that a good approach ? Could there be something I am doing wrong going with this design ?

Thanks !!!

解决方案

The prevailing wisdom is that every table should have a unique autonumbered column named Id.

In classical data modeling, as developed by Codd and Date, the ID field is not necessary for a complete logical model of the data.

What good does the ID field do you? Do you ever reference a row in this table by its ID? If never, then just leave the field out. (shop, branch) provided a perfectly good candidate to be the PK.

这篇关于MYSQL：两个字段为PRIMARY KEY + 1字段为UNIQUE，一个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！