Simple comparison of primary key and unique key

Both keys and unique keys are used to uniquely identify tuples and enforce uniqueness within a column or combination of columns. So how to distinguish them? The following article will give you a brief comparison between primary keys and unique keys, and introduce the differences between primary keys and unique keys. I hope it will be helpful to you.

What is a primary key?

The primary key is a column in a table that uniquely identifies each tuple (row) in the table. The primary key enforces integrity constraints on the table. Only one primary key is allowed in the table. The primary key does not accept any duplicate values ​​and null values. Primary key values ​​in a table rarely change, so you need to be careful when choosing a primary key and choose a place that rarely changes. The primary key of one table can be referenced by the foreign key of another table. [Video tutorial recommendation: MySQL tutorial]

In order to better understand the primary key, we create a table named Student, which has attributes such as roll_number, name, batch, phone_number, citizen_id, etc.

In the above example, the roll_number attribute can never have the same NULL value because a student registered in each university has 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. So in this case we can make Roll_number attribute as primary key.

What is a unique key?

#Unique key constraints can uniquely identify a single tuple 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; a unique key constraint is also referenced by a foreign key from another table. It is used when one wants to enforce unique constraints on columns and column groups that are not primary keys.

To better understand the unique key, we use the Student table with the attributes Roll_number, Name, Batch, Phone_number and Citizen_ID; where the Roll_number attribute has been 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 and not duplicates because Every citizen of a country must have his or her unique identification number. However, if the student migrates from another country, in this case he or she will not have a Citizen_ID and the entry may have a NULL value since a NULL is allowed in the unique constraint.

The main difference between primary key and unique key:

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

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

3. Automatically create a clustered index when defining the primary key. In contrast, Unique keys generate nonclustered indexes.

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of Simple comparison of primary key and unique key. For more information, please follow other related articles on the PHP Chinese website!