Difference between Primary Key and Unique Key Vijay Sinha The Crazy Programmer

In this article we look into the difference between Primary Key and Unique Key Constraints. We will see the description of both along with how to define a schema and create attributes in a table with these constraints.

Primary Key

A Primary Key is a key attribute or a column which is used to uniquely identify a row (tuple) in a table (relation). It maintains the integrity constraints of a table. A table can have only one primary key attribute. The data of primary key must be unique. Hence, no duplicate values are valid. Also, no NULL (empty) values are allowed during insertion as it violates the integrity constraint. The database automatically creates a Clustered Index over primary key which orders data on the basis of primary key.

We define a Primary Key Attribute as follows:

Column_Name DataType PRIMARY KEY

Unique Key

A Unique Key is a constraint or key attribute which also uniquely identifies a row in a table. It’s purpose is somewhat same as primary key. It differs in ways such as a table can have one or more than one Unique keys. It allows the possibility of NULL value insertion limited to only one null value insertion. In other words, Unique Key Constraints allow a column to have only one NULL value. No duplicate values are permitted. By default, the Unique key are generated in a Non-Clustered Index.

We define a Unique Key Attribute as follows while creating a table:

Column_Name Datatype UNIQUE

Now let us look at an example of creating a table with these constraints:

CREATE TABLE CARS
(
Car_Id INT PRIMARY KEY,
Car_Brand VARCHAR(10),
Car_NumberPlate VARCHAR(10) UNIQUE
);

Here Car_Id is the Primary key and Car_NumberPlate is the Unique Key of table CARS.

Let us assume we have these records in table CARS:

If we add a record like this:

INSERT INTO CARS VALUES(3,'Hyundai','');

This will add the record successfully since the unique key Car_NumberPlate allows one null value and show contents of database:

If we add any duplicate value in Primary key field Car_Id, it will show us error

INSERT INTO CARS VALUES(3,'Honda','TN056789');

So, when we try entering duplicate values to our Primary key Car_Id it shows the Unique constraint of the field is violated.

Also Read: Difference between Primary Key and Foreign Key

Difference between Primary Key and Unique Key

Now let’s look at the key differences between them:

Primary Key	Unique Key
1. It is the SQL constraint to uniquely identify rows of a table.	1. It is a unique identifier for rows when primary key is not present.
2. Primary Key does not allow NULL value insertion.	2. It allows one NULL value insertion.
3. Only one Primary Key can be present in a table.	3.There can be one or more than one Unique keys in a table.
4.  The main purpose is to enforce entity integrity.	4. The main purpose is to enforce uniqueness of entity data.
5. By default, the primary key column has Unique Clustered Index.	5. The unique key column has Non-Clustered Index.
6. It is useful in creating relationships between other tables.	6. It is useful for Data Validation.

That’s all for the article you can create a table like the example mentioned above, add some records and see it in working!

Feel free to ask your doubts in the comments section.

The post Difference between Primary Key and Unique Key appeared first on The Crazy Programmer.

from The Crazy Programmer https://ift.tt/2ZwG0KI