Summary of the differences between Mysql primary key and unique key

What is a primary key?

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

To understand the primary key better, let us create a table called Student having attributes like 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 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. So, in this case, we can make the Roll_number attribute as the primary key.

What is a unique key?

A unique key constraint uniquely identifies a single tuple in a relation or table. Unlike a primary key, a table can have multiple unique keys. A unique key constraint can accept only one null value for a column; the unique constraint is also referenced by a foreign key from another table. It can be used when one wants to enforce unique constraints on columns and groups of columns that are not primary keys.

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

Key differences between primary key and unique key:

1. When an attribute 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. A table can have only one primary key, but can have multiple unique keys.

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

The above is all about the difference between Mysql primary key and unique key. Thank you for your support to 123WORDPRESS.COM.