Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

Difference between Primary key and Unique key with Comparison Chart

|| 1

Difference between Primary key and Unique key with Comparison Chart
Stechies

Both primary and unique keys are responsible for making relational database management systems function in their proper way. Through this article, we aim to help our readers know more about what is primary key, what is unique key, the main difference between primary key and unique key, the difference between unique and primary key in a tabulated format, etc. Read on for more.

Primary Key vs Unique Key

Basis of Difference

Primary Key

Unique Key

NULL Value

By default, primary keys cannot accept NULL value

Only one NULL value can be accepted by unique keys

Clustered Index

By default, the primary key is a clustered index and the data present in the table of the RDBMS is organized physically in sequence and as a unique clustered index

By default, the unique key is a non-clustered, unique index

Number

Only one primary key is possible in a table

More than one unique key can be present in a table

Basic

Used for serving as a unique identifier for individual rows in a table.

Along with the primary key, this key uniquely determines an individual row, which is not defined in any way by the primary key.

What is primary key?

Before we delve into the difference between unique key and primary key, it is important to know the definition of primary key in dbms or a database management system. A primary key is used as an important means of identifying each record in a given table. It helps in identifying a row by using the data present within the row. In general, the primary key is based on any one or more of the given columns, for instance, the first and last name. In many other dbms designs, the primary key happens to be an auto-generated number belonging to an identity column.

As you understand the features and functions of unique key and primary key in depth, you will find all relational database management systems supporting primary keys.

In order for any table to be reckoned as a relational table, as in its first normal form, the table must comprise of a primary key. In other words, the primary key is an important and necessary part of any RDBMS.

In context to SQLServer, when programmers refer to the primary key, they term it as the PRIMARY KEY constraint. The following characteristics are to be noted in a primary key:

  • There can be just one primary key in any table.
  • The primary key comprises of either one or more columns.
  • It is useful for enforcing the entity integrity of any table.
  • The columns defined in the table must have the default value of NOT NULL.
  • The primary key is used for identifying a row uniquely.
  • A primary key will result in unique, CLUSTERED indexes by default.

In a nutshell, between the unique key and primary key, the primary key happens to be the main key of a given table and is used for identifying each row.

When do you use a primary key?

Always. A primary key refers to a special table column (or a combination of columns) in a relational database. It is defined and designated for the purpose of uniquely identifying all available table records in the database. As the primary contains a unique value for every row of data, null values cannot be contained by the primary key. A primary key example is given below:

Say, there exists a Student table in your RDBMS with attributes like Roll_number, Name, Year, Contact_number, Aadhar_ID.

Table Name: Student

Roll_number

Name

Year

Contact_number

Aadhar_ID

In the above table, the attribute named Roll_number can never have a null or identical value as each enrolled student will be provided with a roll number at the time of his/ her admission in the university. More so, no two students can possess the same roll number as each entity will be uniquely identified by the authorities. So, the Roll_number attribute serves as the primary key in this table.

A primary key is capable of being referenced by a foreign key. It builds a unique clustered index that is related to the table. The data rows are sorted in a clustered index and stored in views or in a table based upon the key values. As the data rows can be sorted in just one order in any table, there can be only one clustered index in any table.

What is Unique Key?

Similar to the primary key in dbms, the unique key constraint is also used for identifying an individual tuple in any relation in a unique manner. Unique key constraints comprise of a set of either one or multiple fields/columns of any table that identifies a record uniquely (in a given database table). A unique key is capable of having only one null value. Just like the primary key, it cannot have any duplicate values. In other words, just like the primary key, a unique key also offers guarantee for the uniqueness of columns or multiple columns. There may exist multiple unique keys in any table but there will be only one primary key constraint for any given table.

In SQL Server, the following characteristics are depicted by unique keys:

  • Multiple unique keys can be defined on a table.
  • By default, unique keys will result in unique and NONCLUSTERED indexes.
  • A unique key is usually made up of one or more columns in a given table.
  • A column may have a NULL value but only one NULL per column is permissible.
  • It is possible to reference a unique constraint by a Foreign Key Constraint.

When do you use a unique key?

Unique key constraints are put in use to ensure that the data contained in any column is not duplicated in any two rows present in the database. This is a good way of ensuring data validation. Only one row in the relational database is permitted to have null value for the unique key constraint. In the example cited above, the column Aadhar_ID can be assigned as the unique key wherein each entry will not be duplicate but unique and as assigned by the Government as a unique identification number. In case a student has migrated from another country then the value of Aadhar_ID will be NULL in his/ her case. This is permissible as only one NULL can be included in the unique constraint. This is an important difference between primary and unique key.

Unique constraints are designed to be referenced by any foreign key. They can be used in case constraints have to be enforced on one or more columns that are not the primary key. Unlike in the case of primary keys, unique constraints are known to generate the non-clustered index that have a distinct structure from data rows. The individual key-value entries in non-clustered indexes point to the data row containing the key value; hence the use of pointers.

Key Difference between Unique key and Primary key

It is commonplace for RDBMS users to ask, “What is the difference between primary key and unique key?” The primary key and unique key have their own set of features and functionality that differ in certain cases. The UNIQUE Key and PRIMARY Key constraints are both similar in the sense that they are used for enforcing the uniqueness of the particular column that they are defined on.

The main differences are:

  • Every table in the database structure is capable of having just a single primary key. On the other hand, there may exist multiple unique constraints on any given table.
  • The attribute declared as a primary key is not equipped to accept NULL values. In contrast, the attribute declared as the unique constraint is capable of accepting one NULL value.
  • When a primary key is defined, a clustered index is automatically created by default. Its definition results in the data present in the database table being physically organized, and in sequence, in the clustered index. However, the definition of a unique key ends up generating a non-clustered index.

The above three diff between primary key and unique key are the most important of the lot.

Conclusion

Both Primary key and Unique key in SQL serve as unique identifiers for the rows belonging to the tables in a database. They identify the unique values in any column or set of columns. The significant point pertaining to unique vs primary key is that while a table has a maximum of one primary key, multiple unique keys can be assigned to the same.

In case you have any other point to add to the unique key and primary key difference table above, do drop us a line in the Comments section below. We will wait to hear from you.


Comments

  • 31 Jan 2019 12:00 am

    Primary key vs. Unique key

    Primary key : It is having null values, initial value is not checked in the checkbox.

    Unique key : It does not have null values, initial value is checked in the checkbox.

    Primary key exists in database table while unique key exists in internal tables. We can perform actions like sorting, searching, modifying, updating on the basis of primary key in the database table. Same actions can be performed with unique key in the internal tables.

    Internal tables with a unique key cannot contain duplicate entries. The uniqueness depends on the table access method.


Advertisement