Shulou(Shulou.com)06/01 Report--

Xiaobian to share with you what is the difference between the primary key and the unique key in MySQL database, I hope you will gain a lot after reading this article, let's discuss it together!

What is a primary key?

A primary key is a column in a table that uniquely identifies each tuple (row) in the table. Primary keys enforce integrity constraints on tables. Only one primary key is allowed in a table. The primary key does not accept any duplicate or null values. Primary key values in a table rarely change, so be careful when choosing a primary key where changes rarely occur. A primary key of one table can be referenced by a foreign key of another table.

To better understand the primary key, we create a table called Student with attributes such as roll_number, name, batch, phone_number, citizen_id, and so on.

In the example above, the roll_number attribute can never have the same NULL value, because students enrolled in each university have a unique Roll_number, so two students cannot have the same Roll_number, and each row in the table can be uniquely identified by the student's roll_number attribute. Therefore, in this case, we can use the Roll_number attribute as the primary key.

What is the unique key?

Unique key constraints uniquely identify individual tuples in a relationship or table. Unlike primary keys, a table can have multiple unique keys. A unique key constraint can only accept a null value for a column; the unique constraint is also referenced by a foreign key for another table. It can be used when someone wants to impose unique constraints on columns and column groups that are not primary keys.

To better understand unique keys, we use the Student table with Roll_number, Name, Batch, Phone_number, and Citizen_ID attributes; where the Roll_number attribute is already given to the primary key.

In this example, a unique constraint can be assigned to Citizen_ID, where each entry in the Citizen_ID column should be unique, not duplicate, because every citizen of a country must have its unique identification number. However, if the student migrates from another country, in which case he or she will not have a Citizen_ID, and the entry may have a NULL value because one NULL is allowed in the unique constraint.

The main differences between primary and unique keys are:

When an attribute is declared as a primary key, it will not accept NULL values. On the other hand, when a property is declared Unique, it can accept a NULL value.

There can only be one primary key in a table, but there can be multiple unique keys.

3. Cluster index is automatically created when defining primary key. In contrast, Unique keys generate nonclustered indexes.

After reading this article, I believe you have a certain understanding of the difference between the primary key and the unique key in MySQL database. If you want to know more about it, welcome to pay attention to the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.