Primary Key in SQL
A table's main key is the field that serves as each tuple's (row's) unique identifier. The table's integrity restrictions are enforced by the primary key. In a table, only one primary key may be used. Null and duplicate values are not accepted for the primary key. Since the primary key value in a table is the one that changes the least, it is carefully packed in cases where changes are possible but unusual. A foreign key from one table can reference the main key from another.
Characteristics of the Primary Key
The following section discusses a few of Primary Keys' key components.
- In the event of a primary key, there won't be a duplicate row.
- There is just one primary key per table.
- There are NOT NULL constraints in the primary key.
- One or more table fields can be used to create the main key.
Unique Key in SQL
Additionally, in a relation or table, key constraints uniquely identify each tuple. As opposed to a main key, a table can have multiple unique keys. There can only be one NULL value for the column in unique key restrictions. The foreign key of another table also references unique restrictions. When someone wants to impose unique restrictions on a column or set of columns that aren't main keys, they can utilize it.
Characteristics of a Unique Key
Below is a discussion of some of Unique Keys' key components.
- A table may have more than one unique key.
- NULL values are allowed in the column for Unique Keys.
- One or more tables can be combined to create unique keys.
- Unique Keys are a source of reference for Foreign Keys.
Primary key vs Unique key
| Parameters |
Primary Key |
Unique Key |
| Basic |
Used as a distinct identifier for every database row. |
Finds a row that isn't the primary key in a unique way. |
| NULL value |
Cannot take values of NULL. |
Able to take NULL values. |
| Key define in the table |
One primary key only. |
Multiple unique keys. |
| Index |
Makes an index clustered. |
Construct an index that is not clustered. |
| Auto increment |
Auto-increment value is supported by a primary key. |
Auto-increment values are not supported by unique keys. |
| Modification |
Primary key values cannot be altered or removed. |
The unique key values are modifiable. |
| Uses |
To uniquely identify the rows, use the primary key. |
The purpose of the unique key is to avoid duplicate entries. |
| Syntax |
CREATE TABLE table_name ( field_name data_type PRIMARY KEY ) |
CREATE TABLE table_name ( field_name data_type UNIQUE ) |
| Example |
CREATE TABLE Employee ( Id INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL ) |
CREATE TABLE Employee ( Id INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, EmailAddress VARCHAR(100) UNIQUE ) |
Summary
- While a table may include more than one unique key, it may only have one primary key.
- While the unique key can accept NULL values, the primary key will not allow them.
- When a primary key is defined, an automatically generated clustered index is produced, whereas a non-clustered index is produced by a unique key.
- A unique key cannot be a primary key, although a primary key can be a unique key.
We learned the new technique and evolved together.
Happy coding!