如何使复合键成为唯一的？ [英] How to make a composite key to be unique?

问题描述

我正在一所学校制作一个学生数据库。这是我迄今为止所做的：

如果您不喜欢阅读跳转到简短部分

问题是我对这种设计不满意。我想将等级，路径和 id_class 的组合是独一无二的，作为学生表的主要关键。
我可以删除 student_id ，并从3中复合键，但我也不希望。也许我应该让另一张表让我们说 combination_id 其中等级，路基和 id_class 是外键，还有一个额外的列 comb_id 用作表的ID。所有的列都将是主键。但问题是这3列仍然可以重复，因为额外的列（ comb_id ）。例如，我可以拥有相同的等级，路径和 class_id 但不同的 comb_id ，这将使该行有效，因为表的4列的复合键（ combination_id ）

简而言之，我想要 students_id 保持表的唯一主键，但是成为外键另外一张表是等级，路基和 class_id 。

如果我不清楚，请在下面的评论中提前谢谢。

PS 对于这个难以置信的标题我很抱歉，但我不太命名

编辑1：
要更清楚：
等级可以是1到12
路基可以可以是一个j
id_class 可以是1到30，这是你在课上的号码

所以一个学生可以从7b班和他的班级中的人数 - 5

解决方案

不要混合独特的概念键和主键。您可以很好地添加一个唯一键，跨越三个等级，路径和 class_id 。这样，可能没有两行这三列相同的值。当你写的时候，你不想把这三个作为一个复合的主键，我不知道一个复合的唯一的补充键是否会更好。如果没有，您必须澄清复合键是否可以接受。

要创建唯一键，可以使用以下SQL 声明：

  ALTER TABLE学生ADD UNIQUE gsc（成绩，路基，class_id）; 
  

单词 gsc 只有一个名字我从主要专栏的缩写组成;使用您想要的任何名称，因为除非您想要识别某些 EXPLAIN 输出或类似的密钥，否则几乎不起作用。

I am making a database of students in one school.Here is what I have so far:

If you don't like reading jump to the "In short" part

The problem is that I'm not happy with this design. I want the combination of grade, subgrade and id_class to be unique and to serve as a primary key for the students table. I can remove the student_id and make a composite key from the 3 but I don't want that either. Maybe I should make another table lets say combination_id where grade, subgrade and id_class are foreign keys and there is one extra column comb_id that serves as ID for the table. And all the columns will be Primary Keys. But the problem is that those 3 columns can still repeat because of that extra column (comb_id). For example I can have the same grade, subgrade and class_id but different comb_id which will make the row valid because of the composite key of the 4 columns of the table (combination_id).

In short I want students_id to remain the only primary key of the table but to be a foreign key to another table which is somehow unique combination of grades, subgrade and class_id.

If I was not clear enough ask in the comments below and thank you in advance.

PS I'm sorry for the indescriptive title but I'm bad at naming

EDIT 1: To be more clear: grade can be 1 to 12 subgrade can be a to j id_class can be 1 to 30 and it is your number in class

So a student can be from 7b class and his number in class - 5

解决方案

Don't mix the concepts of unique keys and primary keys. You can very well add a unique key spanning the three columns grades, subgrade and class_id. That way, no two rows could have the same values for these three columns. As you write that you don't want to have these three as a composite primary key, I'm not sure whether a composite unique supplemental key would be any better. If not, you'll have to clarify when composite keys are acceptable.

To create a unique key, you can use the following SQL statement:

ALTER TABLE students ADD UNIQUE gsc (grades, subgrade, class_id);

The word gsc there is just a name I made up from the initials of the key columns; use whatever name you want, as it hardly matters unless you want to identify the key in some EXPLAIN output or similar.

这篇关于如何使复合键成为唯一的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！