A method that makes two fields different in MySQL

Sometimes we encounter such a situation: there are a number of different majors, each major has some different school numbers, professional + students can correspond to individual. At this time, how to define the column in the database, to ensure that the Professional + student number corresponding to the uniqueness of students?

It is obviously not feasible to define the number as a unique (unique) because there are 1000 such a number in both A and B majors.

Method 1: Federated primary Key

PRIMARY KEY (Major,id);

not recommended . Because the federated primary key will cause inconvenience to the maintenance of the table. In addition, the primary key may have been set.

Method 2: Establish a federated unique constraint (Federated unique Index) ( recommended )

Federated UNIQUE Constraints: (the uniqueness index is automatically created when a uniqueness constraint is created)

ALTER TABLE ADD CONSTRAINT UNIQUE (Major,id);

Federated Unique Indexes:

CREATE UNIQUE INDEX  on student (Major,id);

The difference between the two:

① when inserting 20 data into a database, where 5 data repeats, if a uniqueness constraint is used, the result must be that 20 of the data are all rejected for insertion. If a unique index is used, if the Ignore duplicates switch is turned on, 15 data that is not duplicated are inserted correctly and 5 duplicate data is ignored.

② If a column has multiple row values that are NULL, you cannot establish a unique index on that column.

A method that makes two fields different in MySQL