A series of tables having rows and columns that store information that is retrieved or used as and when required are termed as data. The data stored in the rows and columns is determined by the keys used in the table. A table has Primary Key, Unique Key, Foreign Key, etc.
Primary Key vs Unique Key
The main difference between the Primary Key and Unique Key is that a primary key is used to uniquely identifies a row of a table, the process of unique identification of rows is termed as entity integrity, a table only one unique key while unique key places additional conditions to make rows unique other than the primary key, a table can have more than one unique key.
A primary key constraint in a table is used to identify the rows of the table uniquely. In any data table, there is only one primary key. It does not take null values, also it does not allow duplicate keys.
A unique key of a table confirms that the data in each row and column is unique and not repeated. In the table, there are more than one unique key. The unique key may take null values and duplicate keys. A primary key is always unique but the vice versa is not always true.
Comparison Table Between Primary Key and Unique Key
| Parameter of Comparison | Primary Key | Unique Key |
|---|---|---|
| Basic function | The basic function of a primary key is to uniquely identify a row in a table. | The basic function of a unique key is to apply additional conditions to a row so that it becomes unique. |
| Number of keys in a table | A table has only one primary key. | A table can have more than one unique key. |
| Null value acceptance | A primary key does not accept null values. | A unique key can accept null values. |
| Indexing | A primary key has a clustered default index. | The default index in a unique key is non clustered. |
| Duplicate keys | Duplicate keys are not allowed in a primary key of a table. | Duplicate keys are allowed in a unique key of a table if one or more key parts are null. |
What is Primary Key?
One of the most important keys of a data table is the primary key. The primary key is a key that confirms the uniqueness of the data table. It determines the row of a table uniquely and therefore applies the process of entity integrity. In a table, there is only one primary key, however, the value of the primary key has two parts. The primary key can also be defined as the SQL constraint that uniquely identifies rows of the record.
A primary key does not accept null values. The primary key also does not accept duplicate values. The default index of the primary key is clustered. The values in the rows of a primary key of the table cannot be altered or deleted. A unique key can be associated with some other table as a foreign key.
The syntax for creating the primary key in the table is-
CREATE TABLE Persons (ID int NOT NULL, Lastname varchar(255) NOT NULL, Firstname varchar(255), Age int, PRIMARY KEY(ID));
What is Unique Key?
Another key of the data table is the unique key. The unique key in the data table identifies rows uniquely other than the primary key. The function of the unique key is to apply conditions to the rows that make them unique. The purpose of it is to make sure that the data in each row or column is unique and not repeated. In a table, there are more than one unique key. It uniquely defines the data in the records.
A unique key can accept null values. It also takes duplicate values in the table if and only if one or more than one key parts are null. The default index of the unique key is encountered. The values in the rows and columns can be changed or altered. The unique key cannot be associated with any other table as a foreign key.
The syntax of creating the unique key in the table is-
CREATE TABLE Persons (ID int UNIQUE. Name varchar(255) NOT NULL Surname varchar(255), Age int, UNIQUE KEY(ID));
Main Differences Between Primary Key and Unique Key
- The basic function of a primary key is that it identifies rows uniquely in a table while a unique key identifies rows other than the primary key uniquely.
- The purpose of the primary key is to apply entity integrity while the purpose of a unique key is to apply unique data.
- The primary key is a SQL constraint to uniquely identify each record while the unique key is a SQL constraint that does not allow the same value in records.
- In a table, there is only one primary key while there can be more than one unique key.
- A primary key of a table does not accept any null value while a unique key does accept null values.
- In a table, a primary key has a clustered default index while a unique key has an unclustered default index.
- In the table, duplicate keys are not allowed in the primary key while they are allowed in a unique key.
- The values of a primary key cannot be changed or deleted while the values of a unique key can be changed or deleted.
- A primary key can be associated with other tables as a foreign key while a unique key can’t be associated as a foreign key.
- A primary key is always unique while a unique key is not necessarily a primary key.
Conclusion
In a relational database, both the primary key and the unique key assures the uniqueness of the column and the table. The primary key identifies unique records, on the other hand, the unique key prevents duplicate entries in the table.
References
- https://link.springer.com/article/10.1023/A:1006720022161