SQL PRIMARY KEY Vs UNIQUE KEY Constraint
The PRIMARY KEY uniquely identifies each record in the table, whereas the UNIQUE KEY , with the exception of NULL values, precludes duplicate entries in a column. Let's look at the distinctions between primary and unique keys in more detail.
SQL PRIMARY KEY Constraint
A PRIMARY KEY is a table column that uniquely identifies each tuple (row) within it.
The table's integrity restrictions are enforced by the primary key. In a table, only one primary key can be used.
Duplicate and NULL values are not accepted in the primary key.
The PRIMARY KEY value in a table changes relatively seldom, thus it is carefully picked in cases where changes are likely to occur infrequently.
A foreign key of one table can refer to a primary key of another table.
A minimum super key is another name for it.
A foreign key column in one table can refer to a main key column in another table. If you're going to make a composite primary key, you should do so at the table level.
Example: The following example describes that there is a table called student. It contains five attributes, 1) StudID, 2) Roll No, 3) First Name, 4) Last Name, and 5) Email.
There can never be a duplicate value in the Roll No attribute. It's because each university student can have a different Roll No .
Each row of a table may be easily identified by the student's Roll No. As a result, it is regarded as a primary key.
SQL UNIQUE KEY Constraint
A UNIQUE KEY is a single column or a set of fields used to uniquely identify database records in a table.
Unlike primary keys, a table can have several unique keys.
This key is comparable to the primary key, except that the unique key column can hold one NULL value.
The foreign key of another table also refers to unique restrictions. It's useful when you want to impose unique constraints on a column or a set of columns that aren't primary keys.
Example: For example, let's consider the same table named students with attributes such as Stud_ID , Roll_No, Name, Mobile , and Email .
Because each student must have a unique identification number, Stud ID can be used as a unique constraint.
If a student transfers colleges, he or she will no longer have a student ID . Because a unique key requirement allows for NULL storage, the item may include one, but it should be the only one.
SQL Distinguish in PRIMARY KEY And UNIQUE KEY Constraint
There are 7 main difference in primary & unique key :-
| PRIMARY KEY | FOREIGN KEY |
|---|---|
| To uniquely identify records in a table, a primary key might be one or more fields in the table. On the other hand, if you're looking for a unique way to express yourself, | A unique key avoids duplicate entries in a column in two rows. |
| In a relational database, a table cannot have more than one primary key, however in a non-relational database, a table can have many primary keys. | Each table can have many unique keys. |
| A primary key column cannot have NULL values, although a foreign key column can. | A unique key can have NULL values, but a table can only have one NULL. |
| While a primary key should be unique, | It should not be primary key. |
| By default, the primary key is a clustered index, with data physically structured in a sequential index. | The unique key is a non-clustaered index that is unique. |
| The main key ensures entity integrity, whereas the secondary key ensures data consistency. | The unique key ensures that data is unique. |
| The main key values cannot be or deleted. | We can change the values of the unique key column. |