Difference Between Primary key and Unique key

Primary key vs Unique key

A column or a set of columns, which can be used to identify or access a row or a set of rows in a database is called a key. A unique key is a key that can uniquely identify a row in a table in the context of relational databases. A unique key is made up of a single column or a set of columns. A primary key is also a combination of columns in a table that uniquely identify a row. But it is considered to be a special case of the unique key.

Read More: Oracle Database Certifications

What is Unique Key?

As mentioned earlier, unique key is a single column or set of columns that can uniquely identify a row in a table. So, a unique key is constrained such that no two values of it are equal. One important property is that the unique keys do not enforce the NOT NULL constraint. Since NULL represents the lack of a value, if two rows have NULL in a column then it does not mean the values are equal. Column defined as a unique key allows only a single NULL value in that column. Then that can be used to identify that particular row uniquely. For example, in a table that contains student information, student ID can be defined as a unique key. Since no two students can have the same ID it uniquely identifies a single student. So the student ID column satisfies all the properties of a unique key. Depending on the design of a database, a table may have more than one unique key.

What is Primary Key?

Primary key is also a column or a combination of columns that uniquely defines a row in a table of a relational database. A table can have at most one primary key. Primary key enforces the implicit NOT NULL constraint. So, a column that is defined as the primary key cannot have NULL values in it. Primary key can be a normal attribute in the table that is guaranteed to be unique such as social security number or it could be a unique value generated by the database management system such as a Globally Unique Identifier (GUID) in Microsoft SQL Server. Primary keys are defined through the PRIMARY KEY constraint in ANSI SQL Standard. Primary key can also be defined when creating the table. SQL allows primary key to be made up of one or more columns and each column that is included in the primary key is implicitly defined to be NOT NULL. But some database management systems require making the primary key columns explicitly NOT NULL.

Difference Between Primary key and Unique key

Even though both the primary key and unique key are one or more columns that can uniquely identify a row in a table, they have some important differences. Most importantly, a table can have only a single primary key while it can have more than one unique key. Primary key can be considered as a special case of the unique key. Another difference is that primary keys have an implicit NOT NULL constraint while the unique key does not have that constraint. Therefore, unique key columns may or may not contain NULL values but primary key columns cannot contain NULL values.

Source: differencebetween.com