Why we need a primary key?

I am reading about primary keys and at a lot of tutorials, technical blogs etc., and I found that a primary key cannot be null. I think it’s totally wrong because I was able to insert null value in the column. I suppose a primary key can have a not null value only if the column is declared as not null. But again this is not a feature of primary keys.

My question is why do we have a concept of primary key because I find only one difference between primary key and unique key is that “Primary key can be declared only on one column whereas unique key can be declared on multiple columns”. So my understanding is that why can’t we also declare the primary key as a unique key if we don’t have any other difference.

Answer

I suppose a primary key can have a not null value only if the column is declared as not null.But again this is not a feature of primary key.

Primary key can’t have a null values. By definition of primary key, it is UNIQUE and NOT NULL.

My another question is that why do we have a concept of primary key because I find only one difference between primary key and unique key is that “Primary key can be declared only on one column whereas unique key can be declared on multiple columns”

This is completely wrong. You can create primary key on multiple columns also, the difference between Primary Key and Unique Key is Primary Key is not null and Unique key can have null values.

The main purpose of primary key is to identify the uniqueness of a row, where as unique key is to prevent the duplicates, following are the main difference between primary key and unique key.

Primary Key :

1. There can only be one primary key for a table.
2. The primary key consists of one or more columns.
3. The primary key enforces the entity integrity of the table.
4. All columns defined must be defined as NOT NULL.
5. The primary key uniquely identifies a row.
6. Primary keys result in CLUSTERED unique indexes by default.

Unique Key :

1. There can be multiple unique keys defined on a table.

2. Unique Keys result in NONCLUSTERED Unique Indexes by default.

3. One or more columns make up a unique key.

4. Column may be NULL, but on one NULL per column is allowed.

5. A unique constraint can be referenced by a Foreign Key Constraint.

I suggest you read this primary key and unique key