mysql - 在两个唯一的列上创建主键

Question

如果这个问题是个愚蠢的问题，我深表歉意。我试图避免创建任意索引列作为主键。我想做的是:

根据student_id +section_id列的唯一性创建主键(单独的列在表中不会是唯一的，但两者一起将是唯一的)。以下内容不起作用，但我想知道是否有类似的东西可以？

CREATE TABLE Registration 
    (
        student_id VARCHAR(10) NOT NULL,
        section_id VARCHAR (6) NOT NULL,
        midterm_grade VARCHAR(2), 
        final_grade VARCHAR(2),

        CONSTRAINT Registration_unique UNIQUE (student_id, section_id),
        CONSTRAINT Registration_pk PRIMARY KEY (Registration_unique),

        CONSTRAINT Registration_fk1 FOREIGN KEY (student_id) REFERENCES Student(student_id)
            ON DELETE CASCADE,
        CONSTRAINT Registration_fk2 FOREIGN KEY (section_id) REFERENCES Section(section_id)
            ON DELETE CASCADE
    )
    ENGINE = INNODB;

Answer 1

您不需要UNIQUE，因为您可以创建复合PRIMARY KEY (student_id,section_id)。

CREATE TABLE Registration 
    (
        student_id VARCHAR(10) NOT NULL,
        section_id VARCHAR (6) NOT NULL,
        midterm_grade VARCHAR(2), 
        final_grade VARCHAR(2),

        CONSTRAINT Registration_pk PRIMARY KEY (student_id, section_id),

        CONSTRAINT Registration_fk1 FOREIGN KEY (student_id) REFERENCES Student(student_id)
            ON DELETE CASCADE,
        CONSTRAINT Registration_fk2 FOREIGN KEY (section_id) REFERENCES Section(section_id)
            ON DELETE CASCADE
    )

如果您希望 student_id 是唯一的并且 section_id 是唯一的，您需要创建两个唯一的 key

CREATE TABLE Registration 
    (
        student_id VARCHAR(10) NOT NULL,
        section_id VARCHAR (6) NOT NULL,
        midterm_grade VARCHAR(2), 
        final_grade VARCHAR(2),
        CONSTRAINT Registration_unique_student_id UNIQUE (student_id),
        CONSTRAINT Registration_unique_section_id UNIQUE (section_id),
        CONSTRAINT Registration_pk PRIMARY KEY (student_id, section_id),

        CONSTRAINT Registration_fk1 FOREIGN KEY (student_id) REFERENCES Student(student_id)
            ON DELETE CASCADE,
        CONSTRAINT Registration_fk2 FOREIGN KEY (section_id) REFERENCES Section(section_id)
            ON DELETE CASCADE
    )

这意味着您只能插入一名学生 - 一个部分和一个部分 - 一名学生类型的行。

如果您想为多个学生使用相同的部分，请删除第二个唯一的CONSTRAINT Registration_unique_section_id UNIQUE (section_id)。